Execute code after a data connection refresh is finished

[broch]

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

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.

 

[p45cal]

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

 

[broch]

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]

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]

Once that property is set it should stay put.
But try recording yourself adjusting that property and you should get something like:

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:

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:

For Each sht In ThisWorkbook.Sheets
    For Each lo In sht.ListObjects
        lo.QueryTable.BackgroundQuery = False
    Next lo
Next sht

 

[RoryA]

You can check the type:

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]

You can check the type:

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.

 

[broch]

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]

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?