software

Ooops:
Dim qt as Querytable
set qt=Activesheet.Querytables(1)
....quot;Ardus Petusquot; gt; a écrit dans le message de news:...
gt; dim qt=Activesheet.querytables(1)
gt; qt.connection = blah
gt; qt.sql = blah
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;Catarinaquot; gt; a écrit dans le message de
gt; ...
gt; gt; Hi.
gt; gt; I have two databases that are similar, and i have created an Excel
gt; document
gt; gt; that imports data from one database.
gt; gt; My problem is that i want to change the database of the document
gt; gt; programatically. I think that creating a macro in VBA the problem is
gt; solved.
gt; gt; But i don't know how to do it.
gt; gt; Anyone have any idea?
gt; gt; Thanks
gt; gt; Catarina
gt;
gt;

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.

quot;Ardus Petusquot; wrote:

gt; Ooops:
gt; Dim qt as Querytable
gt; set qt=Activesheet.Querytables(1)
gt; ....
gt;
gt;
gt; quot;Ardus Petusquot; gt; a écrit dans le message de news:...
gt; gt; dim qt=Activesheet.querytables(1)
gt; gt; qt.connection = blah
gt; gt; qt.sql = blah
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;Catarinaquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt; gt; Hi.
gt; gt; gt; I have two databases that are similar, and i have created an Excel
gt; gt; document
gt; gt; gt; that imports data from one database.
gt; gt; gt; My problem is that i want to change the database of the document
gt; gt; gt; programatically. I think that creating a macro in VBA the problem is
gt; gt; solved.
gt; gt; gt; But i don't know how to do it.
gt; gt; gt; Anyone have any idea?
gt; gt; gt; Thanks
gt; gt; gt; Catarina
gt; gt;
gt; gt;
gt;
gt;
gt;

If your database is an Access mdb:

Sub ModQuery()
Const oldpath = quot;U:\Databases\mydatabasequot;
Const newpath = quot;C:\mydatabasequot;
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

quot;Catarinaquot; gt; a écrit dans le message de
...
gt;
gt; Thanks for your help.
gt; But this script only makes me choose another ODBC connection, it doesn't
gt; change my database.
gt; Is like this, i choose another ODBC connection with another database but
it
gt; uses the old database.
gt; I don't understand why.
gt; If you know something else that could help i apreciated.
gt; Thank you, anyway.
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; Ooops:
gt; gt; Dim qt as Querytable
gt; gt; set qt=Activesheet.Querytables(1)
gt; gt; ....
gt; gt;
gt; gt;
gt; gt; quot;Ardus Petusquot; gt; a écrit dans le message de
news:...
gt; gt; gt; dim qt=Activesheet.querytables(1)
gt; gt; gt; qt.connection = blah
gt; gt; gt; qt.sql = blah
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;Catarinaquot; gt; a écrit dans le
message de
gt; gt; gt; ...
gt; gt; gt; gt; Hi.
gt; gt; gt; gt; I have two databases that are similar, and i have created an Excel
gt; gt; gt; document
gt; gt; gt; gt; that imports data from one database.
gt; gt; gt; gt; My problem is that i want to change the database of the document
gt; gt; gt; gt; programatically. I think that creating a macro in VBA the problem is
gt; gt; gt; solved.
gt; gt; gt; gt; But i don't know how to do it.
gt; gt; gt; gt; Anyone have any idea?
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; Catarina
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

Thank you, for this script.
My database is an SQL Server file, but i'll try to adapt the script.quot;Ardus Petusquot; wrote:

gt; If your database is an Access mdb:
gt;
gt; Sub ModQuery()
gt; Const oldpath = quot;U:\Databases\mydatabasequot;
gt; Const newpath = quot;C:\mydatabasequot;
gt; Dim qt As QueryTable
gt; Set qt = ActiveSheet.QueryTables(1)
gt; With qt
gt; .Connection = Replace(.Connection, oldpath, newpath)
gt; .CommandText = Replace(.CommandText, oldpath, newpath)
gt; End With
gt; End Sub
gt;
gt; HTH,
gt; --
gt; AP
gt;
gt; quot;Catarinaquot; gt; a écrit dans le message de
gt; ...
gt; gt;
gt; gt; Thanks for your help.
gt; gt; But this script only makes me choose another ODBC connection, it doesn't
gt; gt; change my database.
gt; gt; Is like this, i choose another ODBC connection with another database but
gt; it
gt; gt; uses the old database.
gt; gt; I don't understand why.
gt; gt; If you know something else that could help i apreciated.
gt; gt; Thank you, anyway.
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt; gt; Ooops:
gt; gt; gt; Dim qt as Querytable
gt; gt; gt; set qt=Activesheet.Querytables(1)
gt; gt; gt; ....
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ardus Petusquot; gt; a écrit dans le message de
gt; news:...
gt; gt; gt; gt; dim qt=Activesheet.querytables(1)
gt; gt; gt; gt; qt.connection = blah
gt; gt; gt; gt; qt.sql = blah
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt; --
gt; gt; gt; gt; AP
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Catarinaquot; gt; a écrit dans le
gt; message de
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi.
gt; gt; gt; gt; gt; I have two databases that are similar, and i have created an Excel
gt; gt; gt; gt; document
gt; gt; gt; gt; gt; that imports data from one database.
gt; gt; gt; gt; gt; My problem is that i want to change the database of the document
gt; gt; gt; gt; gt; programatically. I think that creating a macro in VBA the problem is
gt; gt; gt; gt; solved.
gt; gt; gt; gt; gt; But i don't know how to do it.
gt; gt; gt; gt; gt; Anyone have any idea?
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; Catarina
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;