Singapore Expats

Excel Hyperlinks don't work properly!

Discuss about computers & Internet. Including mobile phones, home appliances & other gadgets. Read about Windows security risks or virus updates.
Post Reply
User avatar
Strong Eagle
Moderator
Moderator
Posts: 11758
Joined: Sat, 10 Jul 2004 12:13 am
Answers: 11
Location: Off The Red Dot
Contact:

Post by Strong Eagle » Thu, 29 Oct 2009 10:14 am

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

User avatar
nakatago
Moderator
Moderator
Posts: 8364
Joined: Tue, 01 Sep 2009 11:23 pm
Location: Thunderbolts* HQ

Post by nakatago » Thu, 29 Oct 2009 10:46 am

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.
"A quokka is what would happen if there was an anime about kangaroos."

User avatar
Strong Eagle
Moderator
Moderator
Posts: 11758
Joined: Sat, 10 Jul 2004 12:13 am
Answers: 11
Location: Off The Red Dot
Contact:

Post by Strong Eagle » Thu, 29 Oct 2009 12:09 pm

sundaymorningstaple wrote: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
Regular
Regular
Posts: 98
Joined: Mon, 29 Jun 2009 5:10 pm
Contact:

Post by mrlily » Thu, 29 Oct 2009 2:24 pm

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!
https://mechanical.co.nz - Development of a Secure Threaded Websocket Server in C.

User avatar
sundaymorningstaple
Moderator
Moderator
Posts: 40549
Joined: Thu, 11 Nov 2004 1:26 pm
Answers: 21
Location: Retired on the Little Red Dot

Post by sundaymorningstaple » Thu, 29 Oct 2009 2:56 pm

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.
SOME PEOPLE TRY TO TURN BACK THEIR ODOMETERS. NOT ME. I WANT PEOPLE TO KNOW WHY I LOOK THIS WAY. I'VE TRAVELED A LONG WAY, AND SOME OF THE ROADS WEREN'T PAVED. ~ Will Rogers

mrlily
Regular
Regular
Posts: 98
Joined: Mon, 29 Jun 2009 5:10 pm
Contact:

Post by mrlily » Thu, 29 Oct 2009 5:54 pm

can you send me the file also
https://mechanical.co.nz - Development of a Secure Threaded Websocket Server in C.

User avatar
ksl
Governor
Governor
Posts: 5989
Joined: Mon, 19 Jul 2004 8:52 pm
Location: Singapore
Contact:

Post by ksl » Thu, 29 Oct 2009 7:31 pm


User avatar
sundaymorningstaple
Moderator
Moderator
Posts: 40549
Joined: Thu, 11 Nov 2004 1:26 pm
Answers: 21
Location: Retired on the Little Red Dot

Post by sundaymorningstaple » Thu, 29 Oct 2009 8:26 pm

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......
SOME PEOPLE TRY TO TURN BACK THEIR ODOMETERS. NOT ME. I WANT PEOPLE TO KNOW WHY I LOOK THIS WAY. I'VE TRAVELED A LONG WAY, AND SOME OF THE ROADS WEREN'T PAVED. ~ Will Rogers

User avatar
sundaymorningstaple
Moderator
Moderator
Posts: 40549
Joined: Thu, 11 Nov 2004 1:26 pm
Answers: 21
Location: Retired on the Little Red Dot

Post by sundaymorningstaple » Thu, 29 Oct 2009 11:46 pm

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! :???: :mad:
Last edited by sundaymorningstaple on Fri, 30 Oct 2009 12:09 am, edited 1 time in total.
SOME PEOPLE TRY TO TURN BACK THEIR ODOMETERS. NOT ME. I WANT PEOPLE TO KNOW WHY I LOOK THIS WAY. I'VE TRAVELED A LONG WAY, AND SOME OF THE ROADS WEREN'T PAVED. ~ Will Rogers

User avatar
ksl
Governor
Governor
Posts: 5989
Joined: Mon, 19 Jul 2004 8:52 pm
Location: Singapore
Contact:

Post by ksl » Thu, 29 Oct 2009 11:51 pm

sundaymorningstaple wrote:
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.
Last edited by ksl on Fri, 30 Oct 2009 10:08 am, edited 1 time in total.

mrlily
Regular
Regular
Posts: 98
Joined: Mon, 29 Jun 2009 5:10 pm
Contact:

Post by mrlily » Fri, 30 Oct 2009 1:17 am

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.
https://mechanical.co.nz - Development of a Secure Threaded Websocket Server in C.

User avatar
Strong Eagle
Moderator
Moderator
Posts: 11758
Joined: Sat, 10 Jul 2004 12:13 am
Answers: 11
Location: Off The Red Dot
Contact:

Post by Strong Eagle » Fri, 30 Oct 2009 8:45 am

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?
Last edited by Strong Eagle on Fri, 30 Oct 2009 8:54 am, edited 1 time in total.

User avatar
nakatago
Moderator
Moderator
Posts: 8364
Joined: Tue, 01 Sep 2009 11:23 pm
Location: Thunderbolts* HQ

Post by nakatago » Fri, 30 Oct 2009 8:49 am

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.
"A quokka is what would happen if there was an anime about kangaroos."

User avatar
Strong Eagle
Moderator
Moderator
Posts: 11758
Joined: Sat, 10 Jul 2004 12:13 am
Answers: 11
Location: Off The Red Dot
Contact:

Post by Strong Eagle » Fri, 30 Oct 2009 9:04 am

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

User avatar
sundaymorningstaple
Moderator
Moderator
Posts: 40549
Joined: Thu, 11 Nov 2004 1:26 pm
Answers: 21
Location: Retired on the Little Red Dot

Post by sundaymorningstaple » Fri, 30 Oct 2009 11:30 am

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?
SOME PEOPLE TRY TO TURN BACK THEIR ODOMETERS. NOT ME. I WANT PEOPLE TO KNOW WHY I LOOK THIS WAY. I'VE TRAVELED A LONG WAY, AND SOME OF THE ROADS WEREN'T PAVED. ~ Will Rogers

Post Reply
  • Similar Topics
    Replies
    Views
    Last post

Return to “Computer, Internet, Phone & Electronics”

Who is online

Users browsing this forum: No registered users and 1 guest