×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Query .AfterRefresh questions

Query .AfterRefresh questions

Query .AfterRefresh questions

(OP)
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:

CODE

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

RE: Query .AfterRefresh questions

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/office/vba/excel...

combo

RE: Query .AfterRefresh questions

(OP)
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

RE: Query .AfterRefresh questions

Following the article:

1. class module ClsModQT

CODE --> ClsModQT_module

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

CODE --> module

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

RE: Query .AfterRefresh questions

(OP)
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

CODE

Public WithEvents qtQueryTable As QueryTable 

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

CODE

Sub InitQueryEvent(QT As Object) 

the variable QT is an Object / callout type.

CODE

Set qtQueryTable = QT 

QT is the variable QueryTable referenced in this code:

CODE

Public WithEvents qtQueryTable As QueryTable 


CODE

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.

CODE

Dim clsQueryTable As ClsModQT 


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

CODE

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?


CODE

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!

RE: Query .AfterRefresh questions

Quote (remeng)

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. Set clsQueryTable = New ClsModQT creates an object from ClsModQT.

Quote (remeng)

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)
 If Success Then 
 ' Query completed successfully 
 Else 
 ' Query failed or was cancelled 
 End If 
End Sub
 

You may also see my ancient four-parts faq about events:
FAQ707-4973: Understanding events. I: The basics
FAQ707-4974: Understanding events. II: Automation events
FAQ707-4975: Understanding events. III: Some useful stuff
FAQ707-4976: Understanding events. IV: A substitute for control arrays in VBA

combo

RE: Query .AfterRefresh questions

(OP)
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.

CODE

ActiveWorkbook.Connections(QUERY NAME).refresh 

Here are the settings:



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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close