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 VBA AskToUpdateLinks Property

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.

 

14 thoughts on “Excel VBA refresh closed workbook”

  1. This has generally worked for me but my workbooks also have signatures and marked as final sections. Is there another application property that would suppress those too?

    Reply

  3. I have been unable to properly modify your code. properly and was wondering if you might be able to help.
    I have 3 different workbooks, I wish to run a macro on WB1 to make WB2 refresh its formulas, some of which are referencing WB3.
    I want to make this dynamic though so instead of having a defined path (like your example) have the path pull the location of where WB1 is and search that whole folder.
    One issue to overcome here is to make the search skip over the current sheet as otherwise it will try to open and update it when it is already open.

    Any help would be greatly appreciated

    thank you in advanced

    Reply

  4. Hi Nate, not sure if I get your question right, it sounds like you have a WB4 in the same folder as WB1 – WB3, and you only want to refresh WB1. Change the For statement a little bit
    For Each file In folder.Files
    If file.Name = "WB3.xlsx" Then
    Workbooks.Open Path & file.Name
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    ActiveWorkbook.Close True
    End If
    Next

    If you want to loop through all files except for WB4, then

    For Each file In folder.Files
    If file.Name <> "WB4.xlsx" Then
    Workbooks.Open Path & file.Name
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    ActiveWorkbook.Close True
    End If
    Next

    Reply

  5. Hi Wyman,

    thank you for posting this. I am really hoping this can help me with a problem i have been trying to solve for a while.

    I am trying to use this code in PowerPoint to open just one Excel file and update its external links. I am assuming PowerPoint VBA would work the same, right? The code i have looks like the following (below), but i am getting an error code “Compile error: method or data member not found”. Could you advice on why i might be getting this message?

    Public Sub refreshXLS()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Path = “H:\DeptShare\EW CAPS PERFORM\EW CAPS OUTLOOK\EW CAPS OUTLOOK\Scheduling”
    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 file.Name = “ViewableGelSchedulev3.xlsx” 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

    Thank you!
    Katie

    Reply

    1. Hi Katie, you need to create Excel object first in order to use Excel methods, otherwise you can only use powerpoint methods

      Public Sub refreshXLS()
      Dim fso As Object
      Dim folder As Object
      Dim file As Object
      Dim XL As Object
      Set XL = CreateObject("Excel.Application")
      Path = "C:\Users\WYMAN\Desktop\folder\"
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(Path)

      With XL
      .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
      XL.Workbooks.Open Path & file.Name
      XL.ActiveWorkbook.UpdateLink Name:=XL.ActiveWorkbook.LinkSources
      XL.ActiveWorkbook.Close True
      End If
      Next

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

      Reply

  6. Thanks for the help so far!
    I was able to skip the WB in which I was running the program using the following:

    For Each file In folder.Files
    If file.Name = “filename.xlsm” Or file.Name = “~$filename.xlsm” Then
    Else
    Workbooks.Open Path & file.Name
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    ActiveWorkbook.Close True
    End If
    Next

    Using didn’t work maybe because it is text and not a number. Also I had to include the ~$ because it creates a temporary file before saving if there is a way around that I’d love to know as well.

    My next question is how do I make the if statement reference the file which the macro is running in without physically typing the file name into the vba code. This is needed because the end user will be changing the file name for different applications. I tried using ActiveSheet.Name but that didn’t seem to work.
    My other thought was to set 2 variables at the beging of the program so the ActiveSheet.Name and hve the if statemetn compare each file name to that variable before continuing to the else but I am not having much luck with this. Maybe I need to do a Dim …. As Object or Set … = like you did for the other variables.

    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Path = Application.ActiveWorkbook.Path & “\”
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set folder = fso.GetFolder(Path)
    current = ActiveSheet.Name
    current_temp = “~$” & ActiveSheet.Name

    I was able to accomplish this with the file path by using:
    Path = Application.ActiveWorkbook.Path & “\”

    Thoughts?

    thanks!

    Reply

    1. Hi Nate

      1. <> would definitely work on text
      2. It would be cleaner to use NOT LIKE to handle temporary file:
      If NOT filename Like "*Book1*" Then
      3. Use ThisWorkbook.Name to return the workbook name that contains the running Macro. Activesheet.Name wouldn’t work, because it is referencing the worksheet, not the workbook(the file)

      Reply

  7. Thank you again! I now have something that fully functions. I figured I’d post the final code incase someone ran into the same issues down the rode:

    Public Sub refreshXLS()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Path = Application.ActiveWorkbook.Path & “\”
    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) = “xlsm” And Not file.Name Like “*” & ThisWorkbook.Name 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

    Awesome work, thanks for the help!

    Reply

  8. Very very interesting as subject…!!
    Since longtemp I looked for a solution to this but I did not find.

    I have two workbook connected, workbookA and WorkbookB (Data receiver)
    The path of the WorkbookB is: C:\Users\ANEM\Desktop\Base de donnée_Fiche technique_Wilaya\Liste_source.xlsm

    I tried to apply the code with a click button on workbookA but the test show me error in Path.

    How I should apply this code ?

    Thank you in advance.

    Reply

    1. Hi Adnan, I am not 100% sure if you really can use “é” in VBA. Anyway I updated a section in my post called “refresh closed workbook (single workbook)”, you can give it a try

      Reply

  9. Thnak you Wyman,
    Can you guide me a little more ?
    Ok, i inserted this code in the workbook I want to refresh it at the module level, and it did not give any error when I executed it, but after closing the dialog, it disappeared completely.
    I would like to know, where I have to insert this code, at the modules or at Thisworkbook or at Excel projet object?
    Merci pour les éclaircissements.

    Reply

  10. Thanks again Wyman,
    I always got a result, but I found another alternative that worked well and I would like to share it, here it is:

    Sub RefreshTest()
    Workbooks.Open Filename:=” The folder ”
    Sheets(“Récepteur”).Select
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    ActiveWindow.Close
    End Sub

    Anyways, thank you Wyman.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *