RefreshAll PivotTables failing

Hi

I have an excel 2007 workbook that contains cells plus pivottables (all in several worksheets) that are
linked to other workbooks. All worksheets are password protected.

The issue is, I want to refresh all the links (cells and pivottables) in at least 2 diferent situations:
1. When the workbook is opened
2. when I run VBA, from the workbook in question, that changes the external linked files and their data


My current code (for item 1.) removes the password, then runs a refreshall, then relocks with the password. However, the refresh is not
completing before the password relock occurs and hence all the data is not refreshed.

The only way I got this to work, is to remove the code that resets the password, which of course is not the final solution intention.

Please Login or Register  to view this content.

After some research, I understand that to force the refresh to complete, one needs to have the pivotcaches backgroundquery = false

So I then tried running the refresh through a loop, as below, but now I get an error, which I am sure is related to this backgroundquery
Run-time error "1004"
Application-defined or object-defined error

Please Login or Register  to view this content.

Any help would be much appreciated.

if you have pivot tables that do not get data from an external query you can't set the backgroundquery property for them and you will get a 1004 error

please note the code tags I added to your post and use them in future. thanks :-)

Hi JP

sorry, new to this forum ... where do I find these code tags? .... could not see any changes

please see forum rule 3. I'm a little surprised you can't see any difference between your post as it is now and the way you posted it!