How to refresh Power Query using VBA?

Question

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

Please, try replacing of ActiveWorkbook with MyWorkBook, in all cases where it appears. — FaneDuru
– FaneDuru, Commented Jun 28, 2022 at 9:05
Are the queries embedded in the macro'd workbook, or MyFile.xlsx ? — CLR
– CLR, Commented Jun 28, 2022 at 9:34
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
– mustafa00, Commented Jun 28, 2022 at 9:36
Have you tried MyWorkbook.Worksheets("worksheetname").ListObjects("tablename").QueryTable.Refresh BackgroundQuery:=False ? — CLR
– CLR, Commented Jun 28, 2022 at 9:44

CLR · Accepted Answer · 2022-06-28 12:19:37Z

8

I have used the following to refresh PowerQuery tables:

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

answered Jun 28, 2022 at 12:19

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eduardo · Accepted Answer · 2024-02-03 19:11:30Z

It may be happening because you are referencing the active workbook. in the macro above, if you have another workbook activated, it will not work, because you referencing the active workbook. Try changing your code, activating the workbook where the queries are sitting in and then refresh.

Sub RefreshQuery()

Application.DisplayAlerts = False

File = "C:\Users\User1\Desktop\MyFile.xlsx"
Set MyWorkBook = Workbooks.Open(File)

MyWorkBook.activate

MyWorkbook.Queries.FastCombine = True 'ignores privacy levels on all computers

'Refresh option #1
MyWorkbook.RefreshAll

MyWorkbook.Save
Application.Wait (Now + TimeValue("00:00:03")) '-> do you really need this?  
MyWorkbook.Close

End Sub

You must include a loop if there is more than one workbook.

Collectives™ on Stack Overflow

How to refresh Power Query using VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related