Refresh Many Excel Reports Automatically

Today I’d like to share a little homemade tool I whipped up to save me the tedious yet trivial recurring chore of opening several Excel workbooks and clicking the Refresh button inside each one.

Many of these are shared files saved on a team SharePoint document library, and various teammates routinely refer to these files throughout their workweek. While most team members have permission to click “Refresh” on the toolbar to sync the file with updates, some of the refreshes could take a while to complete and there was no reason for every team member to pull down data again, so I made it my weekly chore to keep these evergreen files updated for everyone to share.

These aren’t mission-critical files, the data in most is mostly only volatile over a date range in weeks or months, and only stored as shared files and kept updated purely for convenience of the end-users and of myself. If someone on the larger team needs to check one of these simple tables, they can help themselves by double-clicking a file and looking: no DBA expertise, no connection configuration, nothing. Most of these “evergreen” files are even set to be read-only so users will just look at them when needed, without ever making changes.

What didn’t work: Power Query / Get Data

Believe me, I’ve worked to help others on-board to self-service data acquisition with Power Query and other tools, but it’s been fruitless on that front. While everyone on the team has the technical permissions and are setup to retrieve what they desire from source databases themselves, they do not all share the technical aptitude nor appetite to do so. When prompted for credentials, most of the users do not remember their passwords, or quickly lock themselves out, or are frightened off by any encounter with any dialog message of any kind. And even if they could refresh the files, some involve queries that take several minutes to finish, and these are busy team members who need a fast and simple answer now.

What didn’t work: Microsoft Power Automate

I tried using Microsoft’s Power Automate to programmatically refresh these files, but it had several serious limitations. While it could automatically open an Excel Workbook and refresh it, it could only do so if the computer running the automation was either “logged off” entirely or if there was an active user session running with the screen unlocked at the trigger time: neither of which was likely to ever be the case when I wanted the automation to run, for example at 4:20 AM. And even when I did conceit to keeping the machine logged-off, the Power Automate service would frequently fail to contact the gateway running on the machine at the scheduled time and thus no actions would be performed. This would occur often, even when the machine was indeed verified to be turned on and online, reachable over the network and even confirmed that the gateway service was running. It just wouldn’t run reliably.

What didn’t work: Power BI

All of the data for all of these files is available on PowerBI, but the users that need to reference these files cannot consume it from there with anywhere near the ease they enjoy by just opening a shared Excel file. Even after we gave every user a $9.99/month Power BI Pro subscription, PowerBI is really meant as a live dashboard for data and doesn’t lend itself to rich detailed reports capable of data manipulation and exploration or (most critically) relating it to offline data they had at hand from who-knows-where. Users would always need to export the raw data, then spend time transforming that into a useful form from scratch. PowerBI is a great tool, and we use it extensively for internal productivity and client performance data dashboards, but it did not meet this need.

So, I built my own solution.

Microsoft would tell me I should look elsewhere than VBA (pry it from my cold dead hands!), other data pros would probably tell me the same thing, or to have users get their own data (these users have zero interest in doing so, and it’s a poor option even if they wanted to), and many would point me to PowerBI, but that just pushes the chore to everyone to do in parallel (and significantly increases the LOE to achieve what they get today from a shared file). So at last my goal was clear: update these evergreen files as they live today, with no change to technology or team culture.

This tool is (drumroll please) an Excel file the consists solely of a list of other Excel files. This table is creatively named “ReportList”:

In addition to a simple list of files to be refreshed, this main file consists of 3 small subroutines. Let’s look at them all.

Private Sub Workbook_Open()
       Application.StatusBar = "Will begin at " & DateAdd("s", 15, Now)
       Application.OnTime DateAdd("s", 15, Now), "MainLoop"
End Sub

This short workbook_open() sub runs when the workbook is opened, displays a small countdown in the status bar, and (after 15 seconds), then launches the main loop, also creatively named: MainLoop(). This outer loop iterates through the ReportList table and calls the RefreshFile subroutine for each one.

Sub MainLoop()
 Dim tbl As ListObject
 Dim rng As Range
 Dim basePath As String
 Dim currentFilePath As String
 Dim currentFileName As String
 Dim delaySeconds As Integer
 Set tbl=Workbooks("Refresher.xlsm").Worksheets("Main").ListObjects("ReportList")
 Set rng = tbl.ListColumns("Path").DataBodyRange
    
 For Each trow In rng.Rows

   currentFilePath = trow.Offset(0, 0).Text
   currentFileName = trow.Offset(0, 1).Text
   delaySeconds = trow.Offset(0, 2).Value
        
   Call RefreshFile(currentFilePath, currentFileName, delaySeconds)
    
  Next trow
        
  Application.StatusBar="Done, exiting at " & Format(DateAdd("s", 60, Now), "hh:mm:ss")
  Application.OnTime DateAdd("s", 60, Now), "QuitApp"
End Sub

Sub QuitApp()
    Application.Quit
End Sub

Finally, the real meat of the application: When called, the RefreshFile subroutine will:

• Open the file, ignoring any read-only recommendation that might be set
• Refresh the file, same as clicking 🔃Refresh on the toolbar
• Wait 15 seconds, then save and close the file

Afterword, program execution returns to the MainLoop where it will either move to the next file or exit if it has reached the end of the list.

Sub RefreshFile(filePath, fileFileName, waitSeconds)

    'Open the file
    Application.StatusBar = "Opening " & fileFileName
    Workbooks.Open fileName:=filePath & fileFileName, ignoreReadOnlyRecommended:=True
    
    'Refresh the file
    Application.StatusBar = "Refreshing " & fileFileName
    Workbooks(fileFileName).RefreshAll
    
    newHour = Hour(Now())
    NewMinute = Minute(Now())
    newSecond = Second(Now()) + waitSeconds
    waitTime = TimeSerial(newHour, NewMinute, newSecond)
    
    'Allow a little buffer time
    Application.StatusBar = "Will continue at " & DateAdd("s", waitSeconds, Now)
    Application.Wait waitTime
    
    'Save the file
    Application.StatusBar = "Saving " & fileFileName
    Workbooks(fileFileName).Save
    
    'Close the file
    Application.StatusBar = "Closing " & fileFileName
    Workbooks(fileFileName).Close (True)
    
    'Clear the status bar before continuing main loop
    Application.StatusBar = False
End Sub

It looks like a lot because of all the extra comments and status bar updates for style points, in reality it’s just 5 real actions. Here’s the same sub condensed:

Sub RefreshFile(filePath, fileFileName, waitSeconds)
 newHour = Hour(Now())
 NewMinute = Minute(Now())
 newSecond = Second(Now()) + waitSeconds
 waitTime = TimeSerial(newHour, NewMinute, newSecond) 

 Workbooks.Open fileName:=filePath & fileFileName, ignoreReadOnlyRecommended:=True
 Workbooks(fileFileName).RefreshAll
 Application.Wait waitTime
 Workbooks(fileFileName).Save
 Workbooks(fileFileName).Close (True)
End Sub

Requirements

For this application to perform as expected, the Power Query queries in the workbooks need to be set with background refresh off. If background refresh is enabled, the refresh program will continue onward to the next step (saving and closing the file) before the refresh is complete.

The “wait a moment” buffer time gets its data from the report list table (column C), and needs to only be a few seconds. I used 15 seconds myself and could have probably just hard coded that into the RefreshFile subroutine but I decided to leave it as a user-configurable option.

To execute this program automatically, you’ll need this macro file to be marked as a trusted file. As well, this assumes that you have saved credentials in Excel Power Query for the data sources involved in refresh.

Automating this process to recur daily is easy, just schedule a task that opens the macro file at a time of your choosing, I simply use Windows Task Scheduler and have all these reports update for me. Since it’s automatic, I even increased the frequency seven-fold and now have these update daily at 4:20 AM.

📎 Update 21-May-2023 to make the Bulk Refresh Tool.xlsm file available for download. A preview of the included macro is below.