Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hyperlinks - change destination to over 2000 links!!!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hyperlinks - change destination to over 2000 links!!!!
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 he 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 wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hyperlinks - change destination to over 2000 links!!!!
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 "Dave Peterson" wrote: 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 he 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with hyperlinks when moving destination file in excel | Excel Discussion (Misc queries) | |||
How can I insert a row in a source that links to a destination | Excel Worksheet Functions | |||
Destination formatting 2000? | Excel Discussion (Misc queries) | |||
links in excel change destination after sort | Excel Worksheet Functions | |||
Hyperlinks - identifying source in destination sheet | Excel Discussion (Misc queries) |