Changing a Drive letter for a Data Query. Excel 2003.

[WizzRog]

Hi all - I was wondering if you might help save my fingers with excessive typing.

I have a workbook of 52 Worksheets - each sheet is reading a data source from an MS-Access database (all from the same MDB). Currently the sheet is reading from C:\ but I would like to post the file onto a network drive (along with the MDB it reads from).

I need to change the drive mapping of each of the 52 queries from C:\ to M:\

I can do this manually, via Edit Query - and going to Microsoft Query, using the SQL button to change the drive mapping. I have done about 4 and it's very time consuming.

Any help would be greatly appreciated.

Thanks
Rog

 

[hatman]

You can write a macro with the following code:

chDrive "M"

This changes the default drive that excel looks at for Save and Open commands (and I assume QL queries too).

You can either place this code in an event or you can link it to a button or a keyboard short-cut, depending on how you want to trigger it.