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) = "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
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
- [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)
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”
Yes AS, you are correct. The article has been revised. Thanks for noticing this and letting us know.
Regards
Mohammad Shah Miran
Team ExcelDemy
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