Excel Hyperlinks don't work properly!

[sundaymorningstaple]

Here's an interesting problem for you Windoze Excel experts.....

Uninstalled & Reinstalled Office XP with a break of about 1 week in the middle. In the meantime, I used OpenOffice (most recent version) and continued to open and work on my excel spreadsheet which has hyperlinks to several hundred jpgs in a number of subfolders. The break was because after uninstalling Office XP I found my CD drive had gone bust so couldn't reload the programme with the original disks, so I had to wait until I could get a replacement CD/DVD drive. Not sure if that is part of the reason but I suspect it is (using OpenOffice).

Anyway, got new drive, re-installed office XP and figured I was good to go (I'd been using the spreadsheet & subfolders with image files all on a 1GB SD card and working on it both in the office & at home. Worked fine in the office on my PC using Excel and worked fine on my home PC using OpenOffice. I thought!

Now, with Excel XP (2002) reinstalled at home I opened up the spreadsheet and ! My hyperlinks now only open up the 1st image file in the subfolders. If I have 100 separate links to 100 separate jpgs and they are correctly shown, why is it that all 100 links regardless of what the link destination is (within a given folder), all only show the 1st jpg in that particular subfolder!

I've done a heap of research over the past couple of nights and I'm coming to the conclusion that I've got to completely, somehow, remove office XP completely (or at least Excel) including all references to it in the registry (which uninstall doesn't do). I've looked for a took to do it but again, all I can find it 'Eraser97' a tool that does just that but for Office97.

It would seem that maybe OpenOffice has screwed around with the excel spreadsheet when it was saved in it's original format (not OpenOffice's native format) which, according to OO saves it in a Excel 97/2000/2003 format. This seems to infer that 2002 is somehow different and the file has been corrupted. I can run the file okay on the office PC as it's running Excel 2003. Saving it as an excel workbook on the office PC doesn't seem to correct the problem as it still wont run correctly on the home pc with excel 2002.

Ideas?

I'm thinking it's time for a reformat anyway!

[nakatago]

I've had similar experiences from my old company when they took our MS Office licenses, had us install OpenOffice, but require all documents to be MS Office.

Openoffice only approximates the behavior of MS office formats as they're not really open. MS also has been known to mook around with its own formats, not even following its own "standards", between versions (e.g. doc, docx, xls, xlsx). And even if Microsoft says they follow standards, they really don't.

You can also trying opening the file with office 2003, then save as 2002 format (probably farther down the dialog box).

Otherwise, you'll have to redo the file.

[sundaymorningstaple]

That's probably the only thing I haven't tried!

Am doing that at the moment. making a complete new copy of the entire folder inclusive of the spreadsheet. Then I'll take that folder and open and resave the spreadsheet in 2002. Load it to up an SD card and take it home and give it a try, still keeping a working copy in 2003 on the office PC. I'll let you know how it goes.
Thanks!

[sundaymorningstaple]

It would appear that even Excel 2003 doesn't give you the option to save in 2002 format either. It must have been a real piece of crap! Nobody wants it, not even MS. Guess it's time to go back and reinstall office 97 or just get rid of MS altogether.

[Strong Eagle]

I don't know for sure... but you might take at look at the hyperlink itself. Does it contain a drive letter? Is the link path absolute or is it relative to the spreadsheet itself? My SWAG is that OO maybe gummed up hyperlink references.

[nakatago]

I don't know for sure... but you might take at look at the hyperlink itself. Does it contain a drive letter? Is the link path absolute or is it relative to the spreadsheet itself? My SWAG is that OO maybe gummed up hyperlink references.

Oh yeah, almost forgot about that. Though it may not be completely about the paths, IIRC the original problem.

SMS, no need to reinstall. You could just inspect the hyperlinks first.

[nakatago]

or just get rid of MS altogether.

That could work; just don't save as OOO's default or edit and save the documents created with OOO in MS office.

[sundaymorningstaple]

Here's what I've got so far......

All the files are kept in a folder on a 1GB SD card that I carry back & forth from home to the office (like a thumbdrive). The Spreadsheet in in the main folder with around 6 other subfolders (where the images are kept). The drive is 'F' on my home PC. The link, as can be seen in the screenshot upper left frame is ///F:\eBay purchases\Heritage Mint\Heritage Mint - Arizona 'Seal' - front.jpg But when you click the hyperlink it opens up the file F:\eBay purchases\Heritage Mint\Heritage Mint - Alabama 'Crest'EU-0353 - front.jpg

The same file will open with all the other links that are directed to other files within that same folder. Other links to the other folders will all open only the 1st file in the folder.

Needless to say, I've just about recreated the entire spreadsheet tonight but I've yet to add any hyperlinks. Hopefully by saving the old file as a csv file and then closing excel and reopening the programme and opoening the csv file and saving it as a native 2002 document might sort it out. Guess I'll find out tomorrow night. spreadsheet is 30 columns x 488 rows at the moment!

[nakatago]

Here's what I've got so far......

All the files are kept in a folder on a 1GB SD card that I carry back & forth from home to the office (like a thumbdrive). The Spreadsheet in in the main folder with around 6 other subfolders (where the images are kept). The drive is 'F' on my home PC. The link, as can be seen in the screenshot upper left frame is ///F:\eBay purchases\Heritage Mint\Heritage Mint - Arizona 'Seal' - front.jpg But when you click the hyperlink it opens up the file F:\eBay purchases\Heritage Mint\Heritage Mint - Alabama 'Crest'EU-0353 - front.jpg

The same file will open with all the other links that are directed to other files within that same folder. Other links to the other folders will all open only the 1st file in the folder.

Needless to say, I've just about recreated the entire spreadsheet tonight but I've yet to add any hyperlinks. Hopefully by saving the old file as a csv file and then closing excel and reopening the programme and opoening the csv file and saving it as a native 2002 document might sort it out. Guess I'll find out tomorrow night. spreadsheet is 30 columns x 488 rows at the moment!

I'm not sure but it is possible that it's because the hyperlinks have spaces in them or are longer than the 8 character name + 3 letter extension. Not very familiar with windows internal workings but sometimes it could be that something like "this folder\this image.jpg" could be translated into "this~1\this~1.jpg" hence the first image is always displayed. I experienced before that thunderbird.exe would always show up as thunde~1.exe in the process manager instead of the long name.

There should be something that could be set so that it will use the long filename format, instead of the short one but I'm not sure where. It is also possible that it could in the document options. Perhaps googling "windows XP long filename format office hypelinks" or something could yield some answers. If not, one workaround is to enclose the hyperlinks in quotation marks. this folder\this image.jpg --> "this folder\this image.jpg"

[sundaymorningstaple]

Normally I'd agree with that, but I've been using this spreadsheet for around a year now in the same manner. The problem only started when I uninstalled OfficeXP and downloaded OpenOffice. The only save options that Open office give are similar to the ones that appear when saving in Excel. The only difference is you don't have the option to save as an "Microsoft Office Excel Workbook (.xls) but have the option to save as a Excel 97/2000/2003 or Excel5.0/95 or it's native format "Open Document File" (.odf). I saved it in the 97/2000/2003 excel format and that must have really screwed up the file itself. As you say, it doesn't actually save it as an Excel file but only emulates it.

Will see this evening after I get back home as start adding hyperlinks again, one at a time! Around 750 hyperlinks!

[nakatago]

Normally I'd agree with that, but I've been using this spreadsheet for around a year now in the same manner. The problem only started when I uninstalled OfficeXP and downloaded OpenOffice. The only save options that Open office give are similar to the ones that appear when saving in Excel. The only difference is you don't have the option to save as an "Microsoft Office Excel Workbook (.xls) but have the option to save as a Excel 97/2000/2003 or Excel5.0/95 or it's native format "Open Document File" (.odf). I saved it in the 97/2000/2003 excel format and that must have really screwed up the file itself. As you say, it doesn't actually save it as an Excel file but only emulates it.

Will see this evening after I get back home as start adding hyperlinks again, one at a time! Around 750 hyperlinks!

sucks to be you right now.

[Strong Eagle]

SMS... assuming that you have the correct file name as text to be used as the hyperlink, I can give you a nifty macro that will convert the text to a hyperlink, automatically for all selected entries.

Just fixed up a spreadsheet with a couple hundred text email addresses... worked like a charm.

If you want this, and the file name is in the cell, then

a) give me an example of the name... 'My Cool File.jpg'.
b) Give me either the relative or full path to the actual images.

Cheers.

PS: OR... if you can ID what is wrong with the hyperlink, I can modify the macro to replace it with the correct hyperlink.

It's just a few lines of VBA code.

[nakatago]

that sounds VERY useful. could you post the code here for future reference?

[Strong Eagle]

that sounds VERY useful. could you post the code here for future reference?

This is the code to take a text email address and turn it into a hyperlink.

You select the cells then execute the macro.

You set up the macro by copying this code into the workbook VBA mainsheet... more details available, then it shows up as a macro.

For SMS, he'd want to id the path (relative or otherwise) and that would replace the hard coded 'mailto:'.

The cell reference 'value' retrieves what you see. The cell reference 'formula' retrieves whatever created the value.

Sub HyperAdd()
'
' Converts each text hyperlink selected into a working hyperlink
'
For Each xCell In Selection
If xCell.Value <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="mailto:" & xCell.Value, TextToDisplay:=xCell.Value
End If
Next xCell
'
'
End Sub

[sundaymorningstaple]

SE, I had a look at that, and also saw something similar on one of my searches the evening before but decided that wasn't the ideal solution as I'd end up having to use it for every spreadsheet I create with hyperlinks in it. I guess I could make it an integral part of the basic excel template but that is a workaround that shouldn't be necessary.

However. Last night......

I uninstalled OpenOffice.
I backed up the registry and then deleted the registry key for excel 10.0 (XP), shut down and restarted then started up excel to creat the new registry settings.

I then opened up the file that I created the night before and tried the hyperlinks. Same crap. It will only open the first file in the folder where those images are held. If the link is to one of the other subfolders, same thing, it goes to that designated sub-folder but only fetches the first file in the folder regardless of the file name in the hyperlink. (the whole shebang is now being held on an 80GB external 2.5" drive - Excel file & half a dozen subfolders or so.)

I've brought the External drive to the office this morning. Those hyperlinks that will no longer work on my home PC work fine with the Office PC which is running Office 2003 on an XP OS.

Your macro, if I understand it correctly, needs to have the file name in the field in the spreadsheet. Unfortunately, the spreadsheet name is masked with friendly name while the file name has more info due to various views of the item so some of the "rows" have as many as 3 hyperlinks in 3 fields while all of the rows have at least one link.

As most of the updating of the spreadsheet takes place in my office, I guess I could just view the spreadsheet using OpenOffice at home and updating at work. That way I can access the links in either place.

I think it's time for a complete reformat of my home PC! It hasn't been done in 4 years! Probably something in the accumulated garbage that causing the problem. I just hate wasting an entire weekend trying to reformat & reinstall everything.

Of course, I could always put everything into the same folder including the spreadsheet. but boy what a mess to work with!