Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                In MyFile.xlsx. 'Refresh' macro is located in other Workbook which serves as a global refresher, I refresh various Excel files from that global Workbook.
– mustafa00
                Jun 28, 2022 at 9:36
                Have you tried MyWorkbook.Worksheets("worksheetname").ListObjects("tablename").QueryTable.Refresh BackgroundQuery:=False ?
– CLR
                Jun 28, 2022 at 9:44
I have used the following to refresh PowerQuery tables:
ActiveWorkbook.Worksheets("<yourworksheetname>").ListObjects("<yourtablename>") _
    .QueryTable.Refresh BackgroundQuery:=False
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.