Query table before refresh event not calling

[schallabrak]

I have set up a query from a pervasive sql database and would like to write some code that should execute before the query table is refreshed.
I have put the following code in the sheet1 object where the table is located. This is just to see if I can get the before refresh event to work:
Private Sub querytable_beforerefresh(Cancel As Boolean)
MsgBox ("brace for update")
End Sub
Needless to say, When I refresh the query table I don't receive a msgbox, nor do I get a debug error.
Is there something else I need to do to use the beforerefresh event?

 

[BrianMH]

XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events

Check out this link

 

[schallabrak]

Thanks for the Link Brian.

However, even when following that website step by step I don't get any reaction when clicking refresh on the table. That link is actually the place where I discovered the existence of he beforerefresh event in the fist place, and I have been trying to get it to work every since.

 

[BrianMH]

Have you put the code listed into a class module?

 

[schallabrak]

My first attempt was with the code from the link. This was put into a class module. On my second attempt I simplified it into the above msgbox version just to play around with it. I have placed this code in thisworksheet, sheet2, a module, and a class module but all actions give me similar results

 

[RoryA]

You do not have any code in what you posted that either declares querytable as a WithEvents variable, or assigns your querytable object to it. Do you have any such code? If not, which version of Excel is it?

 

[BrianMH]

Did you run the intitializeit macro first?

 

[schallabrak]

I am using excel 2013. Do I need to add such a line of code?
How would that look?

 

[RoryA]

You would need something like:

Private WithEvents qt as Excel.Querytable
Private Sub qt_beforerefresh(Cancel As Boolean)
MsgBox ("brace for update")
End Sub
Private Sub Worksheet_Activate()
set qt = me.listobjects(1).Querytable
End Sub

then switch worksheets, switch back, and refresh your query. (this assumes you have more than one sheet and only one query on the sheet in question)

 

[VickyW]

Hi RoryA!

Thank you so much
I hope you see this message considering the original post was 2013!
You wouldn't believe how long I've been searching the internet trying to figure out how to do what your code above does.

Since 10am this morning (it's now almost 17:30!) combing the internets and posting on forums trying to understand beforerefresh and afterrefresh events, class modules and objects.... It's all over my head.

But I have questions... is it possible to also add en afterrefresh event to the same bit of code. Something like:

Private WithEvents qt as Excel.Querytable
Private Sub qt_beforerefresh(Cancel As Boolean)
MsgBox ("brace for update")
End Sub
Private Sub qt_afterrefresh(Cancel As Boolean)
MsgBox ("huzzah updated!")
End Sub
Private Sub Worksheet_Activate()
set qt = me.listobjects(1).Querytable
End Sub

Also... (I know, I ask for too much), is there any way to make sure the code always fires, even if you don't switch worksheets first. If it was only for me I would be fine remembering to do that. But I'm building a tool for a team, and I don't think we can rely on them always switching tabs first.

All the best
Vicky