About Us

BizMark

Quote:

Originally Posted by Lawulm

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

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loosing correct hyperlnk location after sorting spreadsheet data	LostnSpace	Excel Discussion (Misc queries)	2	February 2nd 06 10:15 PM
Stock Location Sorting Problem	mark1caroline	Excel Discussion (Misc queries)	3	July 13th 05 03:53 PM
hyperlink an excel cell to a specific location wthin application f	dirtboy	New Users to Excel	1	January 17th 05 08:07 PM
hyperlink an excel cell to a specific location wthin application f	dirtboy	Excel Discussion (Misc queries)	0	January 17th 05 08:03 PM
Removing hyperlink	Frank Marousek	Excel Discussion (Misc queries)	3	January 12th 05 09:53 PM

Menu

About Us