I have a Data Import set as a connection to another spreadsheet.

I'd like to be able to change the connected spreadsheet to another
spreadsheet, but nothing I do seems to change the connection string.

Here is the code:

With Sheets(1).QueryTables(1)
MsgBox .Connection
.Connection = Array(connect1, Range("path"), Range("File"),
connect2, connect3)
MsgBox .Connection
.CommandType = xlCmdSql
.CommandText = Array( "SELECT [F1], [F2 FROM [Combined File$] )
.Refresh BackgroundQuery:=False
End With

The msgbox shows me that the connection string is not being changed.
I can make the change manually just by editing the connection directly.
But in code, I can't make it happen. I've diddled with various other
properties (SourceDataFile, maintainconnection, enableediting, etc) but
nothing works.

Help
(Excel 2003)

The full connection string is:
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Documents and
Settings\me\Combined File Dec 2008.xls;Mode=Share Deny Write;Extended
Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry
Path="";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

A connection can't be change. You have to delete the old table and add a new
table. You can get the SQL (the command text portion of gthe query) and
change the SQL, but not the connection.

"makulski" wrote:

> I have a Data Import set as a connection to another spreadsheet.
>
> I'd like to be able to change the connected spreadsheet to another
> spreadsheet, but nothing I do seems to change the connection string.
>
> Here is the code:
>
> With Sheets(1).QueryTables(1)
> MsgBox .Connection
> .Connection = Array(connect1, Range("path"), Range("File"),
> connect2, connect3)
> MsgBox .Connection
> .CommandType = xlCmdSql
> .CommandText = Array( "SELECT [F1], [F2 FROM [Combined File$] )
> .Refresh BackgroundQuery:=False
> End With
>
> The msgbox shows me that the connection string is not being changed.
> I can make the change manually just by editing the connection directly.
> But in code, I can't make it happen. I've diddled with various other
> properties (SourceDataFile, maintainconnection, enableediting, etc) but
> nothing works.
>
> Help
> (Excel 2003)
>
> The full connection string is:
> Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Documents and
> Settings\me\Combined File Dec 2008.xls;Mode=Share Deny Write;Extended
> Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry
> Path="";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet
> OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
> OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet
> OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
> OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

I was rather coming to that conclusion. The helps file though <i>do</i> say
that connection is read/write. Nasty.

I've tried deleting the dataquery and then readding it with the same
details, but then I get an error message that says:
"A query already exists with that name".
Even though sheets(1).querytables.count replies "0" there still seems to
be some junk holding on. Do you have an example of the proper clean up
needed to delete then re-add?
Thanks.

"Joel" wrote:

> A connection can't be change. You have to delete the old table and add a new
> table. You can get the SQL (the command text portion of the query) and
> change the SQL, but not the connection.
>

OK, I got it to work.
I recorded my changing of the connection string, then I replaced the
recorded file name with the variables. This works:

..Connection = Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" _
, Range("path"), Range(Period & "File"), _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry " _
, _
"Path="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tra" _
, _
"nsactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB" _
, _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
)

I thought this rather ugly, so I rearranged it a bit into this. This
doesn't work. There must be some subtle problem with this array statement.
SInce I can't see why this doesn't work, I'll revert to the uglier version
and just leave it at that.

Const connect1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source="
Const connect2 As String = ";Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
Const connect3 As String = "Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False"
..Connection = Array(connect1, Range("path"), Range(Period & "File"),
connect2, connect3)

I don't know why you need all the parameters. try something like the code
below. Not sure that it works. I usally find with queries there is a hidden
carriage return thagt I miss when I tried to get rid of the rats nest in a
recorded macro. I usually atempt what you are doing an spend hours trying to
get the simplified code to work. Part of the problem is the command line in
the query have a maximum number of characters before you have to have a
semicolon or comma). the original code has some weird syntx that I'm not
sure even works.


..Connection = Array( _
"OLEDB;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=" & Range("path") & Range(Period & "File");" _
"Mode=Share Deny Write;" & _
"Extended Properties=""HDR=YES)


"makulski" wrote:

> OK, I got it to work.
> I recorded my changing of the connection string, then I replaced the
> recorded file name with the variables. This works:
>
> .Connection = Array( _
> "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" _
> , Range("path"), Range(Period & "File"), _
> ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet
> OLEDB:System database="""";Jet OLEDB:Registry " _
> , _
> "Path="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking
> Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tra" _
> , _
> "nsactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
> System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB" _
> , _
> "on't Copy Locale on Compact=False;Jet OLEDB:Compact Without
> Replica Repair=False;Jet OLEDB:SFP=False" _
> )
>
> I thought this rather ugly, so I rearranged it a bit into this. This
> doesn't work. There must be some subtle problem with this array statement.
> SInce I can't see why this doesn't work, I'll revert to the uglier version
> and just leave it at that.
>
> Const connect1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;User
> ID=Admin;Data Source="
> Const connect2 As String = ";Mode=Share Deny Write;Extended
> Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
> Path="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet
> OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
> Const connect3 As String = "Jet OLEDB:New Database Password="""";Jet
> OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
> OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
> Repair=False;Jet OLEDB:SFP=False"
> .Connection = Array(connect1, Range("path"), Range(Period & "File"),
> connect2, connect3)