Each day for work, I have to open 40-50 Excel files (the newer .xlsx format) and run the Data Refresh
on all connections in order for them to pull in the latest numbers. As you can imagine, this takes quite a while to complete.
Is there some sort of program that I can use that will open all of these files at once and run the Data Refresh in the background so I don't have to go into each file and run it manually?
2 Answers
In Excel
- Create new document.
- Press Alt+F11.
- Double-click item Sheet1 in top-left tool window.
Paste the following macro into large code window:
Public Sub RefreshMyDocs() RefreshDoc "D:\Data\Sheet1.xlsx" RefreshDoc "D:\Data\Sheet2.xlsx" RefreshDoc "D:\Data\Sheet3.xlsx" RefreshDoc "D:\Data\Sheet4.xlsx" RefreshDoc "D:\Data\Sheet5.xlsx" End Sub Private Sub RefreshDoc(AbsolutePathToFile As String) Dim W As Workbook Set W = Workbooks.Open(AbsolutePathToFile) W.RefreshAll Rem W.Close 'remove Rem if you wish to close given document after refresh End Sub
Adjust the list of files found in
RefreshMyDocs
.- While keeping text cursor within area between
Public Sub RefreshMyDocs()
and its adjacentEnd Sub
, press F5 to run this code. (Note: to avoid "document is already open" error, make sure that none of listed docs is currently open – close them first.) - Save your document with macro.
Hints: Test with one or two files first, then add the rest. Mind specifying files with their full paths.
If a programmatic solution will be acceptable, Essential XlsIO can be used to auto update data in Excel files. This is completely independent of Excel.
Simple sample application with source code: link.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version
application.DefaultVersion = ExcelVersion.Excel2013;
//Open the workbook
IWorkbook workbook = application.Workbooks.Open(file, ExcelOpenType.Automatic);
//Get the 1st sheet from the workbook
IWorksheet sheet = workbook.Worksheets[0];
//Refresh all the connections in the workbook
for (int i = 0; i < workbook.Connections.Count; i++)
{
//Refresh the table
//Note : Exception will occur if no valid connection
sheet.ListObjects[i].Refresh();
}
//Save the workbook to disk.
workbook.Save();
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
The source requires Visual Studio and Syncfusion Essential Studio to compile.
The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.