Excel - changing embeeded DSN

Question

Hello,

I have an Excel with an embeeded DSN. When I open the Excel in Notepad i see it:

DSN=serverName;Description=serverName;UID=UserName;;APP=Microsoft Office 2003;WSID=LT-533571;DATABASE=DatabaseName

I want to change the server property, how can I do it? It's driving me nuts, tried editing the excel in Notepad - crashes the Excel, tried some basic VBA, but I was only able to edit connection properties of pivot tables, and not change the DSN.

Any ideas?

Answer 1

You don't mention the Excel version, but in 2003 the data source(s) in a workbook can be changed by going to Data>Import External Data.

This however uses an interface from around the time we descended from the trees, Microsoft Query - not very user friendly. I recommend Rob van Gelder's excellent Query Editor add-in which removes much of the pain in managing data connections in Excel.

Edit: In 2007 I gather the DSN can be changed via Data tab>Properties (in Manage Connections Group)