Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check/Change Drives in macros (Excel 2002)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Check/Change Drives in macros (Excel 2002)

    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

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check/Change Drives in macros (Excel 2002)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Check/Change Drives in macros (Excel 2002)

    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

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check/Change Drives in macros (Excel 2002)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •