vba 在工作簿打开时，Excel 宏刷新所有数据连接表和数据透视表，然后将数据透视表导出到 csv
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31807441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
On workbook open, Excel Macro to refresh all data connections sheets and pivot tables and then export the pivot to csv
提问by davidb
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.
我有一个 Excel 文件，其中包含 CSV 数据源和数据透视表，我想自动刷新所有数据源和数据透视表，并在打开 excel 文件时将一个数据透视表导出为 CSV。
I tried the below code, but this code export the CSV file before the data getting refreshed.
我尝试了以下代码，但此代码会在数据刷新之前导出 CSV 文件。
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
采纳答案by R3uK
A simple DoEventsshould do the trick! ;)
一个简单的DoEvents应该可以解决问题！;)
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:
如果不是，只需在以下内容后添加此行DoEvents：
Application.Wait(Now + TimeValue("0:00:05"))
This will put on hold the execution of the code, here for 5 seconds!
这将暂停代码的执行，在这里停留 5 秒钟！
If you want to launch the save parts once a specific range has been modified, place your that code into the sheetmodule :
如果您想在修改特定范围后启动保存部分，请将您的代码放入工作表模块：
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.
并Run "Macro1"在Workbook_Open()事件中摆脱。
Also, be careful, because your last line is Application.DisplayAlerts = Falseyou won't have alerts afterwards, you should use it like this instead :
另外，要小心，因为你的最后一行是之后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
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
