Bulk refreshing workbooks

Overview

There are several ways of refreshing workbooks automatically on a schedule. For example:

  1. Power BI
  2. Using SharePoint Auto Refresh
  3. Using some sort of custom-built desktop automation tool

Current problems with Power BI, and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap! SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that leaves option 3 – desktop automation.

Bulk workbook refreshing

1. How the refresh process works

This solution is semi-automatic – that is, it needs human intervention to kick off the process. The process, as it currently stands, is to…

  1. Open the bulk refresh reporting tool workbook.
  2. Select which folders you want to refresh
  3. Press the “refresh” button

The tool then systematically works through each file in the selected folder(s) and opens, refreshes, saves and closes the file. At the end of the process, you get a report of which workbooks were refreshed, and how long each one took to complete.

2. How to prepare to use the tool

  • The first thing to do is create a clean folder somewhere. It can be a folder on a shared network drive, or simply a folder on the local PC containing the VBA workbook.
  • Then create sub folders for every class of document you need to refresh on a different cycle. Here we have used time periods, but you can use anything that makes sense to your business needs.

Then place the workbooks in the appropriate subfolders.

3. Open the refresh tool

You can download a copy of the report refresh tool here » Report Refresh Tool

  • Open the Refresh Tool Workbook and update the setup data on the right hand side so that it points to your main folder. You can also specify a filename wildcard descriptor if needed.

Note the full path of the refresh folder in cell J1 and the file pattern in cell J2.

  • The next thing to do is click on the button “Update File List”. You will see the table on the left-hand side refreshes with the sub folders and file names (see example below). (Column C is hidden, but contains the full file path)
  • Now you need to manually add the names, “Daily”, “Weekly”, “Monthly”, etc., to the column “Refresh Frequency”. This is a “one off” setup task. Once you have done this you are all set to go!
  • You can manually add more files to this list (don’t forget to add the full file path though to Column C!)

4. How to operate the tool

The process for refreshing the workbooks is as follows:

  1. Open the auto refresh workbook
  2. In the table on the right-hand side, place an ‘x’ against the folders you want to refresh
  3. Click on the button ‘Refresh Selected Reports’ and go and get a coffee!

5. VBA code

Finally, here is the VBA code used for the several macros used across the report refresh tool.

VBA 
Sub RefreshAll()
    RefreshFiles
End Sub
VBA 
Sub UpdateFileList()
    ListAllFolders
    FileLister
End Sub
VBA 
Sub FileLister()
    Dim X As String
    Dim myDir As String
    Dim Y As Integer
    Dim myFileSystem As Object
    Dim myFileObject As Object
    Dim myFreq As Variant
    Dim myPattern As String
    Application.ScreenUpdating = False
    
    Set myFileSystem = CreateObject("Scripting.FileSystemObject")

    Sheets("files").Activate
    Range("B1").CurrentRegion.Offset(1, 0).Clear
    Range("B2").Select
    myPattern = Range("Pattern").Value
    
    Sheets("folders").Activate
    Range("b6").Select
    
    While Selection.Value <> ""
        myDir = Selection.Value
        myFile = myDir & myPattern
        Sheets("files").Activate
    On Error GoTo noFiles:
        X = Dir(myFile, 2)
        Do Until X = ""
            myFreq = Split(myDir, "\")
            Selection.Value = myFreq(UBound(myFreq) - 1)
            Selection.Offset(0, 1).Value = myDir
            Selection.Offset(0, 2).Value = X
            Set myFileObject = myFileSystem.Getfile(myDir & X)
            Selection.Offset(0, 3).Value = myFileObject.Size
            Selection.Offset(0, 3).NumberFormat = "#,##0"
            Selection.Offset(0, 4).Value = myFileObject.DateLastModified
            Selection.Offset(1, 0).Select
            X = Dir
        Loop
noFiles:
        Sheets("folders").Activate
        Selection.Offset(1, 0).Select
    Wend
ExitHere:
Set myFileSystem = Nothing
Set myFileObject = Nothing
Sheets("files").Activate
Application.ScreenUpdating = True
End Sub
VBA 
Sub ListAllFolders()
    Dim X As Integer
    Dim myDir As String
    
    Application.ScreenUpdating = False
    myDir = Range("StartFolder").Value
   
    If myDir = "" Then
        X = MsgBox("Enter a start folder in cell " & Range("startfolder").Address, vbOKOnly)
        GoTo ExitHere:
    End If
    
    Sheets("folders").Activate
    Range("b6").CurrentRegion.Clear
    
    If Right(myDir, 1) <> "\" Then myDir = myDir & "\"
    Range("b6").Select
    On Error GoTo Error:
    ListFolders (myDir)
    GoTo ExitHere:
Error:
    X = MsgBox("Invalid Folder Name", vbOKOnly)
ExitHere:
    Sheets("files").Activate
    Range("StartFolder").Value = myDir
    Application.ScreenUpdating = True
End Sub
VBA 
Sub ListFolders(myDir)
    Dim FS, f, f1, fc, s
    If myDir = Empty Then GoTo ExitHere:
    Set FS = CreateObject("Scripting.FileSystemObject")
    Set f = FS.GetFolder(myDir)
    Set fc = f.SubFolders
    
    For Each f1 In fc
        Selection.Value = myDir & f1.Name & "\"
        Selection.Offset(1, 0).Select
    Next
ExitHere:
    Set FS = Nothing
    Set f = Nothing
    Set fc = Nothing
End Sub
VBA 
Sub RefreshFiles()
    Dim tbRef As Object
    Dim myFile As String
    Dim toDo As Collection
    Dim myStart As Date

    Set tbRef = ActiveSheet.ListObjects("table2")
    Application.DisplayAlerts = False
    
    'create to do list
    Set toDo = New Collection
    Intersect(tbRef.ListRows(1).Range, tbRef.ListColumns(1).Range).Select
    
    While Selection <> ""
        If UCase(Selection.Offset(0, 1)) = "X" Then
            toDo.Add (Selection.Value)
        End If
        Selection.Offset(1, 0).Select
    Wend
    
    'execute todo list
    Range("A2").Select
    
    While Selection <> ""
        For Each Item In toDo
            If Selection.Value = Item Then
                myFile = Selection.Offset(0, 2) & Selection.Offset(0, 3)
                myStart = Now()
                Workbooks.Open Filename:=myFile
                Application.DeferAsyncQueries = True
                ActiveWorkbook.RefreshAll
                'Application.CalculateUntilAsyncQueriesDone
                Application.DeferAsyncQueries = False
                DoEvents
                ActiveWorkbook.Save
                ActiveWorkbook.Close
                Selection.Offset(0, 5).Value = Now
                Selection.Offset(0, 6).Value = "Done"
                Selection.Offset(0, 7).Value = Now() - myStart
                Selection.Offset(0, 7).NumberFormat = "hh:mm:ss"
                MsgBox ("waiting 2 seconds")
            End If
        Next Item
        Selection.Offset(1, 0).Select
        
    Wend
    Set toDo = Nothing
    Set tbRef = Nothing
    Application.DisplayAlerts = True
End Sub