Excel Hyperlinks don't work properly!

[Strong Eagle]

If you want to send it to me (the spreadsheet), I'll take a peek. I'm a fairly dangerous VBA guy.

[nakatago]

That is a doozy.

Though we're not sure if a complete nuke and install would fix the problem, WinXP is known to deteriorate in performance with time.

@SE, thanks for the code.

[Strong Eagle]

Your macro, if I understand it correctly, needs to have the file name in the field in the spreadsheet.

No, not really. The xCell.Address would contain the actual link whereas the xCell.Value would contain the 'friendly' info.

After looking at the exact nature and construction of the hyperlinks, the code would have to be modified to modify the construction of the hyperlink... I still think it has something to do with a relative versus absolute path, or a path construction that confuses one version and not the other.

[mrlily]

nakatago touched on it before; humour us and try... 2mins worth

make a new folder on a local drive (under the root, not 10 folders down) with a short name, like "mrlily", within that folder put some images, again with short names "mrlily001, 002 etc.."

now, are the links still all pointing the first file?

my bet is no!

[sundaymorningstaple]

Will have to wait until I get home this evening as it all works fine on my PC here at the office. It might well work. But, if it does, my question is why has it worked fine for the past one year plus and then after saving it in an excel format via OpenOffice, it doesn't work anymore (on that PC) while it works okay on my office PC running from the same location (an SD card)?

I'll give'r a shot tonight though just to confirm.

SE, I've sent you a truncated test file.

[mrlily]

can you send me the file also

[ksl]

http://office.microsoft.com/en-ca/excel ... 81033.aspx

[sundaymorningstaple]

http://office.microsoft.com/en-ca/excel ... 81033.aspx

None of the above. I've been all through the knowledge base and cannot find any reference to it at all. I've only found one instance that it's happened before, but unfortunately, no reasonable answer as the so called expert said it could not be right and the hyperlink had to be wrong?!?.

http://en.allexperts.com/q/Excel-1059/H ... xcel-2.htm

Still searching......

[sundaymorningstaple]

Well, dropped the entire folder containing the spreadsheet & links subfolders into the root directory of C: with no joy at all. No dice. Same problem. So, I took out the subfolders and the spreadsheet and put them in the root directory without the covering folder. Spreadsheet is at C:/spreadsheet.xls while the subfolders are one level down. Same situation, the spreadsheet works on the office PC using excel 2003 on and XP OS. Will not work properly (same thing) on my home PC using excel 2002 & XP OS. I've come to the conclusion that I don't have a friggin' clue! Maybe the notebook need a float test!

[ksl]

http://office.microsoft.com/en-ca/excel ... 81033.aspx

None of the above. I've been all through the knowledge base and cannot find any reference to it at all. I've only found one instance that it's happened before, but unfortunately, no reasonable answer as the so called expert said it could not be right and the hyperlink had to be wrong?!?.

http://en.allexperts.com/q/Excel-1059/H ... xcel-2.htm

Still searching......

My guess is the open office changed the file extension, which alters the data to the local server on your home computer, so it will always open the Open office file extension and not the original file extentions, data of the change can still be read on the office computer that's why it works only in the office.

Open office file extension is the culprit on your computer now. So try to root out the Open Office driver file .dll file and get rid of it, With caution not delete straight away, but isolate the dll. Just a suggestion It's sitting in the windows system folder.

The problem is that open office over took the role, with a driver, you need a driver to get the images from the local server, and that driver screwed up your excel sheet, with a file extension, so your home local server only recognises Open Office file, and the dll will kick start the search for the new extentions, the file conversion was cocked up to changing only the folder and not the subfolders. There must be a way in office to refresh all the cell data, it should then work.

[mrlily]

try running this vba function, it simply prints the address your hyperlink is aimed at. if open office has somehow chopped the arse end of the address off you'll be able to c it using this. alternatively if the output is pointing at the correct address/file you can use it to rebuild the 2002 hyperlink, as eagle boy was about to do. (i can run it for you if you forward an example file)


Function mrlily(x As Range) As Variant

mrlily = "address:" & x.Hyperlinks.Item(1).Address

End Function


for what its worth i've had similar experiences with formulas not working, the fix was simply to click on the cell, hit f2 (to start editing) then hit the enter key, this refreshed the cell internals.

[Strong Eagle]

Links all work fine on my PC for the sample spreadsheet you sent me. Pictures show up in Explorer instead of my default image viewer. I'll see if there is anything obvious.

The links are all relative to the Excel spreadsheet as in

Heritage%20Mint/Heritage%20Mint%20-%20America%20'Travelin'%20in%20Style'%20-%20front.jpg

This is different than the screen shot example you posted which shows an absolute path beginning with the drive letter.

Does the file I have exhibit the same problems on your home computer?

[nakatago]

if the links work fine for SE and SMS's work computer, I think we can safely conclude that it's SMS's home computer.

[Strong Eagle]

SMS, when the images show up incorrectly, what image viewer did Excel call to display the image? IE? MS Photo Editor? Something else?

[sundaymorningstaple]

The file I created for you was done in my office but using a copy of the master document directly from the SD card that was originally created and has the problem on my home PC. As it was a copy of the same file I didn't not attempt to check it as I've said, they all work fine on my office PC. It's when I open them up in my home pc that the problem arises.

The image files all open up in IE both in the office (IE8) and at home(IE8). What's interesting is that Excel calls it up in IE but double clicking the image file itself opens it up in Windoze Picture & Fax viewer.

I didn't test the truncated file on my home PC at all. But the original file is what I dumped into the root of C: which didn't give me any joy either.

What's the chance of ksl's suggestion with a corrupted dll file for OO.o having some bearing on the problem?