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

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

• 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.

⧭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

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

• 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.