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.
*****
************
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
*************
**************
Any help would be much appreciated.
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.
*****
Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
Dim MyPassWordis As String
MyPassWordis = "manukau01!"
' must unprotect worksheets before running the RefreshAll
For Each wSheet In Worksheets
If ActiveSheet.ProtectContents = True Then
wSheet.Unprotect PassWord:=MyPassWordis
End If
Next wSheet
ThisWorkbook.RefreshAll
For Each wSheet In Worksheets
wSheet.Protect PassWord:=MyPassWordis, UserInterfaceOnly:=True
Next wSheet
Sheets("Charts").Select
End Sub************
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
*************
Code:
Private Sub Workbook_Open()
Dim wSheet As Worksheet
Dim MyPassWordis As String
Dim pc As PivotCache
MyPassWordis = "manukau01!"
' must unprotect worksheets before running the RefreshAll
For Each wSheet In Worksheets
If ActiveSheet.ProtectContents = True Then
wSheet.Unprotect PassWord:=MyPassWordis
End If
Next wSheet
For Each pc In ActiveWorkbook.PivotCaches
pc.BackgroundQuery = False
pc.Refresh
Next
For Each wSheet In Worksheets
wSheet.Protect PassWord:=MyPassWordis, UserInterfaceOnly:=True
Next wSheet
Sheets("Charts").Select
End Sub**************
Any help would be much appreciated.