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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Is the BackGroundQuery property for all the queries set to False?
 
Last edited:

broch

New Member
Joined
Jun 12, 2011
Messages
22
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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:

broch

New Member
Joined
Jun 12, 2011
Messages
22
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.
 

steved2112

New Member
Joined
Apr 26, 2018
Messages
1
AAAARRRRHGGHGH!!! So simple. And I was screwing around with Do Until loops and Rube Goldberg stuff like that. Thanks for the quick fix!!

Is the BackGroundQuery property for all the queries set to False?
 

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

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