Hyperlinks - change destination to over 2000 links!!!!

[LittleAnn]

Hi

I have an excel sheet with over 2000 hyperlinks, problem is that the links
are not correct and can not open the source files as the location files have
all been changed.

Is there an easy way to change the file paths, I have tried the =hyperlink
function but to no avail and also inserted a VBA code to show the file path
(currently can only view the text display, so unable to do a simple find and
replace to automatically do all the work for me)!!! Any help would be
appreciated.

The current file path in the sheets is as follows:

file:///\\nts03\Jobs\6_Graphics\IncomingGraphics\Photos\25 Photos 1\250015.JPG

Displaying just 25 Photos 1\250015.JPG on the sheet but I need to change
this to

file:///\\nts03\Jobs\6_Graphics\IncomingGraphics\Photos\11 Photos 1\110015.JPG

with just 110015.jpg showing on the display text.

Thanks

 

[Dave Peterson]

I've never had problems with the =hyperlink() worksheet function. In fact, I
think that it behaves better than the Insert|Hyperlink version.

If you're interested in using them, you should post the formula you used (and
the values in any cell that the formula uses) and describe what problems you
had.

Saved from a previous post:

If you used Insert|hyperlink (xl2003 menus), you'll have more work to do. But
the good news is David McRitchie has done most of it for you:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[LittleAnn]

Thanks for the reply, I seen the previous post and used that the fix macro in
order to convert and it actually did work.

The problem now actually is that what I found was that someone had entered
the hyperlinks wrong and linked up each cell to the incorrect File!!!!!

Thus, I now need to try find a new formula / macro that will look up the
cell in column A and then opens the folder where all the corresponding
..jpg's are kept and picks the correct file in order to insert the hyperlink
to that file. as there is over 2000 records I need it done swiftly, is it
possible?????
Example:
Clumn A File Name 00001 (macro will look this cell open the folder,
search for that file i.e corresponding .jpg and insert the link to Column B)

Column B Hyperlink
file:///\\nts03\Jobs\6_Graphics\IncomingGraphics\Photos\11 Photos
1\1100001.jpg