Reiteration of problem - I have a query on an Excel
worksheet template which calls an Access database. When i
distribute this, the location of the database will be
different. I need to be able to update the database
location in the query definition somehow.

I tried getting the connection string by using:
?Sheet1.QueryTables("MyQuery").Connection
(obviously modified with my worksheet name, etc.), and
only end up getting Run-time Error 424 - Object required.

I'm new to Excel programming so I am probably missing
something obvious about how to do this, but I'm really
getting stumped and need help. The *only* way around this
that I have found so far is to literally start from a
fresh copy of the template and create the query from
scratch. Since this will be deployed in offices where I'm
not located, I don't want to have to make people there go
in and create queries like this when they are even more
unfamiliar with Excel than I am.

Please help!

I am grappling with this same issue.
I have done this in the Workbook_Open sub:

Private Const strPath As String = Environ("TEMP")
Private Const gstrConnect As String = _
"ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" _
& "Dbq=" & strPath & ";DefaultDir=" & strPath & _
& ";Extensions=asc,csv,tab,txt;"

Private Sub Workbook_Open()
Dim objSheet As Excel.Worksheet
Dim objQuery As Excel.QueryTable

For Each objSheet In Application.Worksheets
With objSheet
For Each objQuery In .QueryTables
With objQuery
.Connection = gstrConnect
End With
Next objQuery
End With
Next objSheet
Me.RefreshAll
End Sub

Hope this helps.

Pete...
-----Original Message-----
Reiteration of problem - I have a query on an Excel
worksheet template which calls an Access database. When i
distribute this, the location of the database will be
different. I need to be able to update the database
location in the query definition somehow.

I tried getting the connection string by using:
?Sheet1.QueryTables("MyQuery").Connection
(obviously modified with my worksheet name, etc.), and
only end up getting Run-time Error 424 - Object required.

I'm new to Excel programming so I am probably missing
something obvious about how to do this, but I'm really
getting stumped and need help. The *only* way around this
that I have found so far is to literally start from a
fresh copy of the template and create the query from
scratch. Since this will be deployed in offices where I'm
not located, I don't want to have to make people there go
in and create queries like this when they are even more
unfamiliar with Excel than I am.

Please help!
.

Both of these functions definitely seem to work - when I
run either of them, yes, I do see that Connection is
printed as if it is pointing to my new test database.

But then, just to test, I renamed the *old* database, and
now it fails. No matter that according to the Connection
string I am pointing to the new database, it is still
trying to point to the old database.

I'm not sure what it is I'm doing wrong.

You have to change the SQL property as well. I thought the connection would
cascade to the SQL, but apparently not:

Sub ChangeDataSource()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection
Debug.Print .Sql
Debug.Print "===================="
..Connection = Application.Substitute( _
.Connection, _
"C:\Program Files\Microsoft Office\Office\Samples", _
"c:\Data")
..Sql = Application.Substitute( _
.Sql, _
"C:\Program Files\Microsoft Office\Office\Samples", _
"c:\Data")

Debug.Print .Connection
Debug.Print .Sql
..Refresh
End With
End Sub


Sub ChangeDataSource1()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection
Debug.Print .Sql
Debug.Print "===================="
..Connection = Application.Substitute( _
.Connection, _
"c:\Data", _
"C:\Program Files\Microsoft Office\Office\Samples")
..Sql = Application.Substitute( _
.Sql, _
"c:\Data", _
"C:\Program Files\Microsoft Office\Office\Samples")

Debug.Print .Connection
Debug.Print .Sql
..Refresh
End With
End Sub

--
Regards,
Tom Ogilvy

"Jennifer Crawford" wrote in message
...
Both of these functions definitely seem to work - when I
run either of them, yes, I do see that Connection is
printed as if it is pointing to my new test database.

But then, just to test, I renamed the *old* database, and
now it fails. No matter that according to the Connection
string I am pointing to the new database, it is still
trying to point to the old database.

I'm not sure what it is I'm doing wrong.

Thank you!!! That was the missing piece - it works
perfectly.

Just curious - How is the performance? Is it fast? Also, have you looked at
the DSN-less connection alternative with ADO?
Thanks,
--
RMC,CPA


"Jennifer Crawford" wrote in message
...
Thank you!!! That was the missing piece - it works
perfectly.