有没有办法让VBA Power query刷新得更快？

Question

我有一个宏，可以成功地打开工作簿并刷新其中的每个Power Query连接。如果我手动点击“全部刷新”，整个过程需要3-5分钟。但是如果我运行这个宏，它需要一个小时来完成这个过程。

我尝试过使用Application.ScreenUpdating = False，但没有任何效果。有什么方法可以让刷新更快吗？

Dim fname As String
    Dim owb As Workbook
    Dim Cname As String
    Dim objConnection As WorkbookConnection, bBackground As Boolean
    fname = "PowerQuery04-CSVMultipleFileImport.xlsx"
    Set owb = Application.Workbooks.Open(fpath & "\" & fname)
        For Each objConnection In ActiveWorkbook.Connections
            If Left(objConnection.Name, 8) = "Query - " Then
            Cname = objConnection.Name
                With ActiveWorkbook.Connections(Cname).OLEDBConnection
                    .BackgroundQuery = False  'or true, up to you
                    .Refresh
                End With
            End If
        Next
    'owb.RefreshAll
    Application.Wait (Now + TimeValue("0:02:00"))
    Application.DisplayAlerts = False
    owb.Save
    owb.Close
    Application.DisplayAlerts = True

Answer 1

我怀疑如果您使用Workbook.RefreshAll method而不是循环遍历查询，您将获得更好的性能。

ActiveWorkbook.RefreshAll