Excel VBA Refresh All Open Workbooks
The ActiveWorkbook.RefreshAll method is used to refresh all the existing data connections and pivot tables in a workbook. The syntax of the method is given below.
Syntax:
wb.RefreshAll
wb represents a workbook type object.
Sample Code:
Sub RefreshAll_Exmple()
ActiveWorkbook.RefreshAll
End Sub
This code refreshes all the connections and pivot tables in the active workbook.
ActiveWorkbook.RefreshAll Not Working: Probable Reasons and Solutions
Reason 1 – Invalid Data Connections
If the connections being used have become invalid or out-of-date, the RefreshAll method will fail. You can check the data connections by selecting the “Data” tab and clicking on “Queries & Connections” in the “Queries & Connections” group.
A window like this will be visible on the right side, where you can check all your connections and queries.
Reason 2 – Inaccessibility of External Data
Verify that you have the proper access rights to any external data sources the workbook may contain, such as SQL Server or Oracle databases. If you don’t have the required permissions, you won’t be able to refresh the data.
Reason 3 – Conflicting Macros
If your workbook contains other macros, they might be interfering with the RefreshAll method. Try turning off any macros before rerunning the RefreshAll method.
Reason 4 – Automatic Calculation Option Turned Off
Check to ensure that your workbook’s calculation settings are set to “Automatic.” The RefreshAll method will not work if you set the calculation settings to “Manual.” To check the calculation settings, go to the “Formulas” tab and click on “Calculation Options” in the “Calculation” group.
Reason 5 – VBA Code Error
Check your code for any VBA errors that might be preventing the RefreshAll method from functioning. You can use the “Debug” tool to locate and fix any errors.
Download the Practice Workbook
Get FREE Advanced Excel Exercises with Solutions!
So, the refreshall does refresh all the links but it also maintains a lock so that other spreadsheets linked back to that first sheet cannot access the data on the sheet unless the sheet is closed. It produces the error “Method ‘Updatelink’ of object’_Workbook failed. Any thoughts of how to correct this problem without closing the spreadsheet?
Hello Roland Crespo,
You can address the issue by ensuring the links are updated without causing the lock. Try the following suggestions:
Update Links Individually: Instead of using ActiveWorkbook.RefreshAll, use ActiveWorkbook.UpdateLink Name:=LinkSources, Type:=xlExcelLinks to update each link explicitly. This can help avoid conflicts with locked resources.
Background Refresh: Ensure that the “Enable background refresh” option for queries is unchecked. Go to Data >> from Queries & Connections >> select Properties then disable background refresh.
Disable Workbook Sharing: Workbook sharing can cause locking issues during link updates. If possible, unshare the workbook.
Force Calculation: After updating the links, trigger a recalculation with Application.CalculateFull.
Regards
ExcelDemy