help - ODBC connection details stored within spreadsheet

DonnieBrasco

Excel 2000 / XP

Hi,

1 a)
I've added a microsoft ODBC for oracle connection to an oracle db via.. CONTROL PANEL -> Administrative Tools - Data Sources - USER DSN

b)
I;ve called the ODBC connection "TEST"

c)
I've Set the server name (to be my oracle alias) eg - testdb


2
I've added a query in Excel - selecting my "TEST" ODBC connection.
I was then prompted for the username and password etc and the data was returned.

3
I saved the spreadsheet and all is good. Data will refresh if any changes are made to the table when i click 'Refresh Data'.


The problem is, if I edit the ODBC connection (step 1c above) and change my server to be something else - like my productoin server.
Then the spreadsheet doesnt show the data from my production server - its still showing data from the original source set at step 1c) above.

When i Send the speadsheet to NOTEPAD - i can see the connection details are embedded/stored within the binary/text code of the actual
spreadsheet.

How do i change this? The spreadsheet above is a simple example, we have hundreds of spreadsheets that need to be repointed to a new server

cheers db.

Bluefrog

Well if I was faced with such a difficulty I think (particularly given the number of workbooks involved) that I would look to Python of all things to help me out.

I believe from a quick search on Google that you can set/update the connection information for your datasource via VBA in Excel and using Python you can run dymamic VBA within a workbook. Python should enable you to reset the datasource in all workbooks in one go

Have been using Python's ability to produce and esecute VBA on a workbook over the past few days myself in another context and am very impressed - very straight forward.

I have had that issue (although with Access) of the update to the datasource file not being reflected in the application and am all too aware the inconvenience it causes.

DonnieBrasco

thanks - i will investigate that.