Refreshing all Queries in Power Query

[uk747]

Hi

I have one query from an external workbook and then I have several other queries that use the External Query

I'm trying to Refresh all Queries in VBA
1st Get the External Data and then from that Refresh All other Queries that are based on that one

Initially I tried Refresh ALL and then Reffresh2 option but neither worked. They refreshed the external query but didnt refresh all other queries in the workbook

Sub Refresh1()
' Refreshed External Query but didn't Refresh other queries
ActiveWorkbook.RefreshAll

End Sub

Sub Refresh2()
' Refreshed External Query but didnt Refresh other queries
Dim objConnection, bBackground

For Each objConnection In ThisWorkbook.Connections
  bBackground = objConnection.OLEDBConnection.BackgroundQuery

  objConnection.OLEDBConnection.BackgroundQuery = False
  
  objConnection.Refresh

  objConnection.OLEDBConnection.BackgroundQuery = bBackground

Next

End Sub

 

[Kerryx]

Did you check connection properties

 

[uk747]

Hi

Just checked main External Query and some of the ones that don't Refresh and settings are the same as in your screenshot

 

[uk747]

Does anyone know the easiest way in VBA to make sure all Power Queries are refreshed including ones that reference them?

Thanks

 

[uk747]

bump

 

[Kerryx]

Did you try to disable the background refresh " uncheck the box on the connection" if you are using RefreshAll in your code ,
disabling the background refresh will cause Workbook.RefreshAll to complete its refresh before the rest of the VBA code does its job. with background refresh on the code will run away so you would have incorrect data in tables

 

[JEC]

Uncheck "Enable background Refresh" for every query like suggested before.
Then ThisWorkbook.RefreshAll should work

 

[uk747]

Did you try to disable the background refresh " uncheck the box on the connection" if you are using RefreshAll in your code ,
disabling the background refresh will cause Workbook.RefreshAll to complete its refresh before the rest of the VBA code does its job. with background refresh on the code will run away so you would have incorrect data in tables

It wasn't clear in your initial reply that's what I was supposed to do. You just highlighted those options

 

[uk747]

Uncheck "Enable background Refresh" for every query like suggested before.
Then ThisWorkbook.RefreshAll should work

Hi shouldn't the code I posted originally Refresh2 have done that (Turn off Background Refresh, Then Refresh, then Turn it back on)

Or If I wanted to automate do I manually Uncheck Enable background refresh and leave it like that permanently and then just use vba below

mysub()
ActiveWorkbook.RefreshAll
End Sub

 

[JEC]

Yes leave it unchecked