Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks to .jpg files (Excel 2002)

    Hyperlinks to .jpg files

    Using Excel 2000 and Windows 98 I created a parts catalogue with hyperlinks to .jpg’s of the actual part. This works as expected.

    When opened in Excel 2002(XP) running on Windows XP it does not work.
    I have read the knowledge base articles:-
    “Description of link management and storage in Excel” also
    OFFXP: Problems with Hyperlinks in Office XP Documents You Open with Earlier Versions of Office”

    I then attempted to produce the catalogue from scratch using Excel 2003(XP) running on Windows XP.

    A hyperlink is produced by Excel in the form.
    ../spanners/Catalogued/R0010227.jpg
    This does not work. I replace the hyperlink with the full path – an absolute link :-
    E:maincataloguespannersCataloguedR0010227.jpg
    (Note the back slashes – the way I thought files and folders were separated.)

    This hyperlink works but when the file is saved the working hyperlink is changed back to the relative hyperlink with the slashes in the wrong direction and fails to work.

    Is it possible to produce a catalogue of this sort in Excel 2002(XP)?
    Have I missed something fundamental here? Be pleased to hear your comments.

    Tony
    Tony Beckett
    Rutland, UK

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

    Re: Hyperlinks to .jpg files (Excel 2002)

    In what sense doesn't it work? Do you get an error message if you try to follow a hyperlink in Excel 2002 or 2003? If so, what does it say?

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hi Tony

    Have you considered adding them as a comment as Rudi suggested in his excellent tip in post <post#=394,332>post 394,332</post#>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    New Lounger
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hi,

    Hope Tony doesn't mind me jumping in here. He & have been discussing this problem.

    The hyperlinks just don't do anything (no error message). This occurs about 8 times out of 400.

    When you point to the cell, the hyperlink looks right; when you go to Edit, you can see the path & slashes have been changed from slashes to / slashes.

    If you edit to change to slashes, the link will work for a while, but after save, won't work. AND if only 'look' at the hyperlink again via Edit won't work (as the slashes change direction).

    BTW, the files have been edited with Excel 2002 & Excel 2003 -- and backed up to a Maxtor and then restored -- but problem seems to occur even on files that have not been backed up and restored.

    Deb

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

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hi Deb,

    I cannot explain the error, but you could try using the HYPERLINK worksheet function instead:

    =HYPERLINK("E:maincataloguespannersCataloguedR0010 227.jpg")

    should work, and since the path is in a quoted string, Excel will not change it.

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hans, thanks for your response, sorry I should have been more specific:-

    A hyperlink is produced by Excel 2002(XP) in the form.
    ../spanners/Catalogued/R0010227.jpg
    I have .jpg’s associated with IrfanView and when I click the above hyperlink Irfanview opens with the message “No file loaded”

    Using “edit hyperlink” I replace the hyperlink with the full path – an absolute link :-
    E:maincataloguespannersCataloguedR0010227.jpg
    (Note the back slashes – the way I thought files and folders were separated. Also the Excel file is in the folder “maincatalogue”)
    I click this hyperlink and R0010227.jpg opens in IrfanView

    When the file is saved, as part of the saving process, the working absolute hyperlink is changed back to the relative hyperlink with the slashes in the wrong direction and when clicked Irfanview opens with the message “No file loaded”.

    Further experimenting has shown me that hyperlinks to .jpg’s in the same folder as the Excel file cause no problems, neither do hyperlinks to a folder ONE below the folder containing the Excel file. It is only if the folder structure gets more complicated than this do the problems start. However, hyperlinks to .jpg’s on another drive, no matter how far down the folder tree open with no problem.

    Does this mean that in Excel 2002(XP) it is not possible to set up a list of hyperlinks to .jpg files that are further away from the Excel file than the next folder?

    The problem seems to come about from Excel 2002(XP) producing hyperlinks using the forward slash / to separate files and folders. Do I just accept this limitation or is there a way round it?

    Steve - thanks for pointing me to Rudi’s post, very neat!

    Tony

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

    Re: Hyperlinks to .jpg files (Excel 2002)

    I cannot reproduce the problem in either Excel 2002 (at home) or 2003 (at work), but I know it *is* a problem, for it is mentioned in the newsgroups.
    As a workaround, I suggested to place formulas in the cells instead of using Insert | Hyperlink. A cell with an =HYPERLINK("...") formula should act as a hyperlink, but it shouldn't suffer from the problem that Excel changes the path of the hyperlink to relative.

  8. #8
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Thanks Hans

    Using the Hyperlink function does the job!

    As usual I was trying to make things too difficult....

    Your help much appreciated.

    Regards

    Tony

  9. #9
    New Lounger
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hi all,

    If use the Hyperlink function -- is the link still 'relative' -- for example, if I move to a backup drive -- all links will convert to E: drive vs C: drive is on my hard disk?

    And/or if I move them from one folder to another?

    Will think of a way to test this -- I am a 'basic' Excel user & have really never used Functions -- so not sure what the difference is.... say between hyperlink function & hyperlink -- but this is something I can read up on.

    Many thanks for all ideas,
    Deb

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

    Re: Hyperlinks to .jpg files (Excel 2002)

    Hi Deb,

    No, the Hyperlink function uses a hard-coded path. But I thought that was what you wanted, since according to Tony, relative paths didn't work.

    You could put the path in a cell. Let's say A1 contains a path such as

    C:MyFolder

    You can then use a formula like this:

    =HYPERLINK(A1 & "spannersCataloguedR0010227.jpg")

    You mostly use functions in Excel to perform calculations. For example, =TODAY() returns the current date, and =SUM(A1:A4) adds the values of A1, A2, A3 and A4. But HYPERLINK is a special funciton that makes the cell that contains it act as a hyperlink.

  11. #11
    New Lounger
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks to .jpg files (Excel 2002)

    Edited by HansV to provide link to URL - see <!help=19>Help 19<!/help>

    Hi Hans,

    hmmmm

    The 'ideal' is that the 'relative' aspect of Excel hyperlinks really works!!!

    I believe there is a bug in the software -- or there is some aspect of 'relative' that I still don't understand.

    Although I've read & re-read this, but still don't understand if there is something that I have done wrong... or another work-around that keeps the 'relativity'.

    http://support.microsoft.com/kb/328440

    I think it is the bit .. "Scenarios that may cause links to not work as expected" Although, I don't think the one I am experiencing is 'covered' -- as the article says "The following are two of the most common scenarios."

    As far as I understand the terminology, I have Not done any of the things mentioned in the 2 Scenarios...

    We do backup the database to another drive & we 'might' change the folder structure -- if all hard-coded -- then have to change all the hyperlinks manually?

    I think I will 'give up' the hyperlinks -- unless I can figure out what the problem is....

    They are a 'neat' feature -- and they usually work -- but there is some complication...

    Thanks to all!!!!

    Deb

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

    Re: Hyperlinks to .jpg files (Excel 2002)

    You can do the following:
    - Press Alt+F11 to activate the Visual Basic Editor
    - Select Insert | Module
    - Copy the following code into the module:

    Public Function MyPath(oCell As Range) As String
    Application.Volatile
    MyPath = oCell.Parent.Parent.Path
    End Function

    - Press Alt+F11 to return to Excel.
    - Use a formula like this:

    =HYPERLINK(MyPath(A1) & "spannersCataloguedR0010227.jpg")

    MyPath(A1) will return the path of the workbook. It should change automatically if the workbook is saved in or moved to another location. So the hyperlink should always point to the correct location.

Posting Permissions

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