vba - 在工作簿打开时，Excel 宏刷新所有数据连接表和数据透视表，然后将数据透视表导出到 csv

Question

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

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

请帮忙解决。提前致谢。

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

Answer 1

一个简单的DoEvents应该做的伎俩! ;)

尝试这个 :

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

如果不是，只需在 DoEvents 之后添加这一行:

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

这将暂停代码的执行，在这里暂停 5 秒!

如果您想在修改特定范围后启动保存部分，请将您的代码放入 表 模块 :

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

并摆脱 Run "Macro1"在 Workbook_Open()事件。

另外，要小心，因为你的最后一行是 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