Results 1 to 2 of 2

Thread: RefreshAll PivotTables failing

  1. #1

    RefreshAll PivotTables failing



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •