Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Links (EXCEL XP)

    I have created a spreadsheet that has links (i.e., like a menu page) to other spreadsheets via the insert, hyperlink menu choices--I then link to an existing file. All of the Excel spreadsheets are on the same drive (c and in the same folder (production). Everything works great. But now, I have been requested to make this application (i.e., the spreadsheets) available via a USB flash drive so that associates can work on these spreadsheets while away from the office. I then copied the spreadsheet files to the USB flash drive---no problem yet.

    The problem is that all of the links are "hard" coded, C:JCCproductionfilename.xls. Naturally this directory only exists on my "C" drive on my work computer. Is there a way to make the links to the other spreadsheets dynamic, that is to use whatever letter the USB flash drive may be when the initial worksheet is opened? While the drive letter is dynamic, the "production" folder will always be on the USB drive and contain all of the EXCEL files. Has anyone created a "portable" excel application to be used via a USB drive?

    I would love to tell you that my end users are sophisticated enough to go to Windows explore and locate the various spreadsheets---but they are not. To make this application work via a USB drive, it has to work like it does at the office. THANKS.

  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: Links (EXCEL XP)

    Hyperlinks may be relative or absolute.

    If you put the master in a folder and have all the references in that folder (or in the same relative space in a folder or the drive) they should work.

    If you hardcoded the file paths then it will always look for them in the hardcoded path.

    You can use a simple find/replace (like in <post:=307,979>post 307,979</post:> ) to change the paths to relative (or even have the code run in the workbook open of the "menu book" to update all the links with the current path of the file on the flash drive.)

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Links (EXCEL XP)

    Steve, thanks. How could I discern the assigned drive letter of the usb via excel vba? I would need this information to fire up a macro at the launch of the "master or menu" workbook to revise the links. I'd prefer not to have to "ask" the end user for this information. Thanks again.

  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: Links (EXCEL XP)

    It should be easily obtained by looking at "thisworkbook.path". You could store it in a variable:

    <pre>dim sPath as String
    sPath = ThisWorkbook.Path</pre>


    This should be valid whether it is copied to a USB, or even another folder on their harddrive or on the network (though it may include more than just the drive letter...).

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Links (EXCEL XP)

    Steve,
    Thanks again...I will give this try tomorrow. Take care.

Posting Permissions

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