Building a VBA-Powered Custom PivotTable Refresh Scheduler: Automate Multi-Source Data Model Updates, Sequence Dependencies, and Error Recovery Across Enterprise Workbooks

Introduction

Picture this: it's 7:45 AM on a Monday, and your Sales Director is about to present the weekly performance review to the executive team. The dashboard workbook pulls from three external SQL databases, two Power Query connections, a shared network workbook maintained by Finance, and a local flat-file feed from the ERP system. Somebody needs to refresh all of it — in the right order, because the KPI calculations in the Data Model depend on the transaction table being loaded before the product hierarchy, which must be loaded before the revenue rollups. And if any single source fails, the whole cascade could silently corrupt the numbers that leadership is about to present.

If that scenario sounds familiar, you already know the problem. Manual PivotTable refresh works fine in a ten-connection workbook where one person owns everything. It falls apart the moment you have multiple data sources with dependencies, multiple workbooks that need to stay synchronized, and zero tolerance for silent failures. What you need isn't a "refresh all" button — you need a scheduler that understands sequence, recovers from errors gracefully, and tells you exactly what happened and why.

By the end of this lesson, you'll have built a production-ready VBA refresh scheduler from scratch. You'll handle the full complexity of enterprise data environments: sequenced multi-source refreshes, cross-workbook coordination, structured error recovery with retry logic, and a logging system that gives you a forensic trail every time the scheduler runs.

What you'll learn:

• How to enumerate and control PivotTable caches and Data Model connections programmatically
• How to build a dependency-aware refresh sequence using a configuration-driven approach
• How to implement retry logic and error recovery that distinguishes transient failures from fatal ones
• How to coordinate refresh operations across multiple open workbooks and external files
• How to create a persistent refresh log and an operator-facing status dashboard using VBA

---

Prerequisites

This lesson assumes you are comfortable with VBA fundamentals — if you need a refresher on variables, loops, and control flow, work through VBA Variables, Data Types, and Control Structures: Building Robust Excel Automation first. You should also have a solid grasp of Excel's object model, particularly how workbooks, worksheets, and ranges relate to each other (covered in Understanding Excel's Object Model: Workbooks, Worksheets, Ranges, and Cells as the Foundation for VBA Automation). Familiarity with PivotTables and the Data Model — what they are and why they exist — is essential; if you haven't yet explored Master Power Pivot and Excel Data Model for Million-Row Analysis, do that before continuing.

You'll need Excel 2016 or later with Power Pivot enabled, and access to at least one workbook with existing PivotTable connections. Administrative rights to the VBA editor (ALT+F11) are assumed.

---

Understanding the Architecture Before Writing a Line of Code

The single biggest mistake people make when automating PivotTable refreshes is jumping straight to ThisWorkbook.RefreshAll. That one-liner exists, it works, and it will eventually burn you — because RefreshAll has no concept of sequence, no error handling, and in asynchronous mode it returns control to your code before the refresh is actually complete. You end up with VBA that cheerfully declares success while Power Query is still pulling 2 million rows from SQL Server in the background.

Before writing a single procedure, let's establish the architecture of what we're actually building:

The Refresh Configuration Table — a named Excel Table (ListObject) that serves as the control manifest. Each row defines one refresh target: its type (PivotCache, WorkbookConnection, PowerQuery, ExternalWorkbook), its sequence number, its dependency chain, retry settings, and expected completion criteria.

The Scheduler Engine — a VBA class module (RefreshScheduler) that reads the configuration, resolves dependencies, and executes refresh operations in order. It's the brain.

The Refresh Executor — a set of specialized procedures that know how to refresh each source type correctly, including synchronous wait logic.

The Error Handler & Retry Manager — structured error recovery that categorizes failures, applies exponential backoff for transient errors, and escalates fatal errors without corrupting downstream data.

The Audit Log — a persistent worksheet log that records every operation with timestamps, durations, error codes, and retry counts.

The Status Dashboard — a simple operator interface, optionally surfaced through a UserForm, that shows real-time progress during a scheduler run.

This layered design means each component has one job. When something goes wrong in production — and it will — you can isolate the problem immediately because the architecture keeps concerns separated.

---

Setting Up the Refresh Configuration Table

Create a new worksheet called RefreshConfig. On it, create a named Excel Table with the following columns. Use Insert → Table (CTRL+T) and name it tbl_RefreshConfig through the Table Design tab.

Column	Description
RefreshID	Unique integer identifier
RefreshName	Human-readable label (e.g., "SQL Transactions Load")
SourceType	PivotCache, WorkbookConnection, PowerQuery, ExternalWorkbook
SourceIdentifier	Cache index, connection name, query name, or file path
SequenceOrder	Integer — lower numbers run first
DependsOn	Comma-separated list of RefreshID values that must complete before this runs
MaxRetries	Integer, typically 1–3
RetryDelaySeconds	Seconds to wait between retries
TimeoutSeconds	How long to wait before declaring a refresh hung
OnFailure	Skip, Abort, or RetryThenAbort
IsEnabled	TRUE/FALSE — lets you disable individual items without deleting rows
LastStatus	Scheduler writes results back here
LastRunTime	Scheduler writes the timestamp back here

This table is the heart of your system. It makes the scheduler declarative — you describe what you want to happen, and the engine figures out how. When a new data source comes online, you add a row. When a dependency changes, you edit the DependsOn column. No code changes required.

Here's a realistic sample configuration for a sales reporting workbook:

RefreshID	RefreshName	SourceType	SourceIdentifier	SequenceOrder	DependsOn	MaxRetries	RetryDelaySeconds	TimeoutSeconds	OnFailure	IsEnabled
1	Product Hierarchy	PowerQuery	qry_ProductHierarchy	10		2	30	120	RetryThenAbort	TRUE
2	SQL Transactions	WorkbookConnection	cn_SalesTransactions	20	1	3	60	300	RetryThenAbort	TRUE
3	Finance Actuals	ExternalWorkbook	\\server\finance\actuals.xlsx	30	1	1	30	60	Skip	TRUE
4	Revenue Rollup Model	PivotCache	1	40	2,3	1	10	60	Abort	TRUE
5	Sales KPI Dashboard	PivotCache	2	50	4	1	10	60	Abort	TRUE

Notice that items 4 and 5 depend on earlier items completing successfully. Item 3 (the Finance Actuals external workbook) has OnFailure = Skip because that source is sometimes unavailable, and the Revenue Rollup can still run with partial data — the business has accepted that trade-off. Item 2 has OnFailure = RetryThenAbort because if the SQL connection fails after all retries, running the downstream Data Model would produce stale numbers, which is worse than no numbers.

---

Building the Core Class Module: RefreshScheduler

In the VBA editor (ALT+F11), insert a new Class Module and name it RefreshScheduler. This class will own the entire refresh lifecycle.

'=============================================================
' Class Module: RefreshScheduler
' Purpose: Dependency-aware PivotTable refresh orchestrator
'=============================================================
Option Explicit

' ---- Private state ----
Private m_ConfigSheet   As Worksheet
Private m_LogSheet      As Worksheet
Private m_Config        As Collection       ' Collection of RefreshItem objects
Private m_Completed     As Collection       ' RefreshIDs successfully completed
Private m_Failed        As Collection       ' RefreshIDs that failed
Private m_AbortFlag     As Boolean
Private m_RunID         As String           ' Unique ID for this scheduler run

' ---- Initialization ----
Public Sub Initialize(ByVal configSheet As Worksheet, ByVal logSheet As Worksheet)
    Set m_ConfigSheet = configSheet
    Set m_LogSheet    = logSheet
    Set m_Config      = New Collection
    Set m_Completed   = New Collection
    Set m_Failed      = New Collection
    m_AbortFlag       = False
    m_RunID           = Format(Now(), "yyyymmdd_HHmmss")
    
    Call LoadConfiguration
End Sub

' ---- Load configuration from the named table ----
Private Sub LoadConfiguration()
    Dim lo      As ListObject
    Dim lr      As ListRow
    Dim item    As RefreshItem
    
    Set lo = m_ConfigSheet.ListObjects("tbl_RefreshConfig")
    
    If lo.DataBodyRange Is Nothing Then
        Err.Raise vbObjectError + 1001, "RefreshScheduler.LoadConfiguration", _
                  "RefreshConfig table is empty. Add at least one refresh item."
    End If
    
    For Each lr In lo.ListRows
        ' Only load enabled items
        If UCase(Trim(CStr(lr.Range.Cells(1, GetColIndex(lo, "IsEnabled")).Value))) = "TRUE" Then
            Set item = New RefreshItem
            item.RefreshID         = CLng(lr.Range.Cells(1, GetColIndex(lo, "RefreshID")).Value)
            item.RefreshName       = CStr(lr.Range.Cells(1, GetColIndex(lo, "RefreshName")).Value)
            item.SourceType        = CStr(lr.Range.Cells(1, GetColIndex(lo, "SourceType")).Value)
            item.SourceIdentifier  = CStr(lr.Range.Cells(1, GetColIndex(lo, "SourceIdentifier")).Value)
            item.SequenceOrder     = CLng(lr.Range.Cells(1, GetColIndex(lo, "SequenceOrder")).Value)
            item.DependsOn         = CStr(lr.Range.Cells(1, GetColIndex(lo, "DependsOn")).Value)
            item.MaxRetries        = CLng(lr.Range.Cells(1, GetColIndex(lo, "MaxRetries")).Value)
            item.RetryDelaySeconds = CLng(lr.Range.Cells(1, GetColIndex(lo, "RetryDelaySeconds")).Value)
            item.TimeoutSeconds    = CLng(lr.Range.Cells(1, GetColIndex(lo, "TimeoutSeconds")).Value)
            item.OnFailure         = CStr(lr.Range.Cells(1, GetColIndex(lo, "OnFailure")).Value)
            item.ConfigRow         = lr.Range.Row
            
            m_Config.Add item, CStr(item.RefreshID)
        End If
    Next lr
End Sub

' ---- Column index helper (robust to column reordering) ----
Private Function GetColIndex(ByVal lo As ListObject, ByVal colName As String) As Long
    Dim lc As ListColumn
    For Each lc In lo.ListColumns
        If lc.Name = colName Then
            GetColIndex = lc.Index
            Exit Function
        End If
    Next lc
    Err.Raise vbObjectError + 1002, "RefreshScheduler.GetColIndex", _
              "Column '" & colName & "' not found in tbl_RefreshConfig."
End Function

Now let's add the main orchestration logic — the Run method. This is where dependency resolution happens:

' ---- Main execution method ----
Public Sub Run()
    Dim sortedItems()   As RefreshItem
    Dim item            As RefreshItem
    Dim i               As Long
    
    ' Sort items by SequenceOrder
    sortedItems = GetSortedItems()
    
    WriteLog "INFO", "Scheduler run started. RunID: " & m_RunID & _
             ". Items to process: " & UBound(sortedItems) + 1
    
    For i = 0 To UBound(sortedItems)
        Set item = sortedItems(i)
        
        If m_AbortFlag Then
            WriteLog "WARN", "Abort flag raised. Skipping: " & item.RefreshName
            GoTo ContinueLoop
        End If
        
        ' Check if all dependencies are satisfied
        If Not DependenciesMet(item) Then
            WriteLog "WARN", "Dependencies not met for: " & item.RefreshName & _
                     ". Skipping based on upstream failure."
            Call UpdateConfigStatus(item, "SKIPPED - Dependency Failed")
            GoTo ContinueLoop
        End If
        
        ' Execute with retry logic
        Call ExecuteWithRetry(item)
        
ContinueLoop:
    Next i
    
    WriteLog "INFO", "Scheduler run complete. RunID: " & m_RunID
    Call SummarizeRun
End Sub

' ---- Sort items by SequenceOrder (bubble sort is fine for <100 items) ----
Private Function GetSortedItems() As RefreshItem()
    Dim arr()   As RefreshItem
    Dim item    As RefreshItem
    Dim i       As Long, j As Long
    Dim temp    As RefreshItem
    Dim count   As Long
    
    count = m_Config.Count
    ReDim arr(0 To count - 1)
    
    i = 0
    For Each item In m_Config
        Set arr(i) = item
        i = i + 1
    Next item
    
    ' Bubble sort by SequenceOrder
    For i = 0 To count - 2
        For j = 0 To count - 2 - i
            If arr(j).SequenceOrder > arr(j + 1).SequenceOrder Then
                Set temp     = arr(j)
                Set arr(j)   = arr(j + 1)
                Set arr(j + 1) = temp
            End If
        Next j
    Next i
    
    GetSortedItems = arr
End Function

' ---- Check whether all declared dependencies have succeeded ----
Private Function DependenciesMet(ByVal item As RefreshItem) As Boolean
    Dim depList()   As String
    Dim depID       As String
    Dim i           As Long
    
    If Len(Trim(item.DependsOn)) = 0 Then
        DependenciesMet = True
        Exit Function
    End If
    
    depList = Split(item.DependsOn, ",")
    
    For i = 0 To UBound(depList)
        depID = Trim(depList(i))
        If Len(depID) = 0 Then GoTo NextDep
        
        ' Check if this dependency is in the failed collection
        Dim failedID As Variant
        For Each failedID In m_Failed
            If CStr(failedID) = depID Then
                DependenciesMet = False
                Exit Function
            End If
        Next failedID
        
        ' Also verify it's actually in the completed collection
        Dim foundCompleted As Boolean
        foundCompleted = False
        Dim completedID As Variant
        For Each completedID In m_Completed
            If CStr(completedID) = depID Then
                foundCompleted = True
                Exit For
            End If
        Next completedID
        
        If Not foundCompleted Then
            ' Not failed but also not completed -- unusual, treat as not met
            DependenciesMet = False
            Exit Function
        End If
        
NextDep:
    Next i
    
    DependenciesMet = True
End Function

---

The RefreshItem Class Module

Insert another Class Module and name it RefreshItem. This is a simple value object — it holds the properties for one row of the configuration table.

'=============================================================
' Class Module: RefreshItem
' Purpose: Value object representing one refresh configuration row
'=============================================================
Option Explicit

Public RefreshID         As Long
Public RefreshName       As String
Public SourceType        As String
Public SourceIdentifier  As String
Public SequenceOrder     As Long
Public DependsOn         As String
Public MaxRetries        As Long
Public RetryDelaySeconds As Long
Public TimeoutSeconds    As Long
Public OnFailure         As String
Public ConfigRow         As Long
Public AttemptCount      As Long
Public LastError         As String

This separation — where the class is just a data container and the scheduler contains all the logic — follows the Advanced VBA: Class Modules and Object-Oriented Patterns for Excel Automation pattern of keeping data and behavior intentionally decoupled. It makes unit testing straightforward, because you can populate a RefreshItem manually without needing to touch the worksheet at all.

---

The Executor Layer: Refreshing Each Source Type Correctly

The most technically nuanced part of this system is actually performing the refresh. Different source types behave very differently, and the synchronous vs. asynchronous problem is real.

' ---- Execute a single refresh item with retry logic ----
Private Sub ExecuteWithRetry(ByVal item As RefreshItem)
    Dim attempt     As Long
    Dim succeeded   As Boolean
    Dim errMsg      As String
    Dim waitUntil   As Date
    
    succeeded = False
    
    For attempt = 1 To item.MaxRetries + 1
        item.AttemptCount = attempt
        
        WriteLog "INFO", "Attempting refresh: " & item.RefreshName & _
                 " (Attempt " & attempt & " of " & item.MaxRetries + 1 & ")"
        
        On Error GoTo RetryHandler
        
        Select Case item.SourceType
            Case "PivotCache"
                Call RefreshPivotCache(item)
            Case "WorkbookConnection"
                Call RefreshWorkbookConnection(item)
            Case "PowerQuery"
                Call RefreshPowerQuery(item)
            Case "ExternalWorkbook"
                Call RefreshExternalWorkbook(item)
            Case Else
                Err.Raise vbObjectError + 2001, "ExecuteWithRetry", _
                          "Unknown SourceType: " & item.SourceType
        End Select
        
        ' If we reach here, no error was raised -- success
        succeeded = True
        On Error GoTo 0
        
        m_Completed.Add item.RefreshID, CStr(item.RefreshID)
        WriteLog "SUCCESS", item.RefreshName & " completed on attempt " & attempt & _
                 ". Duration tracked in log."
        Call UpdateConfigStatus(item, "SUCCESS")
        Exit For
        
RetryHandler:
        errMsg = Err.Description
        item.LastError = errMsg
        
        WriteLog "ERROR", "Attempt " & attempt & " failed for " & item.RefreshName & _
                 ": " & errMsg
        
        On Error GoTo 0
        
        If attempt <= item.MaxRetries Then
            ' Exponential backoff: RetryDelaySeconds * 2^(attempt-1)
            Dim delaySeconds As Long
            delaySeconds = item.RetryDelaySeconds * (2 ^ (attempt - 1))
            
            WriteLog "INFO", "Waiting " & delaySeconds & "s before retry..."
            waitUntil = Now() + TimeSerial(0, 0, delaySeconds)
            Do While Now() < waitUntil
                DoEvents   ' Keep Excel responsive during wait
            Loop
        End If
    Next attempt
    
    If Not succeeded Then
        m_Failed.Add item.RefreshID, CStr(item.RefreshID)
        Call UpdateConfigStatus(item, "FAILED: " & Left(item.LastError, 100))
        
        Select Case UCase(item.OnFailure)
            Case "ABORT"
                m_AbortFlag = True
                WriteLog "CRITICAL", "OnFailure=Abort triggered by: " & item.RefreshName & _
                         ". Halting scheduler."
            Case "RETRYTHENABORT"
                m_AbortFlag = True
                WriteLog "CRITICAL", "All retries exhausted for: " & item.RefreshName & _
                         ". Abort flag raised."
            Case "SKIP"
                WriteLog "WARN", "OnFailure=Skip for: " & item.RefreshName & _
                         ". Downstream items will be evaluated normally unless they depend on this."
        End Select
    End If
End Sub

Now let's implement each executor. The PivotCache refresh is the most direct:

' ---- Refresh a specific PivotTable cache by index ----
Private Sub RefreshPivotCache(ByVal item As RefreshItem)
    Dim cacheIndex  As Long
    Dim pc          As PivotCache
    
    cacheIndex = CLng(item.SourceIdentifier)
    
    If cacheIndex < 1 Or cacheIndex > ThisWorkbook.PivotCaches.Count Then
        Err.Raise vbObjectError + 3001, "RefreshPivotCache", _
                  "PivotCache index " & cacheIndex & " is out of range. " & _
                  "Workbook has " & ThisWorkbook.PivotCaches.Count & " caches."
    End If
    
    Set pc = ThisWorkbook.PivotCaches(cacheIndex)
    
    ' Force synchronous refresh
    pc.MissingItemsLimit = xlMissingItemsNone
    pc.RefreshOnFileOpen = False
    pc.Refresh   ' Synchronous for standard PivotCaches
    
    ' Wait for background query if applicable
    If pc.IsConnected Then
        Call WaitForCacheRefresh(pc, item.TimeoutSeconds)
    End If
End Sub

' ---- Synchronous wait for a cache that runs a background query ----
Private Sub WaitForCacheRefresh(ByVal pc As PivotCache, ByVal timeoutSec As Long)
    Dim deadline    As Date
    deadline = Now() + TimeSerial(0, 0, timeoutSec)
    
    Do While pc.Refreshing
        DoEvents
        If Now() > deadline Then
            Err.Raise vbObjectError + 3002, "WaitForCacheRefresh", _
                      "PivotCache refresh timed out after " & timeoutSec & " seconds."
        End If
        Application.Wait Now() + TimeSerial(0, 0, 1)
    Loop
End Sub

The WorkbookConnection refresh is where synchronous control gets critical — especially for OLAP connections:

' ---- Refresh a named WorkbookConnection ----
Private Sub RefreshWorkbookConnection(ByVal item As RefreshItem)
    Dim conn    As WorkbookConnection
    Dim found   As Boolean
    found = False
    
    Dim c As WorkbookConnection
    For Each c In ThisWorkbook.Connections
        If c.Name = item.SourceIdentifier Then
            Set conn = c
            found = True
            Exit For
        End If
    Next c
    
    If Not found Then
        Err.Raise vbObjectError + 3003, "RefreshWorkbookConnection", _
                  "Connection '" & item.SourceIdentifier & "' not found in workbook."
    End If
    
    ' Disable background refresh to force synchronous operation
    ' This is the critical step most implementations miss
    Select Case conn.Type
        Case xlConnectionTypeOLEDB
            conn.OLEDBConnection.BackgroundQuery = False
            conn.Refresh
            Call WaitForConnectionRefresh(conn, item.TimeoutSeconds)
            
        Case xlConnectionTypeODBC
            conn.ODBCConnection.BackgroundQuery = False
            conn.Refresh
            Call WaitForConnectionRefresh(conn, item.TimeoutSeconds)
            
        Case xlConnectionTypeTEXT
            conn.Refresh
            
        Case xlConnectionTypeWEBSERVICE
            conn.Refresh
            Call WaitForConnectionRefresh(conn, item.TimeoutSeconds)
            
        Case Else
            ' DataModel and other types
            conn.Refresh
            Call WaitForConnectionRefresh(conn, item.TimeoutSeconds)
    End Select
End Sub

Private Sub WaitForConnectionRefresh(ByVal conn As WorkbookConnection, ByVal timeoutSec As Long)
    Dim deadline    As Date
    deadline = Now() + TimeSerial(0, 0, timeoutSec)
    
    Do While conn.Refreshing
        DoEvents
        If Now() > deadline Then
            Err.Raise vbObjectError + 3004, "WaitForConnectionRefresh", _
                      "Connection '" & conn.Name & "' timed out after " & timeoutSec & " seconds."
        End If
        Application.Wait Now() + TimeSerial(0, 0, 1)
    Loop
End Sub

Power Query connections deserve special handling because they run through the Queries collection:

' ---- Refresh a Power Query query by name ----
Private Sub RefreshPowerQuery(ByVal item As RefreshItem)
    Dim pq          As WorkbookQuery
    Dim conn        As WorkbookConnection
    Dim found       As Boolean
    found = False
    
    ' Power Query queries have a matching WorkbookConnection
    ' named "Query - <QueryName>"
    Dim targetConnName As String
    targetConnName = "Query - " & item.SourceIdentifier
    
    Dim c As WorkbookConnection
    For Each c In ThisWorkbook.Connections
        If c.Name = targetConnName Then
            Set conn = c
            found = True
            Exit For
        End If
    Next c
    
    If Not found Then
        ' Also try direct name match (some versions don't use "Query - " prefix)
        For Each c In ThisWorkbook.Connections
            If c.Name = item.SourceIdentifier Then
                Set conn = c
                found = True
                Exit For
            End If
        Next c
    End If
    
    If Not found Then
        Err.Raise vbObjectError + 3005, "RefreshPowerQuery", _
                  "Power Query connection for '" & item.SourceIdentifier & "' not found. " & _
                  "Expected connection name: '" & targetConnName & "'"
    End If
    
    ' Force synchronous: this is the most important line for Power Query
    If conn.Type = xlConnectionTypeOLEDB Then
        conn.OLEDBConnection.BackgroundQuery = False
    End If
    
    conn.Refresh
    Call WaitForConnectionRefresh(conn, item.TimeoutSeconds)
End Sub

The external workbook case is the most complex — it requires opening the file, triggering its refreshes, saving it, and closing it:

' ---- Refresh an external workbook and wait for completion ----
Private Sub RefreshExternalWorkbook(ByVal item As RefreshItem)
    Dim filePath    As String
    Dim wb          As Workbook
    Dim alreadyOpen As Boolean
    alreadyOpen = False
    
    filePath = item.SourceIdentifier
    
    ' Validate file exists before attempting open
    If Dir(filePath) = "" Then
        Err.Raise vbObjectError + 3006, "RefreshExternalWorkbook", _
                  "External file not found: " & filePath
    End If
    
    ' Check if already open (avoid double-open errors)
    Dim w As Workbook
    For Each w In Application.Workbooks
        If LCase(w.FullName) = LCase(filePath) Then
            Set wb = w
            alreadyOpen = True
            Exit For
        End If
    Next w
    
    If Not alreadyOpen Then
        ' Open without running auto-open macros to avoid interference
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Set wb = Workbooks.Open(Filename:=filePath, UpdateLinks:=False, ReadOnly:=False)
        Application.AutomationSecurity = msoAutomationSecurityByUI
    End If
    
    ' Disable background refresh on all connections in the external workbook
    Dim extConn As WorkbookConnection
    For Each extConn In wb.Connections
        On Error Resume Next
        extConn.OLEDBConnection.BackgroundQuery = False
        extConn.ODBCConnection.BackgroundQuery = False
        On Error GoTo 0
    Next extConn
    
    ' Refresh all connections in sequence
    wb.RefreshAll
    
    ' Wait for all connections in external workbook to finish
    Dim deadline    As Date
    Dim allDone     As Boolean
    deadline = Now() + TimeSerial(0, 0, item.TimeoutSeconds)
    
    Do
        allDone = True
        For Each extConn In wb.Connections
            If extConn.Refreshing Then
                allDone = False
                Exit For
            End If
        Next extConn
        
        If Not allDone Then
            DoEvents
            Application.Wait Now() + TimeSerial(0, 0, 1)
        End If
        
        If Now() > deadline And Not allDone Then
            Err.Raise vbObjectError + 3007, "RefreshExternalWorkbook", _
                      "External workbook refresh timed out: " & filePath
        End If
    Loop Until allDone
    
    ' Save and close only if we opened it ourselves
    If Not alreadyOpen Then
        wb.Save
        wb.Close SaveChanges:=False
    End If
End Sub

---

The Audit Log: Building a Forensic Trail

Every enterprise system needs a paper trail. Add this to the RefreshScheduler class. The log writes to a dedicated RefreshLog worksheet and accumulates across runs — it doesn't overwrite on each execution.

' ---- Write a structured log entry ----
Private Sub WriteLog(ByVal logLevel As String, ByVal message As String)
    Dim nextRow As Long
    
    With m_LogSheet
        ' Find next empty row
        nextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        If nextRow < 2 Then nextRow = 2  ' Preserve header row
        
        .Cells(nextRow, 1).Value = m_RunID
        .Cells(nextRow, 2).Value = Now()
        .Cells(nextRow, 3).Value = logLevel
        .Cells(nextRow, 4).Value = message
        
        ' Color-code by severity for quick visual scanning
        Select Case logLevel
            Case "SUCCESS"
                .Cells(nextRow, 3).Interior.Color = RGB(198, 239, 206)   ' Light green
                .Cells(nextRow, 3).Font.Color = RGB(0, 97, 0)
            Case "ERROR", "CRITICAL"
                .Cells(nextRow, 3).Interior.Color = RGB(255, 199, 206)   ' Light red
                .Cells(nextRow, 3).Font.Color = RGB(156, 0, 6)
            Case "WARN"
                .Cells(nextRow, 3).Interior.Color = RGB(255, 235, 156)   ' Light yellow
                .Cells(nextRow, 3).Font.Color = RGB(156, 101, 0)
        End Select
    End With
    
    ' Force Excel to repaint so the log updates visually during run
    DoEvents
End Sub

' ---- Write results back to the config table ----
Private Sub UpdateConfigStatus(ByVal item As RefreshItem, ByVal status As String)
    With m_ConfigSheet
        ' Status column
        .Cells(item.ConfigRow, GetColIndex( _
            m_ConfigSheet.ListObjects("tbl_RefreshConfig"), "LastStatus")).Value = status
        ' Timestamp column
        .Cells(item.ConfigRow, GetColIndex( _
            m_ConfigSheet.ListObjects("tbl_RefreshConfig"), "LastRunTime")).Value = Now()
    End With
End Sub

' ---- Write a final summary entry ----
Private Sub SummarizeRun()
    Dim successCount As Long
    Dim failCount    As Long
    
    successCount = m_Completed.Count
    failCount    = m_Failed.Count
    
    WriteLog "INFO", "=== RUN SUMMARY | RunID: " & m_RunID & " | " & _
             "Succeeded: " & successCount & " | Failed: " & failCount & _
             " | Aborted: " & m_AbortFlag & " ==="
End Sub

---

The Entry Point: Wiring It All Together

Now we need a standard module with the public-facing RunRefreshScheduler procedure that everything else calls. This is what you'd assign to a button, schedule with Application.OnTime, or trigger from a task scheduler. For more on scheduling approaches, see Scheduling and Running VBA Macros Automatically with Windows Task Scheduler and Application.OnTime.

'=============================================================
' Module: modRefreshScheduler
' Purpose: Entry points for the PivotTable Refresh Scheduler
'=============================================================
Option Explicit

Private m_Scheduler     As RefreshScheduler
Private m_NextRunTime   As Date
Private Const SCHEDULE_INTERVAL_MINUTES As Long = 60

' ---- Primary entry point ----
Public Sub RunRefreshScheduler()
    Dim configSheet     As Worksheet
    Dim logSheet        As Worksheet
    
    ' Validate required sheets exist
    On Error GoTo SheetError
    Set configSheet = ThisWorkbook.Worksheets("RefreshConfig")
    Set logSheet    = ThisWorkbook.Worksheets("RefreshLog")
    On Error GoTo 0
    
    ' Suppress screen updates and alerts for performance
    Application.ScreenUpdating = False
    Application.DisplayAlerts  = False
    Application.Calculation    = xlCalculationManual
    
    Set m_Scheduler = New RefreshScheduler
    
    On Error GoTo SchedulerError
    m_Scheduler.Initialize configSheet, logSheet
    m_Scheduler.Run
    On Error GoTo 0
    
CleanUp:
    Application.ScreenUpdating = True
    Application.DisplayAlerts  = True
    Application.Calculation    = xlCalculationAutomatic
    Application.Calculate       ' Force full recalculation after all refreshes
    Exit Sub

SheetError:
    MsgBox "Required worksheet missing. Ensure 'RefreshConfig' and 'RefreshLog' " & _
           "sheets exist in this workbook.", vbCritical, "Scheduler Setup Error"
    Resume CleanUp

SchedulerError:
    MsgBox "Scheduler encountered a fatal error:" & vbNewLine & Err.Description, _
           vbCritical, "Refresh Scheduler Error"
    Resume CleanUp
End Sub

' ---- Schedule recurring runs using Application.OnTime ----
Public Sub StartScheduledRefresh()
    m_NextRunTime = Now() + TimeSerial(0, SCHEDULE_INTERVAL_MINUTES, 0)
    Application.OnTime EarliestTime:=m_NextRunTime, _
                       Procedure:="ScheduledRefreshCallback", _
                       Schedule:=True
    MsgBox "Refresh scheduler started. Next run: " & Format(m_NextRunTime, "HH:MM:SS"), _
           vbInformation, "Scheduler Active"
End Sub

' ---- Called by OnTime -- runs scheduler then reschedules itself ----
Public Sub ScheduledRefreshCallback()
    Call RunRefreshScheduler
    Call StartScheduledRefresh   ' Reschedule next run
End Sub

' ---- Cancel scheduled refresh ----
Public Sub StopScheduledRefresh()
    On Error Resume Next
    Application.OnTime EarliestTime:=m_NextRunTime, _
                       Procedure:="ScheduledRefreshCallback", _
                       Schedule:=False
    On Error GoTo 0
    MsgBox "Scheduled refresh cancelled.", vbInformation, "Scheduler Stopped"
End Sub

---

Adding a Status UserForm for Operator Visibility

A scheduler that runs silently in the background is functional but not user-friendly. Let's add a lightweight status form. If you've worked through Building UserForms for Custom Data Entry Interfaces, this will feel familiar.

Insert a new UserForm named frmSchedulerStatus. Add these controls:

• A ListBox named lstLog (Width: 480, Height: 240) — displays real-time log entries
• A Label named lblCurrentItem — shows the current refresh item name
• A ProgressBar (use a simple Frame with a resizable inner Frame as a workaround since VBA's native ProgressBar requires MSCOMCTL.OCX) or a plain Label named lblProgress
• A CommandButton named btnClose with caption "Close"

The key is wiring the form to the scheduler through a callback pattern. Add a public method to RefreshScheduler that lets external code register a callback:

' Add to RefreshScheduler class:

Private m_StatusCallback    As Object   ' Reference to the status form

Public Sub SetStatusCallback(ByVal callbackObj As Object)
    Set m_StatusCallback = callbackObj
End Sub

' Modify WriteLog to also update the form if connected:
Private Sub WriteLog(ByVal logLevel As String, ByVal message As String)
    ' ... existing log code ...
    
    ' Update form if connected
    If Not m_StatusCallback Is Nothing Then
        On Error Resume Next
        m_StatusCallback.UpdateStatus logLevel, message
        On Error GoTo 0
    End If
End Sub

Then in the UserForm code:

' UserForm: frmSchedulerStatus
Option Explicit

Public Sub UpdateStatus(ByVal logLevel As String, ByVal message As String)
    Dim displayText As String
    displayText = Format(Now(), "HH:MM:SS") & " [" & logLevel & "] " & message
    
    lstLog.AddItem displayText
    lstLog.ListIndex = lstLog.ListCount - 1   ' Auto-scroll to bottom
    lblCurrentItem.Caption = message
    
    DoEvents   ' Force repaint
End Sub

Private Sub btnClose_Click()
    Unload Me
End Sub

---

Handling the Data Model Refresh Order Problem

One subtlety that trips up even experienced developers: when your workbook uses the Excel Data Model (Power Pivot), refreshing individual connections isn't enough. The Data Model maintains its own internal dependency graph, and simply calling Refresh on connections in sequence doesn't guarantee the Data Model tables recalculate their relationships in the right order.

The correct approach for Data Model workbooks is a two-phase refresh:

Phase 1: Refresh all data source connections (SQL, OData, text files, Power Query) in dependency order. These populate the raw tables in the Data Model.

Phase 2: After all source connections are confirmed complete, refresh the Data Model itself to recompute calculated columns, measures, and relationships.

' ---- Refresh the Excel Data Model explicitly ----
Public Sub RefreshDataModel()
    Dim modelConn   As WorkbookConnection
    Dim found       As Boolean
    found = False
    
    ' The Data Model connection has a specific naming convention
    Dim c As WorkbookConnection
    For Each c In ThisWorkbook.Connections
        If c.Type = xlConnectionTypeOLEDB Then
            If InStr(1, c.OLEDBConnection.Connection, "Data Model", vbTextCompare) > 0 _
               Or c.Name = "ThisWorkbookDataModel" Then
                Set modelConn = c
                found = True
                Exit For
            End If
        End If
    Next c
    
    If found Then
        modelConn.OLEDBConnection.BackgroundQuery = False
        modelConn.Refresh
        
        ' Wait for model to fully process
        Dim deadline As Date
        deadline = Now() + TimeSerial(0, 5, 0)   ' 5-minute timeout for large models
        
        Do While modelConn.Refreshing
            DoEvents
            Application.Wait Now() + TimeSerial(0, 0, 2)
            If Now() > deadline Then
                Err.Raise vbObjectError + 4001, "RefreshDataModel", _
                          "Data Model refresh timed out after 5 minutes."
            End If
        Loop
    End If
End Sub

Add a final item in your tbl_RefreshConfig table with SourceType = "DataModel" and sequence order higher than all other items. Then add a Case "DataModel" branch in the ExecuteWithRetry Select Case to call RefreshDataModel. This ensures the Data Model recalculates only after all its source tables are populated. For a deep dive into what's happening inside the Data Model during this process, Master Power Pivot and Excel Data Model for Million-Row Analysis explains the DAX calculation engine's dependency resolution.

---

Performance Optimization and Scaling Considerations

Once your scheduler is running in a real environment, you'll start caring about how long it takes. A few targeted optimizations can dramatically reduce total refresh time without sacrificing correctness.

Parallelism for independent items: Items with no dependency relationship on each other can theoretically run in parallel. Pure VBA can't do true threading, but you can achieve pseudo-parallelism by firing multiple background connections simultaneously (setting BackgroundQuery = True for items that share no dependencies), then polling all of them in a single wait loop until all complete before proceeding to the next dependency tier.

Connection pre-warming: For SQL Server connections, the first connection attempt often includes a TCP handshake and authentication round-trip. If your scheduler runs on a fixed schedule, you can pre-warm connections by calling a lightweight test query a few seconds before the main refresh sequence starts. This shaves several seconds off large connection chains.

Cache invalidation strategy: Not every PivotTable needs to refresh on every run. Add a RefreshIntervalMinutes column to your config table and have the scheduler check LastRunTime. If the last successful run was less than RefreshIntervalMinutes ago, skip that item. This matters enormously for data sources that update hourly when your scheduler runs every 15 minutes.

' ---- Check if item needs refresh based on interval ----
Private Function NeedsRefresh(ByVal item As RefreshItem, ByVal intervalMinutes As Long) As Boolean
    Dim lo          As ListObject
    Dim lastRunCell As Range
    Dim lastRunVal  As Variant
    
    Set lo = m_ConfigSheet.ListObjects("tbl_RefreshConfig")
    Set lastRunCell = m_ConfigSheet.Cells(item.ConfigRow, _
                        GetColIndex(lo, "LastRunTime"))
    lastRunVal = lastRunCell.Value
    
    If IsEmpty(lastRunVal) Or Not IsDate(lastRunVal) Then
        NeedsRefresh = True   ' Never run, always refresh
        Exit Function
    End If
    
    Dim minutesSinceLast As Double
    minutesSinceLast = (Now() - CDate(lastRunVal)) * 24 * 60
    
    NeedsRefresh = (minutesSinceLast >= intervalMinutes)
End Function

Memory and Excel performance: Refreshing many large connections sequentially can push Excel's memory usage significantly. For workbooks with 5+ large connections, add Application.Wait Now() + TimeSerial(0, 0, 2) between refresh operations to let Excel's garbage collector release memory from the previous operation. It sounds trivial but prevents memory-pressure crashes on 32-bit Excel installations. For comprehensive strategies around Excel performance, Excel Performance Optimization: Fix Slow Workbooks and Scale Your Analysis covers the broader picture.

---

Hands-On Exercise

Build a working refresh scheduler from this lesson using the following scenario:

Setup: Create a workbook with two worksheets that act as simple data sources (simulate a "source" by putting data in a range that feeds a Power Query transformation). Create a third worksheet as a summary dashboard with PivotTables.

Step 1: Create the RefreshConfig and RefreshLog worksheets. Set up tbl_RefreshConfig with the columns defined earlier. Add at least three rows — one for each data source connection your workbook has, plus one for a PivotCache.

Step 2: Insert the RefreshItem and RefreshScheduler class modules. Copy the code from this lesson, then read through every procedure carefully and annotate what each section does in your own words as comments.

Step 3: Create the modRefreshScheduler standard module with the entry points.

Step 4: Test with one item first — set IsEnabled = FALSE on all rows except one, run RunRefreshScheduler, and verify the log entry appears. Add a deliberate error (a bad connection name) and verify the error handling writes to the log correctly.

Step 5: Enable all rows and add a real dependency. Set item 3's DependsOn to 1,2. Run the scheduler and confirm in the log that item 3 only executes after items 1 and 2 show SUCCESS status.

Step 6: Test failure handling. Set item 1's SourceIdentifier to a connection name that doesn't exist, set OnFailure = Abort, and run. Verify that the scheduler halts after item 1's failure and that dependent items show "SKIPPED - Dependency Failed" status.

Challenge: Add a RefreshIntervalMinutes column to your config table and implement the NeedsRefresh check in the Run method. Run the scheduler twice in quick succession and confirm that the second run skips items whose interval hasn't elapsed.

---

Common Mistakes & Troubleshooting

Problem: Refresh completes instantly but PivotTable still shows old data. Cause: Background query is still enabled. connection.Refresh returned immediately while the query runs asynchronously. Fix: Always set BackgroundQuery = False before calling Refresh on OLEDB and ODBC connections. Add the WaitForConnectionRefresh call afterward.

Problem: m_Config.Add fails with "Key already exists" error. Cause: Duplicate RefreshID values in your config table. Fix: Add data validation to enforce uniqueness on the RefreshID column, or catch the collision in LoadConfiguration and raise a descriptive error that names the duplicate ID.

Problem: External workbook refresh fails with "file locked" error on a network share. Cause: Another user has the file open in Edit mode. Fix: Open with ReadOnly:=True, refresh all connections, but recognize you can't save changes. For read-only external workbooks, the refresh still pulls fresh data into your Data Model — you just can't persist it in the source file. Set OnFailure = Skip for external workbooks on shared network paths where locking is likely.

Problem: Scheduler runs correctly manually but fails when triggered by Application.OnTime. Cause: Security context differences — Application.OnTime runs with the security level of the current session. If a macro security prompt appears (file opened fresh), OnTime callbacks may be blocked. Fix: Ensure the workbook is in a Trusted Location. Also add Application.AutomationSecurity = msoAutomationSecurityByUI before the OnTime call, not inside the callback.

Problem: Data Model tables show data but DAX measures return stale values. Cause: You refreshed the source connections but didn't trigger a Data Model recalculation. Fix: Add a DataModel row at the end of your sequence that calls RefreshDataModel(). Always make it the last item with the highest SequenceOrder.

Problem: DoEvents during wait loops causes unexpected macro execution. Cause: DoEvents yields to the Windows message queue, which means button clicks, worksheet events, or other macros can run during your wait loop. Fix: Add a boolean flag (m_IsRunning) set to True at the start of Run() and False at the end. Check this flag at the start of any event-driven macro that could interfere, and bail out if m_IsRunning = True. See Building a Custom VBA Event-Driven Framework: Respond to Workbook, Worksheet, and Application Events for Real-Time Automation for event suppression patterns.

Problem: Scheduler log grows to thousands of rows over weeks and slows the workbook. Fix: Add a maintenance procedure that trims log entries older than N days. Run it at the start of each scheduler execution:

Public Sub TrimLog(ByVal keepDays As Long)
    Dim logSheet    As Worksheet
    Dim lastRow     As Long
    Dim i           As Long
    Dim cutoff      As Date
    
    Set logSheet = ThisWorkbook.Worksheets("RefreshLog")
    cutoff = Now() - keepDays
    lastRow = logSheet.Cells(logSheet.Rows.Count, 2).End(xlUp).Row
    
    Application.ScreenUpdating = False
    For i = lastRow To 2 Step -1
        If IsDate(logSheet.Cells(i, 2).Value) Then
            If CDate(logSheet.Cells(i, 2).Value) < cutoff Then
                logSheet.Rows(i).Delete
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

---

Summary & Next Steps

You've built a production-grade PivotTable refresh scheduler that handles the full complexity of enterprise workbooks: multi-source dependency resolution, source-type-aware synchronous refresh control, exponential backoff retry logic, structured audit logging, and graceful failure management with configurable recovery policies.

The architecture we used — a configuration table driving a class-based scheduler — is intentionally extensible. Adding a new data source type means adding a Case in the executor's Select Case block and a new row in the config table. No other code changes required. When your DBA adds a new OLAP cube or your Finance team starts publishing a new flat-file feed, your scheduler absorbs it without surgery.

Where to go from here:

The scheduler built in this lesson runs inside a single workbook. For enterprise environments where data flows across dozens of files, the natural evolution is a multi-workbook consolidation architecture — something explored in Building a Multi-Workbook VBA Consolidation Engine: Merge, Transform, and Audit Data from Hundreds of Files Automatically.

To expose your scheduler to non-technical users through a polished interface, pair it with a custom Ribbon button (covered in Building a Custom Excel Ribbon with VBA and XML: Design, Deploy, and Control Context-Sensitive UI for Professional-Grade Workbooks) so operators can trigger, monitor, and cancel refresh runs without touching the VBA editor.

Finally, once your scheduler is in production use, you'll want automated tests to catch regressions — especially after workbook structure changes or connection renames. Building a test harness that validates scheduler behavior against known configurations is the professional path to sustainable automation, as covered in Building a VBA Testing Framework for Excel: Unit Test Your Macros, Validate Outputs, and Catch Regressions Before Deployment.

The gap between "macro that refreshes stuff" and "scheduler that reliably orchestrates a data pipeline" is exactly what this lesson was designed to close. You now have the architecture, the code, and the mental model to build the latter.