Results 1 to 6 of 6
  1. #1
    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

    New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    Most in our group have XL97. One of my co-workers computer lease expired and he was updated with XL2002. When he creates hyperlinks in workbooks they work in XL2002 but they do not work in XL97.

    If you try to edit the hyperlink (in XL97 or XL2002), in many of the places in the hyperlinks, instead of a backslash () separating the folders/filenames, a slash (/) is used instead.

    When you "hover" the mouse over the hyperlink in XL2002 the tooltip shows it with all backslashes and if you click on it, it works. But if you goto edit it still shows some slashes.

    When you "hover" the mouse over the hyperlink in XL97 the tooltip shows it with some some slashes. The same slashes appear in the edit hyperlinks. As is (with the slashes) clicking on the hyperlink, gives an error.

    We can edit the hyperlink in XL97 to change the slashes with backslashes and all is fine. It can be saved and used with no problem.

    If that "fixed link" (with replaced slashes) is edited in XL2002 at all (not even changing the slashes: eg add text, remove it, <ok>) the link again becomes unusable, as excel 2002 replaces some of the backslashes with slashes in the hyperlink.

    Does anyone know:
    1) Is this a "option" in 2002 that can be altered
    2) Is it a "bug" that there is a "fix for"

    and how to alter/fix it

    Or is it a "new feature" of XL2002 we have to live with (for 3-9 months, until we all get converted to XL2002 - somewhat of a pain, since the new computer is for the guy who updates this workbook with all the hyperlinks <img src=/S/frown.gif border=0 alt=frown width=15 height=15>)?

    Thanks,
    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    Excel 2002 tends to use a relative path for hyperlinks where possible; relative links are stored with forward slashes instead of backslashes. I don't know of a way to avoid or repair this irritating behavior. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  3. #3
    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: New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    They are relative in XL97 also. And XL2002 can read and use these relative links that were created in XL97. It is only the creation of them in XL2002 that seems to be the issue.

    Are you saying that XL2002 uses slashes for relative and backslashes for full path when it creates them?
    XL97 only uses backslashes for either type
    XL2002 can read either type correctly, and XL97 can only read the backslash type.

    Why does XL2002 need to differentiate when it creates them?

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    Yes, Excel 2002 uses backslashes when storing an absolute path, and forward slashes when storing a relative path. The other Office XP apps do the same.

    Why? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  5. #5
    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: New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    For anyone interested, I have "fixed" the problem, by adding the attached code to the thisworkbook object of those files with hyperlinks.

    The macro essentially converts all the slashes to backslashes within hyperlinks. This conversion will solve the problem with XL97 links and does not seem to cause a problem with XL2002 links (I have tested it only a little on my co-workers machine). It is unobtrusive to the user: A worksheet with almost 900 hyperlinks in it took less than 0.1 secs to complete the code.

    The macro runs whenever the workbook is opened.
    It loops thru each worksheet of the workbook
    If the worksheet has hyperlinks and the sheet is visible (we don't need to worry about sheets where the hyperlinks can not be accessed)
    It determines if its protected
    Then it unprotects the sheet with a null password (we typically will protect sheets with no password, to prevent accidentally overwriting and changing formulas, though it is easily removed if needed)
    If the sheet has a password, it creates an error which is trapped and the password-protected sheets (with hyperlinks) are kept track of.
    If the sheet is not protected it changes all the hyperlinks in it
    After looping thru each sheet, if there were password protected sheets, a message detailing the sheets is given and a contact name.

    Though this doesn't solve the cause of the problem, it prevents the symptoms from occuring with a negligible loss in file opening, and the user is not aware of it. The major downside is the macro requirement in each book giving a macro warning (in our case this was not an issue, they already received it.)

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: New/Edited Hyperlinks not backwards compatible (Excel 2002/97)

    Thanks for sharing!

Posting Permissions

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