RefreshAll PivotTables failing

Phil

New member
Joined
Mar 27, 2013
Messages
2
Reaction score
0
Points
0
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.

*****

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.
 

Phil

New member
Joined
Mar 27, 2013
Messages
2
Reaction score
0
Points
0
I have since found an application event 'aftercalculate' which sounds like it might solve the problem but I am not sure how to use. Any help?
 
Top