Refreshing an external connection on an open file

[judgeh59]

I have 2 excel files. One file uses another for certain information that I use for configurating. I have a worksheet in the main file that has an external connection to the config file. I am using this piece of code to refresh to connection.

ActiveWorkbook.Connections("AOI Config Data FF1").Refresh

this works exactly like I want it to unless the config file is open. In other words, if somebody is changing the information in the config file at the same time VBA runs the refresh it tries to open the config file in the main file. I know this seems a bit convoluted. I setup the external connection as read-only. thanks in advance for the help.

Ernest

Be Alert, America needs more lerts

 

[SkipVought]

hi,

Is there a question?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[judgeh59]

Sorry Skip...yes, what options are available to me to deal with config file being open while I'm trying to run an external connection refresh?

Ernest

Be Alert, America needs more lerts

 

[SkipVought]

What is happening when the refresh occurs with the workbook open?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[judgeh59]

nothing - the config workbook is just open...

Be Alert, America needs more lerts

 

[SkipVought]

In other words, 1) the conection is not refreshed and 2) the resultset is not imported?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[judgeh59]

correct - no refresh and no result set imported. If I have the main excel file open and the config excel file is open and the main excel file starts a refresh, the config excel file will open in the main excel file. These excel files will/can be opened on separate computers.

Ernest

Be Alert, America needs more lerts

 

[mosmas]

What are you using this connection for? Pivottable, Table? If you refresh that object, what result do you get?

 

[judgeh59]

the data that is being refreshed is configuration data...IE: ALl, 50078A, MV1, MV2...the are 12 pieces of configuration data. I refresh the data then load that into variables that eventually update pivottables....hope that helps...

Ernest

Be Alert, America needs more lerts