Modifying ODBC data source for multiple reports

[erick-flores]

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
Tools>Macros>MS 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

 

[marbarru]

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=DatabaseName;"
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

 

[erick-flores]

Thanks for ur reply.

Can you give me more details, where do I put this code?

Thanks

 

[erick-flores]

I do have multiple worksheet, where do i need to put this code in the
differente worksheets?

 

[marbarru]

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!

 

[erick-flores]

Thank you very much, that worked perfect