editing odc file data connections in Excel to Stored proceedures

Question

I'm migrating my data to a new server and therefore will have a new data source address, username and password. I can keep the username and password the same, but will have to have a new address.

My issue is that I have about 20 Excel workbooks with pivot tables running off this SQL data imported to Excel using odc connections. When I set these up I connected them to a stored procedure.

I have found the location of the odc files and can edit the server address in them, but when you look at the connection string in Excel data/connections the sting remains the same.

On editing the connection file under 'data / existing connections' by ticking the 'Always use connection file' box I can ensure my data always comes from the odc file and not the string, but if I change my command type to SQL and command text to the stored procedure name, it always advises the following:

Is there a away of editing my odc file to always connect to the stored procedure, or do odc files just make the connection to the database itself, and if you want to connect to a store procedure it has to be defined int he excel file?

Thanks,

Answer 1

Solution:

It is applicable to excel 2013 and office 365

When you change the connection file do not hit ok do the steps below:

1. click export connection file
2. save the file somewhere (really does not matter where), this will change the connection file location again
3. change the connection file to desired location
4. now hit ok everything should work now without any popup

Sometimes there is problem between keyboard and chair, another time there is a problem in Microsoft :-)

Answer 2

After editing the Connection Property, click Export Connection File... before you OK out. This will save your changes to the underlying ODC file. This works for me in Excel 2007.