How to Refresh Data Connection in Excel Without Opening File

Microsoft Excel is a powerful software. We can carry out a variety of operations on a given dataset using Excel’s tools and capabilities. We need access to an Excel file to update data connections. Data must be updated in the Excel file when fetched from other sources. Refreshing the data connection in a typical way could take a long time. Manually restoring the data connection may become very time-consuming. This article will discuss two simple ways to reload data connections without reopening the Excel file. Therefore, go through these 2 easy ways to Refresh Data Connection in Excel Without Opening File.

Refresh Data Connection in Excel Without Opening File: 2 Easy Ways

For example, we shall investigate a sample resource from which we will get data. In this instance, we will utilize the SourceData Excel file. We will need an additional Excel file for data storage. For this, we will use the reloadedData file. You can get both files from the preceding section. There are two columns in our resource file labeled University Name and Establishment Year. First, we will import this data from SourceData into reloadedData and then modify SourceData. Second, we will wait 30 seconds or 1 minute to get the new data in the reloadedData file. In addition, I should have mentioned that I wrote this essay using Microsoft Excel 365.

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

You can import data from several sources and then clean, convert, and restructure it using the advanced analytics tool Power Query, which is accessible in Excel. In this context, we will fetch data from a resource file and store it in another file mentioned earlier. After that, we will refresh the data connection within a time interval. To complete the task, please follow the instructions below.

STEPS:

• First, open the “reloadedData” and go to the PowerQueryR sheet.
• Then, select the B2 cell.
• Second, go to the Data tab.
• After that, click the Get Data icon.

• Subsequently, a menu bar will open.
• Later, choose the From File option, followed by From Excel Workbook.

• Next, find the “SourceData” file and click Import.

• Due to this, the Navigator window will open up.
• Now, pick the PowerQueryS sheet from the list.
• Then, go to the Load icon, followed by Load To.

• As a result, the Import Data window will pop up.
• Latterly, check the Existing worksheet, then hit OK.

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

• Again, navigate to the Data tab, followed by Refresh All to open a menu bar.
• Next, choose Connection Properties.

• As a consequence of this, Query Properties open.
• Currently, check the Refresh every and type 1 in the Input box.
• Afterward, hit OK.

• Now, open the “SourceData” file and pick the PowerQueryS sheet.
• After that, select B8.
• Next, remove the value and type MIT, then hit Enter.
• Immediately, press Ctrl+S.

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

Read More: How to Create Excel Data Connection to Another Excel File

2. Reload Data Connection Without Opening File Through Excel VBA

Visual Basic for Application is referred to as VBA. Microsoft created the programming language known as VBA. Users may use the VBA programming language to access Excel-incompatible functionalities. We will use VBA to reload the Excel Data connection without opening the file in this part. Please follow the guidelines below to complete the assignment.

STEPS:

• First, Go to the “reloadedData” file and make the ExcelVBAR an active sheet.
• Next, pick the B2 cell.
• Second, follow the previously described procedure to open the Navigator window.
• Next, choose the ExcelVBAS sheet this time.
• Then, go to Load, followed by Load To.

• At this point, the Import Data window pops up.
• Presently, check the Existing worksheet and hit OK.

• As a result, the intended data will be fetched from the resource file.

• After that, navigate to Developer.
• Then, click Visual Basic.

• Later, select Insert, followed by Module.

• Next, 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

• After that, press F5 or click Run.

• Subsequently, the Macros window will open.
• Latterly, choose the Reload macros and hit Run.

• Then, a small Microsoft Excel window pops, and click OK.

• Now, go to the “SourceData” file and choose the ExcelVBAS sheet.
• After that, select B8.
• Next, remove the value and type MIT, then hit Enter or Tab.
• Instantly, press Ctrl+S.

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

Read More: Excel VBA: How to Refresh All Data Connections

Download Practice Workbook

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

Conclusion

You can now refresh the data connection without opening an Excel file using the methods mentioned in this section. Please share your ideas and views if you conceive alternate ways to complete the assignment. Please use the area given below to provide any questions, comments, or suggestions.

Related Articles

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

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