How to refresh Power Query using VBA?-CodePudding

I created a query in Power Query. What I need to do is to refresh PQ and save the Workbook upon running the macro. I've tried to do it using various macros but none of them refreshes a query. I use this macro in other Workbook - it opens given Workbook, 'refreshes' it (but it doesn't in fact because it lasts too short) and closes. Interesting thing is that when I run 'Refresh all' on Excel ribbon then PQ refreshes (all connections and quesries). However, when I use ActiveWorkbook.RefreshAll then it doesn't refresh PQ at all.

Below is my macro with many ways of refreshing PQ:

Sub RefreshQuery()

    Application.DisplayAlerts = False
    
    File = "C:\Users\User1\Desktop\MyFile.xlsx"
    Set MyWorkBook = Workbooks.Open(File)
   
    ActiveWorkbook.Queries.FastCombine = True 'ignores privacy levels on all computers
    
    'Refresh option #1
    ActiveWorkbook.RefreshAll
    
    'Refresh option #2
    For Each cn In ActiveWorkbook.Connections
        cn.Refresh
    Next cn

    'Refresh option #3
    ActiveWorkbook.Connections("GetStatData").Refresh

    'Refresh option #4
    ActiveWorkbook.Connections("Query - GetStatData").Refresh

    'Refresh option #5
    ActiveWorkbook.Query("GetStatData").Refresh

    ActiveWorkbook.Save
    Application.Wait (Now   TimeValue("00:00:03"))   
    ActiveWindow.Close

End Sub

CodePudding user response：

I have used the following to refresh PowerQuery tables:

ActiveWorkbook.Worksheets("<yourworksheetname>").ListObjects("<yourtablename>") _
    .QueryTable.Refresh BackgroundQuery:=False