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

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) = "xlsx" 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

			

Auto Refresh Entire Folder

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) = "xlsx" 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo