asked on Deteching a "Connection" Refresh Event
Hi, I'm using MS Excel 2010. I have a "From Web" external data connection in one of my workbooks that refreshes periodically. I would like to know when the refresh happens, so that I can trigger a Macro event imminently after the refresh - each time the refresh happens.
I tried to use the code below to detect a data "refresh" of the connection, but my code is not working.
If ActiveWorkbook.Connections
' Do something...(this can be anything visual for testing purposes)
[B3].Value = "Refresh happened..."
End If
I tried putting the code in the code-block for the worksheet, a module and also in the "ThisWorkbook" code-block, but it didn't work in any of those locations. It didn't cause an error...it simply didn't do anything.
Does this code have to be placed in a Subroutine of some sort? -- Please keep in mind that I need the code to detect each refresh event - as long as the worksheet is open, so it's not a one time thing like "OnWorkbook_Open" or something like that.
Can anyone assist in helping me detect the connection event?
Thanks,
Fulano
I tried to use the code below to detect a data "refresh" of the connection, but my code is not working.
If ActiveWorkbook.Connections
' Do something...(this can be anything visual for testing purposes)
[B3].Value = "Refresh happened..."
End If
I tried putting the code in the code-block for the worksheet, a module and also in the "ThisWorkbook" code-block, but it didn't work in any of those locations. It didn't cause an error...it simply didn't do anything.
Does this code have to be placed in a Subroutine of some sort? -- Please keep in mind that I need the code to detect each refresh event - as long as the worksheet is open, so it's not a one time thing like "OnWorkbook_Open" or something like that.
Can anyone assist in helping me detect the connection event?
Thanks,
Fulano
Microsoft Excel
Last Comment
Mr_Fulano
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
the
For Each QT In WS.QueryTables
Next QT
captures all the querytables
Set clsQ = New clsQuery
creates an instance of the class object
Set clsQ.MyQuery = QT
assigns the actual querytable to the "myQuery" of the class object
colQueries.Add clsQ
add the class object to a collection
For Each QT In WS.QueryTables
Next QT
captures all the querytables
Set clsQ = New clsQuery
creates an instance of the class object
Set clsQ.MyQuery = QT
assigns the actual querytable to the "myQuery" of the class object
colQueries.Add clsQ
add the class object to a collection
ASKER
Thank Rgonzo, I'll study your answer and this technique and try to understand why QTs was the way to go.
I appreciate your great work!!!
Fulano
I appreciate your great work!!!
Fulano
ASKER
EXCELLENT..beyond great work!!! Very, very accurate and helpful.
So, from what I can tell, this is what happens here:
1). The Workbook_Open code calls the InitializeQueries when I open the workbook. (That part I understand).
2). The "clsQuery" code calls two different events - one before and one after the refresh. (I can follow that)
-- I'm not sure why you used QueryTables. --- That was an interesting approach.
3. In the normal Module, I'm a bit lost...I understand that it's two nested 'For Each' loops, but the code below is what confuses me.
For Each QT In WS.QueryTables
Set clsQ = New clsQuery
Set clsQ.MyQuery = QT
colQueries.Add clsQ
Next QT
In general, what is the thought process you used here?
GREAT work by the way!!!
If you could help me learn a little bit about why you did what you did, I would be greatly appreciative. I don't like to simply use code that I can't understand. That is not a good way to learn, and I think we all need to learn something new everyday.
Thank again,
Fulano