Excel Forum

Pivot Tables via Macro Upload Images Link
Michael Soprani-Dersch
8 months ago

HI all,

I have the following case:
I have several excel files, which contain pivot tables. These files need to be refreshed regularly. I do not want to do it manually, so I created a code in a separate file.
The code shoule open each of the specified excel, rehresh the pivot table, save and close.
The interesting part is open file not a problem, then the code is:
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close

If you debug step by step all is fine, but if you run the code you get an error between RefreshAll and Save. Reason: The Save command is executed WHILE the pivt table is still refreshing and this generates an error, whereas if you allow the rferhesh t complete, no error.
So, is there a way to tell the system to wait until refrehs is complete, before moving to the Save command?

Alex Hedley
8 months ago

What do you have set on

Data > Connections > Properties > enable background refresh

Richard Rost
8 months ago

Mac is all you, Alex. :)

Alex Hedley
8 months ago

Is it not Mac as in Macro and just the subject got truncated?

Richard Rost
8 months ago

Don't I feel silly? :)

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Excel Forum Comments