This Excel tutorial explains how to refresh a closed workbook.
You may also want to read:
Excel refresh closed workbook
First of all, to refresh closed workbook involves opening the workbook, refresh and then close it, it is impossible to refresh closed workbook without opening it, but we can open a workbook by vba without seeing it physically opened.
Second, ask yourself what kind of refresh you want to perform.
Refresh Pivot Table ONLY
The below code refresh Pivot Table using the same Pivot Cache
Refresh External Data ONLY
The below code refresh all external data in the workbook
Refresh Pivot Table + External Data
The below code refresh all external data and Pivot Tables in the workbook
Excel VBA Code – refresh closed workbook
This question was originally asked in Microsoft Community, I answered the question and moved it here with some modifications.
The below example shows how to update all external data in the workbook.
Public Sub refreshXLS() Dim fso As Object Dim folder As Object Dim file As Object Path = "C:\Users\WYMAN\Desktop\folder\" Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(Path) With Application .DisplayAlerts = False .ScreenUpdating = False .EnableEvents = False .AskToUpdateLinks = False End With For Each file In folder.Files If Right(file.Name, 4) = "xlsx" Or Right(file.Name, 3) = "xls" Then Workbooks.Open Path & file.Name ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources ActiveWorkbook.Close True End If Next With Application .DisplayAlerts = True .ScreenUpdating = True .EnableEvents = True .AskToUpdateLinks = True End With End Sub
Explanation of VBA Code – refresh closed workbook
At the beginning of the code, I disable DisplayALerts, ScreenUpdating, EnableEvents and AskToUpdateLinks, we need to make sure no message box is prompted to interrupt the refresh code.
The Sub Procedure loop through specific folder and find all xls and xlsx files.
For each workbook, open it and refresh all links using ActiveWorkbook.UpdateLink, finally close the workbook.
As you open the workbook, you will be prompted to confirm if you want to update external source, Application.AskToUpdateLinks is used to disable the message.
Application.ScreenUpdating allows you to update without seeing the workbook open and close.