Excel VBA refresh closed workbook

This Excel tutorial explains how to refresh a closed workbook.

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.

This question was originally asked in Microsoft Community, I answered the question and moved it here with some modifications.

Excel VBA Code – refresh closed 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, finally close the workbook.

As you open the workbook, you will be prompted to confirm if you want to update external source, Application.AskToUpdateLinks disable the message.

Application.ScreenUpdating allows you to update without seeing the workbook open and close.

 

Excel VBA refresh closed workbook