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.
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 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
- How to Auto Refresh Pivot Table without VBA in Excel (3 Smart Methods)
- [Fixed!] Excel Cells Not Updating Unless Double Click (5 Solutions)
- How to Refresh Chart in Excel (2 Effective Ways)
- How to Automatically Update a Pivot Table When Source Data Changes
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
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
- [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
- How to Disable Background Refresh in Excel (2 Handy Methods)
- How to Refresh Pivot Table in Excel (4 Effective Ways)
- VBA to Refresh Pivot Table in Excel (5 Examples)
- How to Auto Update with Interval 5 Seconds in Excel
- Pivot Table Not Refreshing (5 Issues & Solutions)
- How to Auto Refresh Pivot Table in Excel (2 Methods)