Option Explicit
Private WithEvents pQueryTable As QueryTable
Private pStatusBaseCell As Range
Private pRefreshStartTime As Date
Private pRefreshEndTime As Date
Public Property Get QueryTable() As QueryTable
Set QueryTable = pQueryTable
End Property
Public Property Set QueryTable(ByVal qt As QueryTable)
Set pQueryTable = qt
End Property
Public Property Get StatusBaseCell() As Range
StatusBaseCell = pStatusBaseCell
End Property
Public Property Set StatusBaseCell(baseCell As Range)
Set pStatusBaseCell = baseCell
End Property
Private Sub pQueryTable_BeforeRefresh(Cancel As Boolean)
pRefreshStartTime = Now
With pQueryTable
pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, "", "Refresh Running")
End With
End Sub
Private Sub pQueryTable_AfterRefresh(ByVal Success As Boolean)
pRefreshEndTime = Now
With pQueryTable
If Success Then
pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, pRefreshEndTime, "Succeeded")
Else
pStatusBaseCell.Resize(, 5).Value = Array(.ListObject.Name, "'" & .Destination.Worksheet.Name & "'!" & .Destination.Address, pRefreshStartTime, pRefreshEndTime, "Failed")
End If
End With
End Sub