[hutteto]

Hey,

I have Excel 2007.
I have 3 external data connections in a workbook. 2 are embedded,1 is
NOT. I want to refresh the 3rd one that is NOT embedded in any
worksheet, but it wont let me. I've tried doing this on the GUI AND
through VBA, and cant get either method to work.

For instance when I click on the Data tab and then click on the
connections button, it brings up the Workbook Connections screen. If
I click on 1 of the 2 connections that are embedded, then I can click
the little Refresh drop down button and I have the option of clicking
"Refresh". However if I click on the connection that is NOT embedded,
I dont have that option.

Alternatively in VBA I used the following code:
Dim w As Worksheet, qt As QueryTable
Dim cn As WorkbookConnection
Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
For Each cn In ThisWorkbook.Connections
cn.Refresh
Next

When I run it, each of the embedded queries refresh just fine, but
when it trys to execute the .Refresh command on the 3rd non-embedded
query, absolutely nothing happens. I dont get an error but the
connection also doesnt refresh.

Just for the heck of it, I tried to refresh it using a more direct
approach(which is really the same thing) using the following code:
ActiveWorkbook.Connections("CREATE TABLE
TBL_CR_Retail_Churn").Refresh

Again, it executes fine with no error, but it also doesnt refresh the
connection.

I dont care if I have to do this through the GUI or VBA, I just want
to be able to refresh non-embedded queries. its like Excel is somehow
actively rejecting the refresh command, eventhough it can clearly see
the connection and see's the .Refresh command as being valid for the
connection.

Any help will be very much appreciated.

[AB[_2_]]

It's the first time i've seen a construct like that where the
connection object is actually used as DDL statement (or the other way
around). But if it works - it's actually quite neat.
The way i see it Excel treats all the connection object as really
connection strings that pull data into the wb/ws and hence if there is
no target range for the data - there is nothing for it to refresh
(i.e., there is no range to populate with the updated data).
When you say:
"Again, it executes fine with no error, but it also doesnt refresh the
connection."
what exactly you expect to happen? How would you expect the 'refresh'
event to manifest itself?


On Mar 3, 6:35*pm, hutteto wrote:
Hey,

I have Excel 2007.
I have 3 external data connections in a workbook. *2 are embedded,1 is
NOT. *I want to refresh the 3rd one that is NOT embedded in any
worksheet, but it wont let me. *I've tried doing this on the GUI AND
through VBA, and cant get either method to work.

For instance when I click on the Data tab and then click on the
connections button, it brings up the Workbook Connections screen. *If
I click on 1 of the 2 connections that are embedded, then I can click
the little Refresh drop down button and I have the option of clicking
"Refresh". *However if I click on the connection that is NOT embedded,
I dont have that option.

Alternatively in VBA I used the following code:
* * * * Dim w As Worksheet, qt As QueryTable
* * * * Dim cn As WorkbookConnection
* * * * Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection
* * * * For Each cn In ThisWorkbook.Connections
* * * * * * cn.Refresh
* * * * Next

When I run it, each of the embedded queries refresh just fine, but
when it trys to execute the .Refresh command on the 3rd non-embedded
query, absolutely nothing happens. *I dont get an error but the
connection also doesnt refresh.

Just for the heck of it, I *tried to refresh it using a more direct
approach(which is really the same thing) using the following code:
* * * * ActiveWorkbook.Connections("CREATE TABLE
TBL_CR_Retail_Churn").Refresh

Again, it executes fine with no error, but it also doesnt refresh the
connection.

I dont care if I have to do this through the GUI or VBA, I just want
to be able to refresh non-embedded queries. *its like Excel is somehow
actively rejecting the refresh command, eventhough it can clearly see
the connection and see's the .Refresh command as being valid for the
connection.

Any help will be very much appreciated.