在工作簿打开时，Excel宏将刷新所有数据连接表和数据透视表，然后将枢轴导出到csv [英] On workbook open, Excel Macro to refresh all data connections sheets and pivot tables and then export the pivot to csv

问题描述

我有一个具有CSV数据源和数据透视表的Excel文件，我想自动刷新所有数据源和数据透视表，并在打开Excel文件时将一个数据透视表导出为CSV。

我尝试了以下代码，但是这个代码在刷新数据之前导出CSV文件。

请帮助解决方案。感谢提前。

  Private Sub Workbook_Open（）
 ThisWorkbook.RefreshAll 
运行Macro1
 End Sub 
 
 
 Sub Macro1（）
 
 Dim ws As Worksheet，newWb As Workbook 
 Dim SaveToDirectory As String 
 
 SaveToDirectory =C：\Macro\
 
 Application.ScreenUpdating = False 
对于每个ws In Sheets（Array（locationwise））
 ws.Copy 
设置newWb = ActiveWorkbook 
使用newWb 
 .SaveAs SaveToDirectory& ws.Name，xlCSV 
 .Close（False）
结束
下一个ws 
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = False 
 End Sub 
  

解决方案

一个简单的 DoEvents 应该做的伎俩！ ;）

尝试这样：

  Private Sub Workbook_Open（）
 ThisWorkbook.RefreshAll 
 DoEvents 
运行Macro1
 End Sub 
  

如果不是，只需在 DoEvents 之后添加此行：

  Application.Wait（Now + TimeValue（0:00:05））
  

这将搁置执行代码，这里是5秒！

---

如果要启动保存部分一旦修改了特定范围，将该代码放入表单模块中：

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Application.Intersect（Target，Me.Range（Rg_To_Check））不是，然后
'不在范围
 Else 
'在范围内检查
运行Macro1
如果
 End Sub 
  

在 Workbook_Op中删除运行Macro1 en（）事件。

---

另外，请小心，因为最后一行是 Application.DisplayAlerts = False 您将不会有警报，您应该使用它：

  Sub Macro1（）
 
 Dim ws As Worksheet，newWb As Workbook 
 Dim SaveToDirectory As String 
 
 SaveToDirectory =C：\Macro \
 
 Application.DisplayAlerts = False 
 Application.ScreenUpdating = False 
对于每个ws In Sheets（Array（locationwise））
 ws.Copy 
设置newWb = ActiveWorkbook 
使用newWb 
 .SaveAs SaveToDirectory& ws.Name，xlCSV 
 .Close（False）
结束
下一个ws 
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
 End Sub 
  

I have an Excel File which has CSV Data sources and Pivot tables, I want to refresh all the data sources and pivot tables automatically and export one pivot table as CSV on opening the excel file.

I tried the below code, but this code export the CSV file before the data getting refreshed.

please help with a solution. Thanks in advance.

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    Run "Macro1"
End Sub


Sub Macro1()

 Dim ws As Worksheet, newWb As Workbook
 Dim SaveToDirectory As String

 SaveToDirectory = "C:\Macro\"

 Application.ScreenUpdating = False
 For Each ws In Sheets(Array("locationwise"))
     ws.Copy
     Set newWb = ActiveWorkbook
     With newWb
        .SaveAs SaveToDirectory & ws.Name, xlCSV
        .Close (False)
     End With
 Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub

解决方案

A simple DoEvents should do the trick! ;)

Try this :

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    DoEvents
    Run "Macro1"
End Sub

And if it's not, just add this line after the DoEvents :

Application.Wait(Now + TimeValue("0:00:05"))

This will put on hold the execution of the code, here for 5 seconds!

---

If you want to launch the save parts once a specific range has been modified, place your that code into the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Me.Range(Rg_To_Check)) Is Nothing Then
    'Not in range
Else
    'In range to check
   Run "Macro1"
End If
End Sub

And get rid of the Run "Macro1" in the Workbook_Open() event.

---

Also, be careful, because your last line is Application.DisplayAlerts = False you won't have alerts afterwards, you should use it like this instead :

Sub Macro1()

 Dim ws As Worksheet, newWb As Workbook
 Dim SaveToDirectory As String

 SaveToDirectory = "C:\Macro\"

 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 For Each ws In Sheets(Array("locationwise"))
     ws.Copy
     Set newWb = ActiveWorkbook
     With newWb
        .SaveAs SaveToDirectory & ws.Name, xlCSV
        .Close (False)
     End With
 Next ws
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

这篇关于在工作簿打开时，Excel宏将刷新所有数据连接表和数据透视表，然后将枢轴导出到csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！