Run VBA Code after data connection is refreshed

Audioa84

Board Regular
Joined
Oct 28, 2013
Messages
61
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
Maybe I don't understand your problem but couldn't you simply force the query to refresh in your VBA code?

Gary

Code:
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

Board Regular
Joined
Oct 28, 2013
Messages
61
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

Board Regular
Joined
Sep 13, 2013
Messages
229
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,294
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top