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

[whose2know]

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

 

[Ed Acosta]

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.

 

[whose2know]

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??

 

[whose2know]

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).

 

[foxden]

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.

 

[Ma77h3w]

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

 

[RoryA]

Excel 2003 does not have a Connections property for the workbook. You have to refresh the querytables themselves.

 

[Ma77h3w]

Thanks.

I've now got

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?

 

[codywohlers]

Disabling background refresh solved my problem. Thanks!

 

[techexpressinc]

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