Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
M001
Helper I
Helper I

Set refresh order for specific queries and data model table with VBA

Hi there,

 

I have a file with multiple queries via Power Query and tables via Power Pivot Data Model that are calculated one after another as shown below. The final result cannot be reflected correctly in the final table (Table 3) by using Refresh All to run as it will trigger all queries and tables refresh at the some time, therefore calculation cannot be flushed through without clicking Refresh All button a few times. 

 

I need to run them in an order as indicated in the Yellow from 1 to 5. I have disabled Background Refresh (Right click >> Properties) but it does not work. I have seen some others actually use VBA to execute the refresh in steps (https://stackoverflow.com/questions/69558829/vba-refresh-power-query-and-subsequently-update-pivot-t...) and may I know how do I write the code for my scenario below?

 

Update:

The following code works for running specific Data Model

ThisWorkbook.Model.ModelTables("DataModelName").Refresh

 

The following codes do not work for running specific Query and I keep getting error as Run-time error "9" Subscript out of range

 

 

SUB Macro_Name()
ThisWorkbook.Connections("QueryName").Refresh
End Sub

 

ThisWorkbook.Worksheets("ExcelSheetName").ListObjects("QueryName").QueryTable.refresh BackgroundQuery:=False 
ThisWorkbook.Worksheets("ExcelSheetName").ListObjects("QueryName").QueryTable.Refresh

 

Thank you very much!!

Question_for_PowerQuery_Running_Seq.PNG

 

 

 

Regards,

M

1 REPLY 1
M001
Helper I
Helper I

Update to my own question above,

 

I have found using a wrong variable name and that's what Error '9' is telling me. You may use the following code to run the list all of your connections/queries and I simply missed we need to add "Query - " in front of your query name, i.e. query name as QueryReport then it should be "Query - QueryReport". Hope this helps anyone with the same problem, thank you

 

 

Sub ListAllQueriesandConnection()

Dim conn As WorkbookConnection

For Each conn In ActiveWorkbook.Connections

Debug.Print conn.Name

Next

End Sub

 

  

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors