Link to home
Start Free TrialLog in
Avatar of Mr_Fulano
Mr_FulanoFlag for United States of America

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("Connection").Refresh Then
              ' 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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mr_Fulano

ASKER

EXCELLENT work....OK, so before I let you go, can we walk through why this works? ....you definitely have the points, but I'd like to learn!!!   I tried so many different things and I never once thought of QueryTables.  

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
Avatar of Rgonzo1971
Rgonzo1971

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
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
EXCELLENT..beyond great work!!! Very, very accurate and helpful.