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

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 down in real time. We want to auto-refresh the values without opening the file.

Auto Refreshing Excel File Data Without Opening

Steps:

Go to 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

Sets 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 refreshed 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 Rename a Workbook in Excel

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

Steps:

Replace the previous macro with the following.

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

Refers to the variables as Object.

Dim mrf As Object
Dim mfolder As Object
Dim mfile As Object

Sets 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

The macro auto-refreshes the files using VBA WITH.

Press F5 to auto-refresh the files inside the folder. For opened files, press F9 to refresh the active worksheet.

Download the Excel Workbook

Auto Refresh.xlsm

<< Go Back to Workbook in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Public Sub AutoRefreshFile() Dim mrf As Object Dim mfile As Object Dim mPath As String With Application.FileDialog(msoFileDialogFilePicker) If .Show = -1 Then mPath = .SelectedItems(1) Else Exit Sub End If End With Set mrf = CreateObject("Scripting.FileSystemObject") With Application .DisplayAlerts = False .ScreenUpdating = False .EnableEvents = False .AskToUpdateLinks = False End With If Right(mrf.GetFileName(mPath), 4) = "xlsx" Or Right(mrf.GetFileName(mPath), 3) = "xls" Then Dim wb As Workbook Set wb = Workbooks.Open(Filename:=mPath) If wb.ReadOnly = True Then wb.ChangeFileAccess Mode:=xlReadWrite End If If wb.ReadOnly Then wb.ChangeFileAccess Mode:=xlReadWrite End If wb.UpdateLink Name:=wb.LinkSources wb.ChangeFileAccess Mode:=xlReadOnly wb.Close SaveChanges:=True End If With Application .DisplayAlerts = True .ScreenUpdating = True .EnableEvents = True .AskToUpdateLinks = True End With End Sub

Private Sub Workbook_Open() ThisWorkbook.Application.Visible = False Call AutoRefreshFolder ThisWorkbook.Application.Visible = True ThisWorkbook.Close Application.Quit End Sub Public Sub AutoRefreshFolder() Dim mrf As Object Dim mfolder As Object Dim mfile As Object mPath = "C:\Users\Alok Paul\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

Public Sub AutoRefreshFolder() Dim mrf As Object Dim mfolder As Object Dim mfile As Object mPath = "C:\Users\Alok Paul\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.RefreshAll 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

16 Comments

AS Mar 24, 2023 at 3:30 PM

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
  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
AnnaWho May 24, 2023 at 4:58 AM

Does it save the file that it opened?
- Reply
  Sishir Roy May 24, 2023 at 1:05 PM
  
  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
  - Reply
    Bismark Jul 11, 2023 at 6:57 PM
    
    Hi SISHIR,
    Does this run one time or multiple times and what happens to the excel file where the ma to sits?
    - Sishir Roy Jul 13, 2023 at 5:21 PM
      
      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
Kaja pohar Jan 3, 2024 at 1:51 PM

hi,

this macro is really a blast, however, The files I want to update I set to “always open as read only” because I don’t want end users to make any changes.
Because of that the macro gets an error.
How should I adjust the macro to work properly? so when the file opens and there is a popup ” the author would like you to open “the file” as read-only unless you need to make changes. Open as read-only?” and of course the “no” must be chosen. The macro opens the file as read only and then wants to save it as copy of the file.
- Reply
  Lutfor Rahman Shimanto Jan 14, 2024 at 1:38 PM
  Hello KAJA POHAR
  
  Thanks for your compliments. Your appreciation means a lot to us. You wanted to work with a read-only Excel file; however, you had issues with the existing VBA code.
  
  I am delighted to inform you that I have developed an Excel VBA Sub-procedure by modifying the existing VBA code. The main idea behind the sub-procedure is it changes the File Access property to xlReadWrite, and before closing, it changes the File Access back to xlReadOnly.
  
  Follow these steps:
  
  1. Press Alt+F11 to open VBE.
  
  2. Click on Insert followed by Module.
  
  3. Paste the following code in the module and Run.
```
Public Sub AutoRefreshFile()

    Dim mrf As Object
    Dim mfile As Object
    Dim mPath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show = -1 Then
            mPath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    Set mrf = CreateObject("Scripting.FileSystemObject")
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With
     
    If Right(mrf.GetFileName(mPath), 4) = "xlsx" Or Right(mrf.GetFileName(mPath), 3) = "xls" Then
        Dim wb As Workbook
        Set wb = Workbooks.Open(Filename:=mPath)
        
        If wb.ReadOnly = True Then
            wb.ChangeFileAccess Mode:=xlReadWrite
        End If
        
        If wb.ReadOnly Then
            wb.ChangeFileAccess Mode:=xlReadWrite
        End If
        
        wb.UpdateLink Name:=wb.LinkSources
        
        wb.ChangeFileAccess Mode:=xlReadOnly
        
        wb.Close SaveChanges:=True
    
    End If
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With

End Sub
```
  As a result, you will see an output like the following GIF.
  
  Hopefully, the idea will help you to reach your goal. Good luck!
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy
Jasmina Jan 10, 2024 at 7:54 PM

Hello,

Pelase help me. I have .xlsx files that have links and it auto refreshes when open.
I am a total idiot when it comes to VBA but I need to do something thus it takes a lot time to open every singe .xlsx from that folder. I have tried to do something with VBA code for a whole folder but I did it wrong so it displays error.
What I have done is opened .xlsm file named vba.content with all my linked files and automatic updates and in it opened VBA, insert => module and in that module paste macro for entire folder. But, I do not know what to change.
folder that needs to be programed has a path E:\Jasmina\Excell-tips\TestVAB\Kupci-upl-test.
Please tell me what to do to fix it.

Jasmina
- Reply
  Maruf Hasan Jan 16, 2024 at 4:55 PM
  
  Hello Jasmina,
  I checked the code in Method 2. It is working fine. I presume you forgot to add a ‘backslash(\)’ after the path. The editor can not detect the path without backslash. Copy the following line and paste in your code. Hope it will work!
  
  mPath = "E:\Jasmina\Excell-tips\TestVAB\Kupci-upl-test\"
  
  Regards
  Maruf Hasan
  ExcelDemy
andrew Feb 6, 2024 at 10:41 PM

In this case, you don’t hace to open the file that needs to be updated, but you do still need to open the file with the macro right?
- Reply
  Lutfor Rahman Shimanto Feb 22, 2024 at 4:08 PM
  
  Hello ANDREW
  
  Yes, that’s correct. In both methods described in the article, you would need to open the file containing the macro to auto-refresh the target Excel files. However, once the macro is executed, it will open and refresh the specified Excel files without manually opening them individually.
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy
Andrew G Feb 7, 2024 at 6:06 PM

Hello!

Thank you for all of this great info. The only “issue” for me is that I still have to open a file and run the macro, which of course it is a lot more efficient than having to open all of the other files tha the macro targets, but I wonder if there is a way to do this without opening any file whatsoever and to program the macro to run periodically.

Thanks again!
- Reply
  Lutfor Rahman Shimanto Feb 27, 2024 at 9:49 AM
  Hi ANDREW G,
  
  Thank you very much for reading our articles.
  
  You wanted to know a way to run the macro periodically without opening any file that will auto-refresh all the Excel files in a certain folder.
  
  Here, I will discuss a way to fulfill your requirements. But to run a macro you need to open an Excel file and insert a macro in it. After that, the macro will refresh all your files in the mentioned folder. We will use the Task Scheduler feature of Windows to run the macro periodically.
  First, insert the following macro in an Excel file named Refresh. The macro will be inserted into the workbook.
  
  Then, save and close the macro and workbook.
  
  The first part of the macro is a workbook event that will run whenever the workbook opens.
  Private Sub Workbook_Open() ThisWorkbook.Application.Visible = False Call AutoRefreshFolder ThisWorkbook.Application.Visible = True ThisWorkbook.Close Application.Quit End Sub Public Sub AutoRefreshFolder() Dim mrf As Object Dim mfolder As Object Dim mfile As Object mPath = "C:\Users\Alok Paul\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
  
  Type Task Scheduler in the Search box.
  
  Then click on the Open option of the application.
  
  In the Task Scheduler window, click the Create Task option from the Actions section.
  
  In the Create Task window, go to the General tab.
  
  Then, insert the name of the task in the Name box.
  
  Next, go to the Triggers tab and click on New.
  
  In the New Trigger window, mark Daily in the Settings section.
  
  Next in the Advanced settings section, mark Repeat task every and choose 5 minutes. You can any other time.
  
  Finally, press OK.
  
  Then, click on the New option in the Actions tab.
  
  The New Action window appears.
  
  Insert the path of the Excel application in the program/script section and the path of the Excel file containing the macro.
  
  Finally, press OK.
  
  After that close the Task Scheduler and the macro will run every 5 minutes to refresh the Excel files inside the mentioned folder.
  Best Regards,
  Alok Paul
  Team ExcelDemy

Parvez Alam May 24, 2024 at 11:52 AM

Hi LUTFOR RAHMAN SHIMANTO,

Nicely explain the process.
Will it update all the Queries related power query?

Lutfor Rahman Shimanto May 26, 2024 at 9:40 AM

Hello Parvez Alam

Thanks for the compliment! You can include the “RefreshAll” command to refresh all Power Query connections. Don’t worry! I have improved the code a bit to fulfil your goal.

Excel VBA Code:

I hope you have found the code you were looking for. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

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

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

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

Leave a reply Cancel reply