VBA that runs Sub after Data Refresh All completes

[KatiL]

I am a VBA hack and not formally trained.. so apologies if I am confusing or unclear.

I need to create VBA code that notices when Data Refresh All completes. Once complete, it would run a VBA sub from Module1 that I have already created and tested. When complete, I would like a message box to indicate that "Data Refresh All successfully completed. All Import Tabs have been updated to include only refreshed data."

From reading through the responses in other threads I think I need to add some code to a Class Module and to ThisWorkbook. I have included a snip of what I think MIGHT be the correct Class Module code.. but I couldn't wrap my head around what would go in ThisWorkbook... and I am not sure if the Class Module code is correct since I cannot test it until the rest of the code is written.

In case you need to know, the Power Queries that would run are:

TSDATA​

eCMS_TCI_DATA​

BILLING_DATA​

CLEARION_DATA​

TS_W_ERRORS​

The VBA Sub I need to run is called

ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS​

 

[John_w]

There is a nice solution here:

Run VBA after ALL tables have refreshed

I want to have a block of VBA code run after all the tables in the workbook have refreshed: User clicks the Refresh All button which causes All tables in the workbook to be refreshed four tables, each on a separate sheet tables are refreshed via Power Query Once all tables have completed the...

www.mrexcel.com

The code in ThisWorkbook is needed to create the Dictionary which holds all the query tables amongst all the worksheets and tables in the workbook.

In the class module, clsQueryTable, the AfterRefresh event handler calls myMacro which checks whether all the refreshing queries have finished and if so calls your code. I would probably move the check to the event handler itself.

 

[KatiL]

There is a nice solution here:

Run VBA after ALL tables have refreshed

I want to have a block of VBA code run after all the tables in the workbook have refreshed: User clicks the Refresh All button which causes All tables in the workbook to be refreshed four tables, each on a separate sheet tables are refreshed via Power Query Once all tables have completed the...

www.mrexcel.com

The code in ThisWorkbook is needed to create the Dictionary which holds all the query tables amongst all the worksheets and tables in the workbook.

In the class module, clsQueryTable, the AfterRefresh event handler calls myMacro which checks whether all the refreshing queries have finished and if so calls your code. I would probably move the check to the event handler itself.

That is the thread I was studying but it seems to be over my head. I am not sure how to customize it for my specific situation... I mean, I created the clsQueryTable (accidentally called it clQueryTable and changed all other references to match...) and added the other code as directed but it is not working. I am not sure what most of that code is doing so am not sure where to begin. Attached are snips of the class module, module2 and ThisWorkbook. Appreciate any insights!

 

[John_w]

I mean, I created the clsQueryTable (accidentally called it clQueryTable and changed all other references to match...) and added the other code as directed but it is not working.

Your Class1 is wrong and should be deleted.

Your screenshots don't show the code for clQueryTable, so replace all the code in it with:

Option Explicit

Private WithEvents m_queryTable As QueryTable

Public Property Set QueryTable(ByVal qt As QueryTable)
    Set m_queryTable = qt
End Property

Private Sub m_queryTable_AfterRefresh(ByVal Success As Boolean)

    If Success Then
    
        Debug.Print "AfterRefresh: " & m_queryTable.ListObject.Name
        
        Dim tableName As String
        tableName = m_queryTable.ListObject.Name
        
        dicQueryTables(tableName)("refreshed") = 1
        
        If dicQueryTables.Count = getRefreshCount() Then
            'All queries have refreshed, therefore call your macro and reset all table flags
            ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS
            resetTableTracking
        End If
        
    End If
    
End Sub

Then save, close and reopen the workbook. Reopening the workbook will run the Workbook_Open routine in ThisWorkbook and create the dicQueryTables dictionary which holds all the query tables and their links to the AfterRefresh event handler for all the query tables (Power Queries) in the workbook.

Note that whilst debugging and editing the code in the VBA editor, or if a run-time error occurs, the dicQueryTables variable may reset and lose all its values and the AfterRefresh event won't fire when a query is refreshed. If this happens, place the cursor anywhere inside the Private Sub Workbook_Open() routine in ThisWorkbook and press F5 to run it, thus recreating the dicQueryTables dictionary.

 

[KatiL]

Hi @John_w - I finally got back to this project and your changes worked perfectly! Thank you so much.

The only thing it is not doing is finishing with a message box indicating success.. something like "Data Refresh successfully completed. All Import Tabs have been updated to include only refreshed data." Can you advise on how to add that?

 

[John_w]

he only thing it is not doing is finishing with a message box indicating success.. something like "Data Refresh successfully completed. All Import Tabs have been updated to include only refreshed data." Can you advise on how to add that?

Either at the end of the ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS procedure, or after the call to it in my code above:

MsgBox "Data Refresh successfully completed. All Import Tabs have been updated to include only refreshed data."

 

[KatiL]

Oh haha.. I didn't realize it was that simple. Thanks again!

 

[KatiL]

I don't understand what I am doing wrong on the MsgBox. It does not pop up after running the clQueryTable code that you provided. I tried putting it in several places in the code that you sent to no avail. I do not want to add it to the other sub because that sub does not refresh data.

I added it to a different sub to see if it works, and it did.. but I can't seem to figure out how to make it work with this class module. Is that the right module? Here's what I have in the clQueryTable including notes in each of the locations I tried already. Wondering if there is an IF statement that I need first?

Option Explicit

Private WithEvents m_queryTable As QueryTable

Public Property Set QueryTable(ByVal qt As QueryTable)
Set m_queryTable = qt
End Property

Private Sub m_queryTable_AfterRefresh(ByVal Success As Boolean)

If Success Then

Debug.Print "AfterRefresh: " & m_queryTable.ListObject.Name

Dim tableName As String
tableName = m_queryTable.ListObject.Name

dicQueryTables(tableName)("refreshed") = 1

If dicQueryTables.Count = getRefreshCount() Then
'All queries have refreshed, therefore call your macro and reset all table flags
ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS
'tried here
resetTableTracking
'tried here
End If
'tried here
End If
'tried here - last try before giving up.
MsgBox "Data Refresh and Import Tab Updates have successfully completed."

End Sub

 

[John_w]

This should be the correct place, but I've added a Stop statement which will stop the code if it reaches there. Simply press F5 to continue, to call your macro and display the message.

Private Sub m_queryTable_AfterRefresh(ByVal Success As Boolean)

    If Success Then
    
        Debug.Print "AfterRefresh: " & m_queryTable.ListObject.Name
        
        Dim tableName As String
        tableName = m_queryTable.ListObject.Name
        
        dicQueryTables(tableName)("refreshed") = 1
        
        If dicQueryTables.Count = getRefreshCount() Then
            'All queries have refreshed, therefore call your macro and reset all table flags
            Stop
            ALL_IMPORTS_CLEAR_AND_AUTOFILL_FORMULAS
            MsgBox "Data Refresh successfully completed."
            resetTableTracking
        End If
        
    End If
    
End Sub

 

[KatiL]

Still no MsgBox.

I added the above code and ran it. When processing stopped, I hit F5 which popped up a "Go To" box with each Query Table listed. Nice to be able to see all my queries in a list and navigate to them, but that's not what I need at this time. I am not sure why adding a Stop would make the MsgBox work and I do not want users to have to take another step to reset tables. If you wouldn't mind, I would like to continue troubleshooting this one. And am open to others chiming in.

Could it be displaying somewhere that the user cannot see? Could it have something to do with my settings? I can't think of anything unusual about this workbook... Stumped.