VBA to run when a table with a SQL query is refreshed?

[Tarver]

I have a table, tbl_SalesData on the tab Sales Data that is populated by a SQL query. There are only three fields in this table, "Item No," "First Day of Posting Month," and "Shipped Qty."

The SQL query populates text dates in the format YYYY-MM-DD. I need to convert those to numerical dates so that Excel can work with them and do some SUMIFS on the data. I have the following code that I think SHOULD work when the table is refreshed, but I can't get it to execute when the table is refreshed.

Private Sub Workbook_SheetQueryTableAfterRefresh(ByVal Sh As Object, ByVal Success As Boolean)
    On Error GoTo ErrorHandler
   
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim cell As Range
    Dim dateString As String
    Dim realDate As Date
   
    ' Set the worksheet and table
    Set ws = ThisWorkbook.Sheets("Sales Data")
    Set tbl = ws.ListObjects("tbl_SalesData")
   
    ' Check if the refresh was successful and the sheet is the one we are interested in
    If Success And Sh.Name = ws.Name Then
        ' Loop through each cell in the "First Day of Posting Month" column
        For Each cell In tbl.ListColumns("First Day of Posting Month").DataBodyRange
            dateString = cell.Value
            If IsDate(dateString) And Len(dateString) = 10 And Mid(dateString, 5, 1) = "-" And Mid(dateString, 8, 1) = "-" Then
                ' Convert the text string date to a real date
                realDate = DateSerial(CInt(Left(dateString, 4)), CInt(Mid(dateString, 6, 2)), CInt(Right(dateString, 2)))
                ' Format the date as MM/DD/YYYY
                cell.Value = Format(realDate, "MM/DD/YYYY")
            End If
        Next cell
    End If
   
    Exit Sub

ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

Any thoughts on why it's not executing when the table is refreshed? I've tried this code as both ThisWorkbook and code for Sheet 9 (Sales Data.) Your help would be most appreciated!

 

[DickyWest32]

Hi Tarver,

Your question has, I think, pointed me in the right direction for a different problem I'm working on, but for your issue I'd have thought it'd be more efficient to use PowerQuery to carry out the date conversion while it is retrieving the table data. If you were able to use PowerQuery, this would remove the need to run a VBA script at all.

I want to use the TableAfterRefresh event, so will give it a go and see if it has any quirks.

Cheers
Rich

 

[RoryA]

The posted code won't work because there is no such event. You have to use a class to trap a querytable's afterrefresh event.

 

[DickyWest32]

It's covered here:-

Can't get my afterrefresh or beforerefresh to work correctly.

I have the class module: Option Explicit Public WithEvents MyQuery As QueryTable Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean) If Success Then MsgBox "Query has been refreshed." End Sub Private Sub MyQuery_BeforeRefresh(Cancel As Boolean) If MsgBox("Refresh query?"...

www.mrexcel.com

It was a bit fiddly but I got it to work, now on to the next part of my challenge.

Cheers
R