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

whose2know

Board Regular
Joined
May 1, 2002
Messages
59
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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Ed Acosta

Board Regular
Joined
Feb 18, 2002
Messages
54
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

Board Regular
Joined
May 1, 2002
Messages
59
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

Board Regular
Joined
May 1, 2002
Messages
59
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

New Member
Joined
Oct 8, 2010
Messages
1

ADVERTISEMENT

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

New Member
Joined
Mar 9, 2011
Messages
2
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,973
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Ma77h3w

New Member
Joined
Mar 9, 2011
Messages
2
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?
 

techexpressinc

New Member
Joined
Apr 13, 2009
Messages
40
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,203
Messages
5,857,902
Members
431,906
Latest member
Reinette

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