Wrong Hyperlink location after sorting

[Guest]

I’m using Excel to do an index of funeral home records. Copy of each page
was taken with a digital camera with a number for each such as DSC0001,
DSC0002, or DSC0003 etc. I enter the name, then the digital number in a
difference column. I hyperlink the spreadsheet to the name on the hard
drive. By waning over the spreadsheet name I will see the correct link as
file:///C:\parkerfuneralhome\dsc0001.jpg. I then sort the file by Last name
for viewing. This works fine for the first 5000 or so records, but when I
added records up to 7000 the sort will change the file name to
file:///C:\Documents and Setting\HP_Administrator\Application
Data\Microsoft\Exec\DSC0001.JPG I can’t view the JPG file due to the wrong
file location. The default file location is C:\parkerfuneralhome. Am I
doing something wrong or is there a bug in Excel? The computer is a HP with
2 gigs of memory and 3.2 gig processor.

Thanks for the help

 

[Dave Peterson]

I'd drop the insert|hyperlink type of hyperlinks and use a cell in the same row
with the =hyperlink() function:

If Dsc0001.jpg was in A1, I could use:

=hyperlink("file:////c:\parkerfuneralhome\" & a1)

or something like this formula.

 

[Guest]

The suggested change did work for one cell, but I have over 14,000 .JPGs
already linked in the original worksheet. The problem comes when I sort the
worksheet.

 

[Dave Peterson]

Are the .jpg's in the same folder?

If yes, you can use the formula and drag it down the column. Then get rid of
the Insert|Hyperlink hyperlinks.

If no, how do you know where the files are? I'm guessing that the hyperlinks
are already corrupted and point to the wrong location.

===
To get rid of the existing hyperlinks:
select the range with the links (the whole column???)
hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
selection.hyperlinks.delete

But don't do this until you're sure you want to remove those links.

 

[Guest]

The 14,000 plus .JPGs are in 26 different folders. If I deleted the
hyperlinks, then I must re-link and I still will have the same problem. Is
there any updates or changes that I can perform to correct this problems?

 

[Dave Peterson]

Do the hyperlinks still point at the correct locations? If yes, you could have
a macro that retrieves the location and creates the =hyperlink() formulas (and
even cleans up the old hyperlinks).

But if you do delete the hyperlinks, then you'll need to put them back some way.

The 14,000 plus .JPGs are in 26 different folders. If I deleted the
hyperlinks, then I must re-link and I still will have the same problem. Is
there any updates or changes that I can perform to correct this problems?