Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

This is a discussion on Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger within the Excel Questions forums, part of the Question Forums category; I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    59

    Default Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen if you step through the macro using the debugger. When you run the macro normally, everything else functions properly, but the data is not refreshed.

    Any help is appreciated. Here is an excerpt of the code:


    Workbooks.Open Filename:="C:\Profile.xls"
    Sheets("SELECTION").Select
    Range("F3").Value = SNR

    ' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
    ActiveWorkbook.Connections("Connection").Refresh
    ActiveWorkbook.Connections("Connection14").Refresh
    ActiveWorkbook.Connections("Query from C_Profile").Refresh
    ActiveWorkbook.Connections("Query from C_Profile1").Refresh
    Last edited by whose2know; May 6th, 2009 at 05:58 PM. Reason: spelling error
    Thanks,
    Scott

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Bushkill, PA
    Posts
    54

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    I had a similar issue in a macro that I was running. The reason it worked when I stepped through was that it was slowed down. The macro was running faster than the flags I was looking for. I added a delay in the code and it worked. Try adding a delay to see if that works.

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    59

    Unhappy Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    Thanks Ed, Problem still remains, however.

    Here's the code I put in to make it wait 3 seconds...but it still does not work:

    ActiveWorkbook.RefreshAll
    Application.Wait (Now + TimeValue("00:00:03"))

    It waits, however, it only refreshed if I step through the macro with a debugger (and it refreshes in an instant.

    Any other thoughts??
    Thanks,
    Scott

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    59

    Cool Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    I think I figured it out...not sure why, but I have to refresh twice and then it seems the data is populated. I still put the time delay in just to ensure. I also took off background refresh on all queries, (but still need to refresh twice).
    Thanks,
    Scott

  5. #5
    New Member
    Join Date
    Oct 2010
    Posts
    1

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    I would strongly suggest against running it twice or adding a time delay as you never know if the time delay will be long enough.

    I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more opions.

    Your solutions only worked because they by chance, gave excel enough time to refresh before moving on. Doing what I have suggested is a far more definitive approach and will reduce bugs moving forward with your coding. I hope this helps, from one programmer to another.

  6. #6
    New Member
    Join Date
    Mar 2011
    Posts
    2

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    I have a data connection being refreshed when the excel is open as so:

    ActiveSheet.Unprotect
    ActiveWorkbook.Connections("XXXXX").Refresh
    ActiveSheet.Protect

    This works fine on Excel 2010 and 2007, however I get a run time error (438) on Excel 2003. Is there an equivalent or fix I can use which will work on 2003 as well?

    Thanks

    Matt

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,378

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    Excel 2003 does not have a Connections property for the workbook. You have to refresh the querytables themselves.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  8. #8
    New Member
    Join Date
    Mar 2011
    Posts
    2

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    Thanks.

    I've now got

    Code:
    ActiveSheet.Unprotect
    Range("Table_MEASURE_ADDED_FIELDS40").ListObject.QueryTable.Refresh BackgroundQuery:=False
    ActiveSheet.Protect
    Which again works fine on 2010/2007 and seems to update the data, but on 2003 I'm now getting a Runtime 1004 Error. The QueryTable is sorted and I saw something saying it's a bug and does this if sorted. Any fixes?

  9. #9
    New Member
    Join Date
    Sep 2011
    Posts
    2

    Default Re: Refreshing Data Connections Through VBA - Only working if macro is "stepped through" debugger

    Disabling background refresh solved my problem. Thanks!

  10. #10
    New Member techexpressinc's Avatar
    Join Date
    Apr 2009
    Posts
    39

    Default Re: Refreshing Data Connections Through VBA MS-Access

    The un-checking the background box worked for me to when connectting to MS-Access queries.
    Russ

Page 1 of 2 12 LastLast

Tags for this Thread

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
  •  


DMCA.com