Solution: Refresh All Code | Pivot Tables, Charts, Formulas, Forms & Link Sources

[DrHacker]

Hello everyone, here is a powerful solution for who needs to update All at once (Pivot Tables, Charts, Formulas, Forms & Link Sources) with a single Macro.

It requires Access Reference as part of the code, for that:

Alt+F11 > Tools > References > Microsoft Access

Code its divided by two sections at the same module (Module name: Functions - It can be changed if prefer)

Section One - Optimize Code Speed (Optional)

Public Sub OptimizeCodeSpeed()

  On Error Resume Next

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableAnimations = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
         
  On Error GoTo 0
                   
End Sub

Public Sub OptimizeCodeSpeedRestore()

  On Error Resume Next

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic

  On Error GoTo 0

End Sub

Section Two - Refresh All Macro

Sub RefreshAll() 'Refresh All Workbook Connections, Pivot Tables, Charts, Forms and Formulas
   
    Dim conn As Variant
    Dim pvtTbl As PivotTable
    Dim pCache As PivotCache
    Dim myChart As ChartObject
    Dim obj As AccessObject
    Dim dbs As Object
    Dim intFormCount As Integer
   
    On Error GoTo ErrorHandler
   
    Call Functions.OptimizeCodeSpeed
   
    ActiveWorkbook.RefreshAll
   
    'Connections Refresh
        Application.CalculateUntilAsyncQueriesDone
        Application.CalculateFullRebuild
        Application.CalculateUntilAsyncQueriesDone
             
        For Each conn In ActiveWorkbook.Connections
           
            conn.ODBCConnection.BackgroundQuery = False
       
        Next conn

    'Refresh all pivot tables
        For Each pCache In ActiveWorkbook.PivotCaches
            pCache.Refresh
        Next pCache
   
        For Each pvtTbl In ActiveSheet.PivotTables
            pvtTbl.RefreshTable
        Next
       
    'Refresh all Workbook Charts
        For Each myChart In ActiveSheet.ChartObjects
            myChart.Chart.Refresh
        Next myChart
       
'Refresh Access Forms, it requieres: Tools > References > Microsoft Access
        Set dbs = Application.CurrentProject
        intFormCount = dbs.AllForms.Count - 1
   
        For i = 0 To intFormCount
            If dbs.AllForms(i).isloaded = True Then
                dbs.AllForms(i).Refresh
            End If
        Next
       
    'Refresh Workbook Links Sources
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
   
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

    Exit Sub

End Sub

 

[richh]

Hi DrHacker,

Thanks for the code! Does it work on refreshing queries as well? That will be super helpful for my weekly reports.

 

[DrHacker]

Hi DrHacker,

Thanks for the code! Does it work on refreshing queries as well? That will be super helpful for my weekly reports.

I hope yes, they’re included as part of the macro:

Application.CalculateUntilAsyncQueriesDone

Application.CalculateFullRebuild

Application.CalculateUntilAsyncQueriesDone



For Each conn In ActiveWorkbook.Connections

conn.ODBCConnection.BackgroundQuery = False

Next conn

 

[pvkvimalan]

Hello everyone, here is a powerful solution for who needs to update All at once (Pivot Tables, Charts, Formulas, Forms & Link Sources) with a single Macro.

It requires Access Reference as part of the code, for that:

Alt+F11 > Tools > References > Microsoft Access

Code its divided by two sections at the same module (Module name: Functions - It can be changed if prefer)

Section One - Optimize Code Speed (Optional)

Public Sub OptimizeCodeSpeed()

  On Error Resume Next

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableAnimations = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
        
  On Error GoTo 0
                  
End Sub

Public Sub OptimizeCodeSpeedRestore()

  On Error Resume Next

    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic

  On Error GoTo 0

End Sub

Section Two - Refresh All Macro

Sub RefreshAll() 'Refresh All Workbook Connections, Pivot Tables, Charts, Forms and Formulas
  
    Dim conn As Variant
    Dim pvtTbl As PivotTable
    Dim pCache As PivotCache
    Dim myChart As ChartObject
    Dim obj As AccessObject
    Dim dbs As Object
    Dim intFormCount As Integer
  
    On Error GoTo ErrorHandler
  
    Call Functions.OptimizeCodeSpeed
  
    ActiveWorkbook.RefreshAll
  
    'Connections Refresh
        Application.CalculateUntilAsyncQueriesDone
        Application.CalculateFullRebuild
        Application.CalculateUntilAsyncQueriesDone
            
        For Each conn In ActiveWorkbook.Connections
          
            conn.ODBCConnection.BackgroundQuery = False
      
        Next conn

    'Refresh all pivot tables
        For Each pCache In ActiveWorkbook.PivotCaches
            pCache.Refresh
        Next pCache
  
        For Each pvtTbl In ActiveSheet.PivotTables
            pvtTbl.RefreshTable
        Next
      
    'Refresh all Workbook Charts
        For Each myChart In ActiveSheet.ChartObjects
            myChart.Chart.Refresh
        Next myChart
      
'Refresh Access Forms, it requieres: Tools > References > Microsoft Access
        Set dbs = Application.CurrentProject
        intFormCount = dbs.AllForms.Count - 1
  
        For i = 0 To intFormCount
            If dbs.AllForms(i).isloaded = True Then
                dbs.AllForms(i).Refresh
            End If
        Next
      
    'Refresh Workbook Links Sources
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
  
    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

    Exit Sub

End Sub

Dear @DrHacker

I recently tried using to code to update a Pivot table. I followed the procedure you mentioned.

1. Checked the Microsoft Access in the Reference section
2. Pasted both the codes in a single module
3. Executed the coed.

I was however getting an error (Images attached).

Please help me in finding a solution.

 

[DrHacker]

Dear @DrHacker

I recently tried using to code to update a Pivot table. I followed the procedure you mentioned.

1. Checked the Microsoft Access in the Reference section
2. Pasted both the codes in a single module
3. Executed the coed.

I was however getting an error (Images attached).

Please help me in finding a solution.

To solve the problem you have two ways to do it:

First Option:

Rename your module name from "Module1" to "Functions". To do it: Select module and press "F4"

Second Option:

At VBA code, replace all: "Call Functions." with "Call Module1."

If you have any additional question, please let me know. Ill try to answer you!

 

[rhombus4]

Hi
In your optimize speed

Application.EnableAnimations = False
Only appears in the first part, is that the default

ActiveSheet.DisplayPageBreaks = False
Appears in both parts as False.
How come enableanimations doesn't appear in both parts

Also, does the call exit sub need to appear before the error handler or doesn't it matter i.e.

    Call Functions.OptimizeCodeSpeedRestore
Exit sub

ErrorHandler:

    Call Functions.OptimizeCodeSpeedRestore

End sub

 

[DrHacker]

I’ll try to answer your questions as clear as possible:

Application.EnableAnimations = False
I don’t like the animations, i feel like it slows down Excel. You can add this line to restore if you want and change it to True.


ActiveSheet.DisplayPageBreaks = False
If you change it to True when restore, they will be always visible. You can comment the line or eliminate if not needed.

Why its not in both parts?
For all this instruction, they can be or not on both routines. it’s up to you.

Exit Sub
If I’m correct, it needs to go after the latest statement / routine, or inside of a conditional (IF, Case, Etc.). if you add it before error handler, it will exit earlier due the code it read 1 by 1 from the beginning to the end, executing consecutively.

 

[rhombus4]

Thanks for the explanation.

With the exit sub, I was just referring to adding exit sub before errorhandler, otherwise the error handler would always run. Guess it doesn't matter in either example unless you have some code in errorhandler which is specific to an error.

Or does it actually need an exit sub in your example as the end sub will exit it

E.g you might have a message box in error handler section which you/user would only want to see if there was an error

So instead of the end of your code which is

    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

ErrorHandler:
     Msgbox "Error in code!"
    Call Functions.OptimizeCodeSpeedRestore
 
    Exit Sub

End Sub

Have

    ActiveWorkbook.RefreshAll
    Application.CalculateFullRebuild 'Refresh all formulas, including custom ones

Exit sub

ErrorHandler:
          Msgbox "Error in code!"
    Call Functions.OptimizeCodeSpeedRestore

End Sub

 

[DrHacker]

I add "Exit Sub" due sometimes the code do not end the routine properlly for some reason. So , i recomend you to add Exit sub to it.

In the other hand, if you want to show the Error Message, intead of a Message box only, i suggest you this code:

If Err.Number <> 0 Then
 Msg = "Error # " & Str(Err.Number) & " was generated by " _
        & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
         MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If

 

[rhombus4]

Ok thanks