Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

help - ODBC connection details stored within spreadsheet

  • 17-12-2008 04:11PM
    #1
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭


    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.


Comments

  • #2
    Bluefrog
    Registered Users, Registered Users 2 Posts: 1,045 ✭✭✭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.


  • #3
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭DonnieBrasco


    thanks - i will investigate that.


Advertisement