VBA Script to log PowerQuery Refresh

Question

I'm trying to run a Worksheet_Change script that creates an audit log of when the last time a Power Query table has been refreshed. This table is a System of Record so its vital for users to know the last time it was refreshed. The issue seems to be that when you refresh a PQ table, Excel does not trigger Worksheet_Change because the data is replaced 'silently'. It doesn't count as a user-initiated cell change. Does anyone have a work around?

On a separate but related note, bonus points for being able to refresh a power query table using a module.

Here is my code which doesn't actually do anything:

    Dim querySheet As Worksheet
    Set querySheet = ThisWorkbook.Sheets("AReS Contract View Master") ' Where the PQ table lives

    If Sh.Name <> querySheet.Name Then Exit Sub

    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = querySheet.ListObjects("AReS_Masters_Contract")
    On Error GoTo 0

    If tbl Is Nothing Then Exit Sub

    If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then
        Dim logSheet As Worksheet
        Set logSheet = ThisWorkbook.Sheets("AReS Contract View >")

        Dim nextRow As Long
        nextRow = logSheet.Cells(logSheet.Rows.Count, "B").End(xlUp).Row
        If nextRow < 5 Then nextRow = 5
        If logSheet.Range("B" & nextRow).Value <> "" Then nextRow = nextRow + 1

        logSheet.Range("B" & nextRow).Value = "Refreshed: " & Format(Now, "yyyy-mm-dd hh:nn:ss") & _
                                              " by " & Environ("Username")
    End If
End Sub

you could probably just change the powerquery to be self-referential and keep cumulative time stamp of its last refreshes, based on exceleratorbi.com.au/self-referencing-tables-power-query — horseyride
– horseyride, Commented May 1 at 12:04
I couldn't figure out how to create a cumulative audit log with the link you provided. I could create a 'one and done' table but struggled with getting multiple records with this approach. — BDASkipper
– BDASkipper, Commented May 2 at 12:28

Dharman · Accepted Answer · 2025-05-26 11:13:01Z

I think the easiest way to achieve what you are looking for is to add directly the date & time to your tables in the last line for example. To do so your can create a new table Time Table in power query having only one column and 1 row equal to the time.

To do so you can use PQ function : DateTime.LocalNow() for example.

Once you have this new table you can append all your other tables with Time Table when refreshing your query you will get the last refresh date & time in the end of your table.

Bonus : To refresh a power query table using a module you can create a new Sub inside a module with the following code :

WB.Connections("Query - NAME OF YOUR PQ TABLE").Refresh

This "Query - " syndax is mandatory and is for the english version of Excel, it will depend on your language for exempla in french it would be : "Requête - NAME OF YOUR PQ TABLE".

You can also refresh everything in the WB using :

WB.RefreshAll

Where WB is your workbook (ActiveWorkbook etc.).

Collectives™ on Stack Overflow

VBA Script to log PowerQuery Refresh

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related