Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
Hello all
My company is moving the business system from one server to another one so we need to change the ODBC data source for all the reports. The reports were created using MS Excel 2003. My task is to change every single report (there are a lot of reports). Right now what I am doing is manually changing the ODBC by opening the report and ToolsMacrosMS Script Editor then I find the DNS=name of ODBC driver; DB=name of DB, etc. This is taking me forever for each report since there are multiple worksheet in each excel report file. My question: Is there any way I can create a script to automatically change the ODBC data source from the old server to the new server? or maybe another faster way to do this? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
Like many others thing in Excel there is a object for ODBC data
connection. The object is the QueryTable. With this object you can change many properties of the ODBC connection. Look this example: Sub ChangeODBCString() Dim qt As Excel.QueryTable For Each qt In Sheets(1).QueryTables qt.Connection = "ODBC;DSN=ODBCName;pwd=PASSWORD;UID=UserId;;DBQ=Da tabaseName;" Next qt End Sub You can make loops for every book open, every sheet in the book, every QueryTable in the sheet... Good Luck Marco Barboza - San José, Costa Rica On Feb 5, 12:02 pm, "erick-flores" wrote: Hello all My company is moving the business system from one server to another one so we need to change the ODBC data source for all the reports. The reports were created using MS Excel 2003. My task is to change every single report (there are a lot of reports). Right now what I am doing is manually changing the ODBC by opening the report and ToolsMacrosMS Script Editor then I find the DNS=name of ODBC driver; DB=name of DB, etc. This is taking me forever for each report since there are multiple worksheet in each excel report file. My question: Is there any way I can create a script to automatically change the ODBC data source from the old server to the new server? or maybe another faster way to do this? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
Thanks for ur reply.
Can you give me more details, where do I put this code? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
I do have multiple worksheet, where do i need to put this code in the
differente worksheets? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
Always is better to insert a new module and that module create a new
process. In my work I had the same problem as you, so I made this litlle script: Public Sub FixConnectionString() Dim objWorkbook As Excel.Workbook Dim objWorkSheet As Excel.Worksheet Dim MyQuery As Excel.QueryTable Dim Cont As Byte Cont = 0 For Each objWorkbook In Application.Workbooks For Each objWorkSheet In objWorkbook.Worksheets For Each MyQuery In objWorkSheet.QueryTables MyQuery.Connection = "ODBC;" 'Here comes the new connection string that works for you Cont = Cont + 1 Next MyQuery Next objWorkSheet Next objWorkbook MsgBox "Queries OK:" & Cont , vbInformation + vbOKOnly, "END OF PROCESS" End Sub Good Luck! On Feb 6, 11:25 am, "erick-flores" wrote: I do have multiple worksheet, where do i need to put this code in the differente worksheets? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying ODBC data source for multiple reports
Thank you very much, that worked perfect :D
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing data source on ODBC query | Excel Discussion (Misc queries) | |||
how can i create quiries in excel from ODBC data source | Excel Programming | |||
change odbc data source | Excel Worksheet Functions | |||
Try again: VBA to automatically install ODBC data source | Excel Programming | |||
Retrieving data with SQL from ODBC source | Excel Programming |