CODEWITHSUNDEEP
excelvbapowerquery
Smith O.
Smith O.

Reputation: 217

Is there a way to make VBA Power query refresh faster?

I have this macro that can successfully Open a workbook and refresh every Power Query connection in it. If I manually click the "Refresh All", the whole process takes 3 - 5 mins. But if I run the macro, it takes up to an hour to complete the process.

I have tried using Application.ScreenUpdating = False but there was no impact. Is there any way to make the refresh faster?

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

Upvotes: 0

Views: 722

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I suspect you'll get better performance if you use the Workbook.RefreshAll method rather than looping through queries.

ActiveWorkbook.RefreshAll

Upvotes: 2

Related Questions