Forum Discussion

Copper Contributor

May 27, 2022

Refresh All button, multiple Query Tables, and VBA

I have an Excel workbook containing multiple Query Tables. To refresh the data in all the Query Tables, I want the User to click the Refresh All button on the Ribbon. I have a block of VBA code I w...

Macros and VBA

alannavarro

Iron Contributor

May 27, 2022

Something similar that I did.

Right click the query > Properties

Checkbox of Enable background refresh is always selected. You need to remove that.

When you do the refresh all with the vba code, the query is going refresh and wait until it is refreshed to move to the next line of code.

I think that if you are going to have more than 1 table, then you need to look for a code that loops over all the tables in the workbook and do that step of removing the enable background refresh.

MichaelJSchulz

Copper Contributor

May 27, 2022

alannavarro

The tables are not going to be refreshed with VBA code; the tables are going to be refreshed by the user clicking the Refresh All button.

NikolinoDE
Gold Contributor
May 28, 2022
MichaelJSchulz
Is that what's not working?
Have you pressed the small file?...or can't you press the button at all?
However, a file would be appropriate to reproduce this.
- MichaelJSchulz
  Copper Contributor
  May 28, 2022
  The Refresh All button does work. The Refresh All button does refresh the data in all the tables.
  The question is how to have a block of VBA code run automatically once all the tables have finished being refreshed.
  - NikolinoDE
    Gold Contributor
    May 30, 2022
    MichaelJSchulz
    
    Option Explicit Private Sub Workbook_Open() Selection.QueryTable.Refresh BackgroundQuery:=False 'Worksheets(?).Range(?).querytable.refresh backgroundquery:=false (alternate code) Call YourMacro End Sub
    
    I'm not sure if that's what's meant...but it doesn't hurt to send :))