Wrong Hyperlink location after sorting
 

Im 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 cant 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

Wrong Hyperlink location after sorting
 

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.

Lawulm wrote:

Im 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 cant 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

Wrong Hyperlink location after sorting
 

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" wrote:

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.

Lawulm wrote:

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

Wrong Hyperlink location after sorting
 

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.

Lawulm wrote:

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" wrote:

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.

Lawulm wrote:

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


--

Dave Peterson

Wrong Hyperlink location after sorting
 

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" wrote:

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.

Lawulm wrote:

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" wrote:

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.

Lawulm wrote:

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


--

Dave Peterson

Wrong Hyperlink location after sorting
 

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.



Lawulm wrote:

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" wrote:

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.

Lawulm wrote:

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" wrote:

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.

Lawulm wrote:

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


--

Dave Peterson


--

Dave Peterson

If you have a pre-sorted copy of your workbook, try this:

Go to 'File'-'Properties'-'Summary' and check what it says under 'Hyperlink Base' (at the bottom).

If this is blank, change it to something like "H:" - assuming you don't HAVE a H drive. If you do, change it to a letter that you DON'T use as a drive.

This will force Excel to store all hyperlinks in full, rather than as a 'relative' address to the Hyperlink Base which I have found Excel gets confused about if blank - it sometimes takes it as 'C:' and sometimes as 'C:\Documents and Settings\username\Application Data...' etc.

Hopefully if you sort after that the hyperlinks won't have been destroyed.

If you'd like to e-Mail me on I can e-Mail you a workbook with a handy macro in it which I use to 'repoint' hyperlinks that have got lost. It has a UserForm for interaction and takes the name of the top folder that you know all your files are contained within (including subfolders). What the routine then does is look through the specified folders and subfolders looking for files that have the same name as the broken hyperlinks.

So, if you have a hyperlink that now reads

'C:\file1001.jpg'

but the file is really in

'C:\Main\My Library\Images\Summer\file1001.jpg'

and you enter

'C:\Main\My Library\Images\' as the starting folder, it will find 'file1001.jpg' and re-insert the folder name it's found in back into the hyperlink.

If two or more files are found with the same name in differing folders, it will be marked as a 'Version Conflict' and give you a list of folders that contain the likenames files below, giving you the chance to select which one the hyperlink should point to before clicking 'Select'.

I use it all the time when I deliberately move/archive/re-arrange files from one set of folders/drives to another and I don't know what I'd do without it. e-Mail me on the address above if you'd like a copy.

If you're interested, I also wrote a tool to 'queue up' files in selected folders into a dialogsheet which would then allow you to quickly 'point and click' which cells should link to which files. Again, I use this a lot for linking cells to files in various registers that I have to keep up-to-date.

MB