Excel MSQuery stills links to old file location

[GoatieEddie]

Hi all,

I have a spreadsheet with several worksheets that each have a msquery to access various bits of info from an access database.

I then needed to change the location of the access file and so programatically changed the sql in each query to access the correct file.

However, I now need to change some of the queries but when I try to add table, msquery still links to the old file (which is bad news in itself as it sits on a removable drive!).

How do I actually remove all reference to the old filename/location and replace it with the new one save for starting all over again?

Many thanks,

GE

 

[PHV]

Have a look at the QueryTables collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoatieEddie]

Hi PHV,

Indeed I have but I cant work out the simple syntax from the examples.

Is it as simple as

qt.connection = "C:\temp\mydb.mdb

 

[PHV]

Have you tried the macrorecorder when you do it manually to get a more detailed example ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886