Execute code after a data connection refresh is finished

broch

New Member
Joined
Jun 12, 2011
Messages
22
Hi there,

I have a piece of code called ConvertDates that formats data contained on 6 worksheets. The 6 data sheets all contain a data connection to a website of foreign exchange tables.

What I want is for my code to execute as soon as the data connection refresh has finished.

When I use the statement

Code:
ActiveWorkbook.RefreshAll
Application.Run "Project1.xlsm!ConvertDates"

The code executes the macro whilst the refresh is still happening, thereby screwing up my results. I don't really want to use a timed wait, because the refresh speed is going to vary from user to user.

Is there some way I can tell excel to wait till the refresh has finished and then execute the code?

Any help would be hugely appreciated.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Is the BackGroundQuery property for all the queries set to False?
 
Last edited:
Upvote 0
Thanks P45cal.

After seeing your reply, I've now used the Workbook Connections dialog box to change the properties of each of the data connections and unchecked the "Enable background refresh option".

That seems to have done the trick.

Can you tell me how I would do the same in VBA? I'm getting nowhere with the help files. I can't figure out how to reference a data connection?

Thanks again, you've been a great help so far.
 
Upvote 0
You need to set the BackgroundQuery property of the Querytable object to False. If the data is set up as a table, then you access the querytable as a property of the ListObject.
 
Upvote 0
Once that property is set it should stay put.
But try recording yourself adjusting that property and you should get something like:
Code:
With ActiveWorkbook.Connections("Query from Excel Files").[COLOR=Red]ODBC[/COLOR]Connection
        .BackgroundQuery = True
From which you'll glean the type of connection, then you can write a macro (if all connections are of the same type like:
Code:
For Each cnct In ThisWorkbook.Connections
   cnct.ODBCConnection.BackgroundQuery = False
Next cnct
but the problem is I don't know how to determine what type (ODBC v. OLEDB, and more?) the connection is, so that each connection's backgroundquery can be set.

edit;
after seeing rorya's suggestion:
Code:
For Each sht In ThisWorkbook.Sheets
    For Each lo In sht.ListObjects
        lo.QueryTable.BackgroundQuery = False
    Next lo
Next sht
 
Last edited:
Upvote 0
You can check the type:
Code:
For Each cnct In ThisWorkbook.Connections
   Select case cnct.type
      case xlconnectiontypeodbc
   cnct.ODBCConnection.BackgroundQuery = False
      case xlconnectiontypeoledb
    cnct.OledbConnection.BackgroundQuery = False
   end select
Next cnct
for example
 
Upvote 0
You can check the type:
Code:
For Each cnct In ThisWorkbook.Connections
   Select case cnct.type
      case xlconnectiontypeodbc
   cnct.ODBCConnection.BackgroundQuery = False
      case xlconnectiontypeoledb
    cnct.OledbConnection.BackgroundQuery = False
   end select
Next cnct
for example
Thanks very much rorya!
I hunted for it and failed but didn't actually try it.
 
Last edited:
Upvote 0
Wow guys!

I won't even pretend to understand half of that. But I will give it all a go later tonight and report back.

Thanks a million for your help, both of you.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top