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("Connection").Refresh Then
' Do something...(this can be anything visual for testing purposes)
[B3].Value = "Refresh happened..."
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?
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
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.