Query .AfterRefresh questions

[remeng]

Hello,

I am trying to run a vba code after a query has been refreshed. My query code does work, but I'd like to tell the user that the query update is successful or failed.

My query update works:

 ActiveWorkbook.Connections("Query - part_structure_ref_doc_table").Refresh

I know the best solution is the .AfterRefresh command, but I can't seem to get it to work. I read the Microsoft areticle, but I just can't understand what I need to do with it or how.

.AfterRefresh Microsoft article

How do I call the AfterRefresh code?

Thanks,

Mike

 

[combo]

QuertTable events use the same logic as Application events. You need a custom class with variable declared with WithEvents as QueryTable. Now required QueryTable event procedures can be added in the class, in the same way as worksheet procedures in workshet module. The class has to be instantiated and your querytable assigned to querytable variable in the class, either in Class_Initialize event or directly after creation object from the class. Another MS article:

https://learn.microsoft.com/en-us/o...hapes/using-events-with-the-querytable-object

combo

 

[remeng]

Hi Combo,

I don't think I've ever used a class before. Would you be able to walk me through the basics so I can better understand the article?

Thanks

 

[combo]

Following the article:

1. class module ClsModQT

Public WithEvents qtQueryTable As QueryTable

Sub InitQueryEvent(QT As Object)
 Set qtQueryTable = QT
End Sub

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
' template created by vba when qtQueryTable is selected from left module's dropdown
' and AfterRefresh event from the right one

' handle the event here using Success as argument returned to procedure

End Sub

2. standard module:
' declare variable as the custom class ClsModQT

Dim clsQueryTable As ClsModQT
 
Sub RunInitQTEvent()
' instantiate the variable
Set clsQueryTable = New ClsModQT
' assign your querytable to the WithEvents declared variable in clsQueryTable
' change reference to valid querytable
clsQueryTable.InitQueryEvent _
QT:=ActiveSheet.QueryTables(1)
' now ActiveSheet.QueryTables(1) querytable should pass events to
' ActiveSheet.QueryTables(1) qtQueryTable,
' only AfterRefresh is processed in qtQueryTable_AfterRefresh procedure
' (the second event proceduer qtQueryTable_BeforeRefresh can be added if necessary).
End Sub

When you run the code in module you should stay with clsQueryTable object instantiated and the querytable assigned in RunInitQTEvent should pass events to qtQueryTable


combo

 

[remeng]

Scratches head... I'm totally lost lol...

Let me see if I understand this correctly.

This is the Class Code in the Class Module named ClsModQT

 Public WithEvents qtQueryTable As QueryTable

When something is done with the qtQueryTable it is referencing the variable as a Query Table.

 Sub InitQueryEvent(QT As Object)

the variable QT is an Object / callout type.

 Set qtQueryTable = QT

QT is the variable QueryTable referenced in this code:

 Public WithEvents qtQueryTable As QueryTable

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

the variable Success can be a True or False result


The following is in the standard Macro workspace.

 Dim clsQueryTable As ClsModQT

The variable / term clsQueryTable should follow the rules defined in the ClsModQT class module

 Set clsQueryTable = New ClsModQT

I'm not sure what NEW does in this statement. After this I'm completely lost since I'm not sure where it tells the query to update the table "Query - part_structure_ref_doc_table" to refresh.

would the rest of the code look like this for my situation?

clsQueryTable.InitQueryEvent _
QT:=ActiveSheet.QueryTables("Query - part_structure_ref_doc_table")
' now ActiveSheet.QueryTables("Query - part_structure_ref_doc_table") querytable should pass events to
' ActiveSheet.QueryTables("Query - part_structure_ref_doc_table") qtQueryTable,
' only AfterRefresh is processed in qtQueryTable_AfterRefresh procedure
' (the second event proceduer qtQueryTable_BeforeRefresh can be added if necessary).
End Sub

If I want a msgbox to say "It worked" and another to say "It failed", how would I add that?

Thanks for the help!

 

[combo]

I'm not sure what NEW does in this statement.

Class module is a template for object. After declaration clsQueryTable is Nothing. You need an object from the class. [tt]Set clsQueryTable = New ClsModQT[/tt] creates an object from ClsModQT.

If I want a msgbox to say "It worked" and another to say "It failed", how would I add that?

In the event procedure qtQueryTable_AfterRefresh VBA tells you about it in the value of Success, as in the code in your first link. So just two msgboxes in:
Private Sub QueryTable_AfterRefresh(Success As Boolean)
[pre] If Success Then
' Query completed successfully
Else
' Query failed or was cancelled
End If
End Sub
[/pre]

You may also see my ancient four-parts faq about events:
faq707-4973
faq707-4974
faq707-4975
faq707-4976

combo

 

[remeng]

Hi Combo,

I came up with a solution that at least prevents someone from running a macro when the query is updating. It's actually in the query properties menu.

When these settings are selected, the user just gets the spinning wheel when the code is run. All macro code stops until the query update has concluded.

ActiveWorkbook.Connections(QUERY NAME).refresh

Here are the settings:

Thank you for the help. I'll definitely read the articles you recommended.