Excel VBA refresh closed workbook

This Excel tutorial explains how to refresh a closed workbook.

You may also want to read:

Excel VBA refresh all Pivot Table or Pivot Cache

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

PivotTable.RefreshTable

Refresh External Data ONLY

The below code refresh all external data in the workbook

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Refresh Pivot Table + External Data

The below code refresh all external data and Pivot Tables in the workbook

Workbook.RefreshAll

Excel VBA Code – refresh closed workbook (single workbook)

The below example shows how to update all external data in a closed workbook from another workbook. Insert the below VBA in a Workbook, define the folder path and then run the Macro.

Public Sub refreshXLS()
    Path = "C:\Users\WYMAN\Desktop\test.xlsx"  'the workbook path you want to refresh

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    Workbooks.Open Path
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    ActiveWorkbook.Close True

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

Excel VBA Code – refresh closed workbook in a folder (all workbooks)

The below example shows how to update all external data in the workbook in a folder from another workbook. Insert the below VBA in a Workbook, define the folder path and then run the Macro.

Public Sub refreshXLS()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Path = "C:\Users\WYMAN\Desktop\folder\"  'the folder that contains the workbooks you want to refresh
    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.