Changing ODBC links in Excel

[searlerm]

Is there a way to change an ODBC link in excel without having to recreate the entire query?

 

[ETID]

Do you mean re-query?

 

[searlerm]

Pass! I have setup an odbc link to a data source but need to change it to another data source. The fields etc stay the same but I don't want to have to recreate the query from scratch.

 

[ETID]

Have you tired opening the .qry file in notepad and manualy reseting the source?

 

[searlerm]

I tried saving the query definition and editing it but with no joy - Could this be because I saved the definition file after the spreadsheet had been created & run a few times? If so, where would I find the original .qry file?

 

[ETID]

It's in a folder called Queries under Application data \ Microsoft ....(the exact tree is dependent on your install, ours is network, so it's ugly), but once you find it the .qry files can be opened with notepad (right click on it and open with notepad)

 

[searlerm]

Sorry to keep bothering you! I have no files in this directory & did a search on *.qry on my local drives - no joy!

Will this only appear if I saved the query wehn it was originally created as I dod not do this. In this case, is the query simply embedded within the spreadsheet itself?

 

[ETID]

Can you refresh the data at any time? If so you need only right click in the data somewhere, then edit query, if the wizard kicks in, cancel then say yes to edit with ms query, you should then be able to save the def. as a "your name".qry


you can also turn on the macro recorder before doin this and have a vb version of the sql, that you can edit, be sure to completey return the data to excel while recording so that all the code is written.