Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Execute code after a data connection refresh is finished

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Execute code after a data connection refresh is finished

    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.

  2. #2
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    Is the BackGroundQuery property for all the queries set to False?
    Last edited by p45cal; Jul 18th, 2011 at 03:38 AM. Reason: to change true to false

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    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.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,375
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    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.

  5. #5
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    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").ODBCConnection
            .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 by p45cal; Jul 18th, 2011 at 06:51 AM.

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,375
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    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

  7. #7
    Board Regular
    Join Date
    Nov 2009
    Posts
    4,768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    Quote Originally Posted by rorya View Post
    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 by p45cal; Jul 18th, 2011 at 06:55 AM.

  8. #8
    New Member
    Join Date
    Jun 2011
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    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.

  9. #9
    New Member
    Join Date
    Apr 2018
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Execute code after a data connection refresh is finished

    AAAARRRRHGGHGH!!! So simple. And I was screwing around with Do Until loops and Rube Goldberg stuff like that. Thanks for the quick fix!!

    Quote Originally Posted by p45cal View Post
    Is the BackGroundQuery property for all the queries set to False?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •