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

 

[Vu Compra]

Re: Refreshing Data Connections Through VBA MS-Access

Disabling the background refresh in the connection worked for me and my co-worker, however not for colleagues with other (probably superior) CRM rights. I shared my Query with all CRM users, but this doesnt seem to change anything. Colleagues receive the following error messages, after enabling the content of the xlsm file:
- The following data range failed to refresh: Query from Microsoft CRM continue to refresh all?
- The following data range failed to refresh: Query from Microsoft CRM continue to refresh all? (2 connections)
- Runtime error 1004 | This Web Query returned no data... @ Active.Workbook.RefreshAll
- Do you want to make this file a Trusted Document?
- This Web Query returned no data, to change the query, click OK, click the error on the name box in the formula bar, click the name of the external data range for the Web Query, right-click the selection, and then click edit Query
- The following data range failed to refresh: Query from Microsoft CRM continue to refresh all?
- This Web Query returned no data, to change the query, click OK, click the error on the name box in the formula bar, click the name of the external data range for the Web Query, right-click the selection, and then click edit Query
- The following data range failed to refresh: Query from Microsoft CRM continue to refresh all?

This thread is a bit older, but maybe there is someone around with some ideas. Im using Excel 2010 and CRM 2007 by the way.
Anything helps

 

[techexpressinc]

Re: Refreshing Data Connections Through VBA MS-Access

I would make sure the Options - Trust Center selections with Excel are set to let Data Connections go. I have bumped into that blockage, especially with MSOffice2010.

Russ