Change database in Excel
 

Ooops:
Dim qt as Querytable
set qt=Activesheet.Querytables(1)
....


"Ardus Petus" a écrit dans le message de news:...
dim qt=Activesheet.querytables(1)
qt.connection = blah
qt.sql = blah

HTH
--
AP

"Catarina" a écrit dans le message de
...
Hi.
I have two databases that are similar, and i have created an Excel
document
that imports data from one database.
My problem is that i want to change the database of the document
programatically. I think that creating a macro in VBA the problem is
solved.
But i don't know how to do it.
Anyone have any idea?
Thanks
Catarina

Change database in Excel
 

Thanks for your help.
But this script only makes me choose another ODBC connection, it doesn't
change my database.
Is like this, i choose another ODBC connection with another database but it
uses the old database.
I don't understand why.
If you know something else that could help i apreciated.
Thank you, anyway.

"Ardus Petus" wrote:

Ooops:
Dim qt as Querytable
set qt=Activesheet.Querytables(1)
....


"Ardus Petus" a écrit dans le message de news:...
dim qt=Activesheet.querytables(1)
qt.connection = blah
qt.sql = blah

HTH
--
AP

"Catarina" a écrit dans le message de
...
Hi.
I have two databases that are similar, and i have created an Excel
document
that imports data from one database.
My problem is that i want to change the database of the document
programatically. I think that creating a macro in VBA the problem is
solved.
But i don't know how to do it.
Anyone have any idea?
Thanks
Catarina

Change database in Excel
 

If your database is an Access mdb:

Sub ModQuery()
Const oldpath = "U:\Databases\mydatabase"
Const newpath = "C:\mydatabase"
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables(1)
With qt
.Connection = Replace(.Connection, oldpath, newpath)
.CommandText = Replace(.CommandText, oldpath, newpath)
End With
End Sub

HTH,
--
AP

"Catarina" a écrit dans le message de
...

Thanks for your help.
But this script only makes me choose another ODBC connection, it doesn't
change my database.
Is like this, i choose another ODBC connection with another database but
it
uses the old database.
I don't understand why.
If you know something else that could help i apreciated.
Thank you, anyway.

"Ardus Petus" wrote:

Ooops:
Dim qt as Querytable
set qt=Activesheet.Querytables(1)
....


"Ardus Petus" a écrit dans le message de
news:...
dim qt=Activesheet.querytables(1)
qt.connection = blah
qt.sql = blah

HTH
--
AP

"Catarina" a écrit dans le
message de
...
Hi.
I have two databases that are similar, and i have created an Excel
document
that imports data from one database.
My problem is that i want to change the database of the document
programatically. I think that creating a macro in VBA the problem is
solved.
But i don't know how to do it.
Anyone have any idea?
Thanks
Catarina

Change database in Excel
 

Thank you, for this script.
My database is an SQL Server file, but i'll try to adapt the script.


"Ardus Petus" wrote:

If your database is an Access mdb:

Sub ModQuery()
Const oldpath = "U:\Databases\mydatabase"
Const newpath = "C:\mydatabase"
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables(1)
With qt
.Connection = Replace(.Connection, oldpath, newpath)
.CommandText = Replace(.CommandText, oldpath, newpath)
End With
End Sub

HTH,
--
AP

"Catarina" a écrit dans le message de
...

Thanks for your help.
But this script only makes me choose another ODBC connection, it doesn't
change my database.
Is like this, i choose another ODBC connection with another database but
it
uses the old database.
I don't understand why.
If you know something else that could help i apreciated.
Thank you, anyway.

"Ardus Petus" wrote:

Ooops:
Dim qt as Querytable
set qt=Activesheet.Querytables(1)
....


"Ardus Petus" a écrit dans le message de
news:...
dim qt=Activesheet.querytables(1)
qt.connection = blah
qt.sql = blah

HTH
--
AP

"Catarina" a écrit dans le
message de
...
Hi.
I have two databases that are similar, and i have created an Excel
document
that imports data from one database.
My problem is that i want to change the database of the document
programatically. I think that creating a macro in VBA the problem is
solved.
But i don't know how to do it.
Anyone have any idea?
Thanks
Catarina