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
Mr_FulanoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

In a class module renamed clsQuery
Option Explicit
 
Public WithEvents MyQuery As QueryTable
 
Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
    If Success Then MsgBox "Query has been refreshed."
End Sub
 
Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
    If MsgBox("Refresh query?", vbYesNo) = vbNo Then Cancel = True
End Sub

Open in new window

in a normal module
Option Explicit
 
Dim colQueries As New Collection
 
Sub InitializeQueries()
 
    Dim clsQ As clsQuery
    Dim WS As Worksheet
    Dim QT As QueryTable
 
    For Each WS In ThisWorkbook.Worksheets
        For Each QT In WS.QueryTables
            Set clsQ = New clsQuery
            Set clsQ.MyQuery = QT
            colQueries.Add clsQ
        Next QT
    Next WS
 
End Sub

Open in new window

und in the thisworkbook module
Private Sub Workbook_Open()
    Call InitializeQueries
End Sub

Open in new window

Regards
EE20150403.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_FulanoAuthor Commented:
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
0
Rgonzo1971Commented:
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
0
Mr_FulanoAuthor Commented:
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
0
Mr_FulanoAuthor Commented:
EXCELLENT..beyond great work!!! Very, very accurate and helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.