How to Auto Refresh Excel File Without Opening (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Often, users need auto-refresh of Excel files that contain volatile functions or Query Connections. Only VBA Macros do auto-refresh Excel files without opening. Query Connection for auto-refresh doesn’t support auto-refresh for closed files. Query Connection properties can only auto-refresh the data of an opened Excel file after a specific set interval. The process is that another xlsm Excel file runs a macro to auto-refresh an assigned closed Excel file. In this article, we demonstrate the application of two macros to auto refresh Excel file without opening it.


Download Excel Workbook

The helper Macro Excel file runs the macro to auto-refresh target files or files.


2 Easy Ways to Auto Refresh Excel File Without Opening

A different Excel file runs macros that open and close the assigned Excel file to auto-refresh by opening. Since the “Countdown” Excel file contains functions that are updated upon opening.

Go through the below section to be able to auto-refresh Excel files automatically without opening them.


Method 1: Using VBA Macro to Auto Refresh a Specific Excel File Without Opening

The Countdown Excel file has TODAY() and NOW() functions for counting countdowns in real-time. We want to auto-refresh the functions’ values without opening the file.

Auto Refreshing Excel File Data Without Opening

Steps: Use a different Excel file to insert a Visual Basic Module (Alt+F11 > Insert > Module or Developer tab > Visual Macro > Insert > Module). In the module, paste the following macro.

Public Sub AutoRefreshClosedFile()
FilePath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\Countdown"
 With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With
Workbooks.Open FilePath
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
ActiveWorkbook.Close True
 With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
 End With
End Sub 
			

Macro to Auto Refresh Specific File

Macro Explanation

  • Set the File Path within the device for auto-refresh.
  • VBA WITH statement executes multiple commands not to alter the file.
With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With
  • Open opens the assigned file.
  • Close closes the opened file.
  • Another VBA WITH updates the file after auto-refreshing.

  • Press F5 to run the macro. Excel opens the Countdown.xlsx file. As the file contains volatile functions such as TODAY() and NOW(), the file gets auto-refresh upon opening in the background. You can check the outcome by reopening the file.

Tips: In case users need to auto-refresh an opened Excel file automatically, use the following macro or just macro lines with commands. Of course, change the Range (i.e., A1:D14) and timing of auto refreshment (i.e., “s”, 30) using Application.OnTime in the macro.

Sub AutoCalculationRange()
Range("A1:D14").Calculate
Application.OnTime DateAdd("s", 30, Now), "AutoCalculationRange"
End Sub

Read More: How to Refresh Excel Sheet Automatically Using VBA (4 Methods)


Similar Readings


Method 2: Executing Auto Refresh for All Excel Files Within a Folder

Alternative to method 1, users can auto-refresh all the Excel files within a folder. Executing this macro will allow users to open, thus auto refresh Excel files inside an assigned folder.

Steps: Replace the previous macro with the latter one.

Public Sub AutoRefreshFolder()
Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object
mPath = "C:\Users\maruf\Desktop\Softeko\Auto Refresh Excel File Without Opening\"
Set mrf = CreateObject("Scripting.FileSystemObject")
Set mfolder = mrf.GetFolder(mPath)
 With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
 End With
 For Each mfile In mfolder.Files
    If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xls" Then
            Workbooks.Open mPath & mfile.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

			

Code Image

Macro Explanation

  • Refer to the variables as Object.
Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object
  • Set the mPath to provide a specific folder.
  • VBA WITH statement executes multiple commands without altering the file.
  • VBA FOR and IF go through each xlsx file to remotely open and close them.
 For Each mfile In mfolder.Files
    If Right(mfile.Name, 4) = "xlsx" Or Right(mfile.Name, 3) = "xls" Then
            Workbooks.Open mPath & mfile.Name
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
            ActiveWorkbook.Close True
    End If
 Next
  • Finally, the macro auto-refreshes the files using VBA WITH.

  • Go to Run > Run Sub/UserForm F5 to auto-refresh the files inside the folder. For opened files, press F9 to refresh the active worksheet. Finally, outcomes similar to method 1 are achieved.

Read More: How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)


Conclusion

This article discusses macro variants to auto-refresh Excel files without opening them. Use any of the described macros to do so. We hope these macros open the xlsx files and thus auto refresh them.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

6 Comments
  1. If Right(mfile.Name, 4) = “xlsx” Or Right(mfile.Name, 3) = “xlsx” sholud be
    If Right(mfile.Name, 4) = “xlsx” Or Right(mfile.Name, 3) = “xls”

    • Reply Avatar photo
      Mohammad Shah Miran Mar 27, 2023 at 2:37 PM

      Yes AS, you are correct. The article has been revised. Thanks for noticing this and letting us know.

      Regards
      Mohammad Shah Miran
      Team ExcelDemy

  2. Does it save the file that it opened?

    • Hello ANNAWHO,
      The workbook (the one that is indicated by the ‘FilePath’) that is opened and changed with new data will be saved before it is closed in the supplied code of this article. The line “ActiveWorkbook.Close True” indicates that the file will be saved with any changes made to it before closing.
      However, the workbook containing the VBA code itself, the file where the macro resides, will not be saved as a result of running this code. The workbook that contains the code isn’t saved by the code itself.

      Best Regards,
      Sishir Roy

      • Hi SISHIR,
        Does this run one time or multiple times and what happens to the excel file where the ma to sits?

        • Hi BISMARK,
          The provided code can be executed multiple times. Each time you run the subroutine, it refreshes the data of a closed Excel file without manually opening it.
          The code does not alter or have any impact on the Excel file where the macro is located. The macro’s file doesn’t change. The code opens the separate Excel file indicated by the FilePath variable, then updates the links in that file before closing it. The actions are performed on the opened file, not the file where the macro code is located.
          It’s important to remember that if the macro is kept in the same workbook as the file you need to edit, you may modify the code to refer to the current workbook rather than using a distinct FilePath variable.
          Best Regards,
          Sishir Roy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo