Program to auto-update data in Excel files

Question

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?

Answer 1

In Excel

1. Create new document.
2. Press Alt+F11.
3. Double-click item Sheet1 in top-left tool window.

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

5. Adjust the list of files found in RefreshMyDocs.

6. While keeping text cursor within area between Public Sub RefreshMyDocs() and its adjacent End 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.)
7. Save your document with macro.

Hints: Test with one or two files first, then add the rest. Mind specifying files with their full paths.

Answer 2

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.

Documentation link

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.