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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors