PowerQuery Macro Refresh

[tcardwell]

Hello,

Trying to get an automatic refresh of tables and pivot table with the following macro:

Sub Refresh()
'
' Refresh Macro
'
ActiveWorkbook.Connections("Power Query - Jan2008").Refresh
ActiveWorkbook.Connections("Power Query - Feb2008").Refresh
ActiveWorkbook.Connections("Power Query - Mar2008").Refresh
ActiveWorkbook.Connections("Power Query - Transactions").Refresh
Worksheets("Transactions").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

What ends up happening is that the Refresh button(that contains the macro) has to be pushed twice in order for the pivot table to refresh. Ideal behaviour is to only have to click the Refresh button once for tables and pivot table to refresh.

Any ideas on how above can be tweaked to accomplish a one click refresh?

Thanks, Tom C

 

[VBA Geek]

Did you try to use RefreshAll

 

[tcardwell]

Using RefreshAll with the Activesheet command generates an error, so I used it with ActiveWorkbook and it does not generate an error. However, original issue does not resolve, still need to click Refresh command twice in order for pivot table to update.

 

[VBA Geek]

The pivot table should refresh on refresh all

try to un-tick this:

 

[tcardwell]

That worked!
I also modified the macro to just read:
ActiveWorkbook.RefreshAll

I assume the query dependency info is used to make sure all source data is loaded and refreshed before the last update to the pivot table is made?

 

[VBA Geek]

Yes, Disable Background Refresh will pause all other operations until the query is done refreshing.
It is highly recommended to turn it off if you 're refreshing queries with VBA.
If you have it on and your code relies on the output of the query then your code jumps to the next step without waiting