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

[M001]

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!!

Regards,

M

[M001]

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