Code To Refresh Table

[Herbiec09]

Good afternoon All,

I was wondering if someone could assist me with a VBA code which I can embed on to a sheet so that whenever that sheet is opened a power query table that is part of that workbook is refreshed.

Not sure if this is enough detail. If not, please let me know

Many Thanks

Herbz

 

[jmacleary]

Hi there

Just record a macro doing what you want to do (i.e. refresh the query table), then place that macro in the relevant worksheet vba code area. To get at this, right click the worksheet name and select view code. Copy the macro you recorded to this area and rename it as : Private Sub Worksheet_Activate()

 

[Misca]

The easiest way would be to refresh everything with

Activeworkbook.RefreshAll

But if you only want to refresh a certain query the code gets a bit more complex:

https://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/

The way to get the code to run automatically would be to use the Worksheet.Activate event. However I'd recommend to think twice before using this to refresh the query: It might take some time and if users go back and forth between the sheets it gets annoying to wait every time you go to this sheet. It might be better to add a button and use that to trigger the macro. If the source data isn't likely to change during the workbook is open you could also use the Workbook.Open event.

 

[Herbiec09]

Thank you

Much appreciated