Refresh Queries at different times

[Paul_Christie]

Ken

I've got 5 Queries that need to be refreshed at different times. Query1 is set to refresh on workbook open, Query5 is set to update when a certain cell based drop-down is changed. Both those work fine. The others, Query2, Query3 & Query4 need to refresh when another cell based drop-down is chosen.

I thought I'd try using your UpdatePowerQuery Function but this runs much slower than a refresh all. Is this what you'd expect, is there another way to do this?

Regards
Paul

 

[Ken Puls]

Hey Paul,

Yes, totally to be expected. A Data-Refresh All refreshes all queries, then loads the total product to the data model once. When refreshing the queries individually the system assumes that this is all you're doing, so loads to the data model after each query. I'm not sure if this is 100% correct, but you can visualize the effect if you think about this scenario:

• "Knock knock"
• "Who's there?"
• "Power Query"
• "How do I know you're really Power Query"
• <some exchanging of credentials takes place until Power Pivot believes that Power Query is a good guy"
• "Okay, let me decompress ALL the tables in the model, since you want to update ONE."
• <wait for Power Pivot to decompress the tables>"
• "What table do you want to update?"
• "Sales"
• "Okay, send me the stuff"
• <Power Pivot writes the data in>
• "Anything else?"

And this is the point where things go sideways. If you're doing a Refresh All, the answer is "yes", and the results of each query are passed to Power Pivot to work with, and the table is updated in the model. When all tables are done, or when Power Query says "nope" (as is the case when using VBA to update individual queries), then the following happens.

• "Okay, cool thanks Power Query. See you next time!"
• <Power Pivot re-compresses all the tables>

Does that make sense? It's the overhead from the handshaking process that kills us when we load queries individually.

 

[Paul_Christie]

Ken

I've done this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wbkTW As Excel.Workbook
    Dim shtX As Excel.Worksheet
    Dim loX As ListObject
    Application.StatusBar = True
    Application.StatusBar = "Refreshing Data"
    Select Case Target.Address
        Case Is = "$B$3"
            Set wbkTW = ThisWorkbook
            Set shtX = wbkTW.Sheets("HRD")
            Set loX = shtX.ListObjects("Round_Data")
            loX.QueryTable.Refresh BackgroundQuery:=False
            Set shtX = wbkTW.Sheets("CATS_Data")
            Set loX = shtX.ListObjects("CATS_Data")
            loX.QueryTable.Refresh BackgroundQuery:=False
            Set shtX = wbkTW.Sheets("Rate_Card")
            Set loX = shtX.ListObjects("Rate_Card")
            loX.QueryTable.Refresh BackgroundQuery:=False
            Set shtX = wbkTW.Sheets("KPI")
            Set loX = shtX.ListObjects("KPI_Data")
            loX.QueryTable.Refresh BackgroundQuery:=False
            Set shtX = wbkTW.Sheets("Parameters")
            Set loX = shtX.ListObjects("Overview")
            loX.QueryTable.Refresh BackgroundQuery:=False
        Case Is = "$B$4"
            Set wbkTW = ThisWorkbook
            Set shtX = wbkTW.Sheets("Stops")
            Set loX = shtX.ListObjects("Stops")
            loX.QueryTable.Refresh BackgroundQuery:=False
    End Select
    Application.StatusBar = ""
    Application.StatusBar = False
End Sub

and got an acceptable response out of it. Is there any reason why I shouldn't use this method as opposed to the method in your UpdatePowerQuery function?

 

[Ken Puls]

Nope. If it works, use it.

Curious, the original code of mine... was it using the BackgroundQuery:=False or no?

 

[Paul_Christie]

Ken

BackgroundQuery:=False

Like your approach, if it works use it. It's a pity it doesn't make a catchy abbreviation. IIQUI doesn't have a ring to it.

Paul