RefreshAll PivotTables failing

[philtucker]

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.

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

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.