Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
Excel 2002 Add-in: Open Database Connectivity available for Excel | Excel Discussion (Misc queries) | |||
Database and Excel | Excel Discussion (Misc queries) | |||
Force format change in excel | Excel Worksheet Functions | |||
Open Access Database under and Excel window using a Macro | Excel Worksheet Functions |