Run VBA Code after data connection is refreshed

[Audioa84]

Hello all,
I have a worksheet that has a connection to an Access database query. I unfortunately am not the only person who uses this file and who may refresh the connection to get the most up to date data. Is there an event handler of some type that can catch a refresh of a data connection? I would like to do a few things with the data (in vba) after it is refreshed and I don't want to assume the other people would remember to do these things.

Thanks,
Audioa84

 

[Gary McMaster]

Maybe I don't understand your problem but couldn't you simply force the query to refresh in your VBA code?

Gary

Dim oSheet As Worksheet
Dim oQuery As QueryTable

Set oSheet = ActiveSheet

For Each oQuery In oSheet.QueryTables

    oQuery.BackgroundQuery = False
    oQuery.Refresh

Next oQuery

 

[Audioa84]

I could, but the problem would be the person who clicks refresh all and then proceeds to use the data without the additional steps I have in the vba code. However, I do appreciate the code as I will use it elsewhere.

 

[btomjack]

You can also set that property in the Workbook Connection itself. I'm assuming you're using Excel 2007 or later:
1. Select the Data tab
2. Click on "Connections"
3. Select the appropriate connection and click "Properties"
4. On the "Usage" tabe, uncheck "Enable Background Refresh"

This will have the same result as the code above, but it will happen any time someone clicks the Refresh button.