Check/Change Drives in macros (Excel 2002)

[nichoc00]

U drive is common folder on file server (shared area), which contains departmental folders, e.g. Finance, Logistic, HR etc. All finance users have access to Finance folder, Logistic users access to Logistic folder etc. Finance users have XLS, which contains macros and links to other workbooks or files on U drive. Is there any way to check/change automatically these documents to use different Drive letter?

[sdckapr]

If you use code in vb instead of using the drive letter to refer to them use the "full path designation" including the server.
instead of:
U:folderfilename.xls
use:
serverfolder1folder2folderfilename.xls

Look in windows explorer for teh designation
Steve

[nichoc00]

Thanks. Is there an application that can tell which spreadsheets or macros have U: hard coded? Or even better that can be run and make the changes to each macro?
Any help is greatly appreciated.
Colin

[sdckapr]

You would have to open the workbooks, and search the macro codes for the drive letters. You could do find/replace in the entire project if desired.

If you wanted a macro to do it you would have to go thru all the macro code via a macro:
Chip Pearson has some code to read code on his site though there is nothing directly applicable, it could be modified.

It would still involve, looping thru all the files you wanted to test, opening each one, then finding and replacing the appropriate items in the code, closing and then going to the next file.


Steve

[nichoc00]

Steve
Many thanks for your superb assistance. I really appreciate it.
Colin