How to Refresh Data Connection in Excel Without Opening File: 2 Methods

Method 1 – Utilize Excel Power Query to Refresh File Data Connection Without Opening

STEPS:

• Open the “reloadedData” and go to the PowerQueryR sheet.
• Select the B2 cell.
• Go to the Data tab.
• Click the Get Data icon.

• A menu bar will open.
• Choose the From File option, followed by From Excel Workbook.

• Find the “SourceData” file and click Import.

• The Navigator window will open up.
• Pick the PowerQueryS sheet from the list.
• Go to the Load icon, followed by Load To.

• The Import Data window will pop up.
• Check the Existing worksheet, hit OK.

• The data is fetched from the resource file to the store file, as shown below.

• Navigate to the Data tab, followed by Refresh All to open a menu bar.
• Choose Connection Properties.

• The Query Properties open.
• Check the Refresh every and type 1 in the Input box.
• Hit OK.

• Open the “SourceData” file and pick the PowerQueryS sheet.
• Select B8.
• Remove the value and type MIT, hit Enter.
• Press Ctrl+S.

• 1 minute later, go to “reloadedData” file to see the intended output.

Method 2 – Reload Data Connection Without Opening File Through Excel VBA

STEPS:

• Go to the “reloadedData” file and make the ExcelVBAR an active sheet.
• Pick the B2 cell.
• Follow the previously described procedure to open the Navigator window.
• Choose the ExcelVBAS sheet this time.
• Go to Load, followed by Load To.

• The Import Data window pops up.
• Check the Existing worksheet and hit OK.

• The intended data will be fetched from the resource file.

• Navigate to Developer.
• Click Visual Basic.

• Select Insert, followed by Module.

• Enter the following code into the Module box.

Public ReloadInterval As Double
Public Const Period = 30
Sub Reload()
  MsgBox "Updates will begin to occur at " & _
    "the interval of " & Period & " seconds"
  Call FirstReload
End Sub
Sub FirstReload()
  ReloadInterval = Now + TimeSerial(0, 0, Period)
  Application.OnTime _
    EarliestTime:=ReloadInterval, _
    Procedure:="ReloadConnections", _
    Schedule:=True
End Sub
Sub ReloadConnections()
  ThisWorkbook.RefreshAll
  Call FirstReload
End Sub

• Press F5 or click Run.

• The Macros window will open.
• Choose the Reload macros and hit Run.

• A small Microsoft Excel window pops, and click OK.

• Go to the “SourceData” file and choose the ExcelVBAS sheet.
• Select B8.
• Remove the value and type MIT, hit Enter or Tab.
• Press Ctrl+S.

• 30 seconds later, go to the “reloadedData” file to get the desired output.

Download Practice Workbook

The sample workbooks used during the workshop are free to download for all attendees.

Related Articles

• [Fixed]! Data Connection Not Refreshing in Excel
• How to Create a Data Source in Excel
• External Data Connections Have Been Disabled in Excel 
  
• Excel Connections vs. Queries
• Excel Queries and Connections Not Working

<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel