Changing multiple hyperlinks

Hi, I have a spreadsheet with a list of about 100 hyperlinks to files I use 
and, because some of our folders have been moved and the folder names have 
changed, the links now no longer work.

I have tried using edit/replace to change the links but that is only 
changing the name of the hyperlink not the actual link itself. Does anyone 
know of a way of changing the links for all of the files at all please?

All of the links have the same root for example 

The files

U:\CommercialServices\book1
U:\CommercialServices\book2
U:\CommercialServices\book3
U:\CommercialServices\book4
U:\CommercialServices\book5

Are now all called

U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book1
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book2
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book3
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book4
U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book5

0
Utf
5/24/2010 8:45:01 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1192 Views

Similar Articles

[PageSpeed] 33

I like to use the =hyperlink() worksheet function for this.  I've never had one
of these worksheet functions change.

I'll put the path in a hidden cell (A1 in my example).  (Include the trailing
backslash, too!)

Then put the filenames in A2:Axxx
Then use the =hyperlink() formula in B2:Bxx.

=hyperlink("File:////"&$a$1&a2,"Click me")
and drag down

Then when I need to change the folder, I can change it one location (A1).

I could embed the path directly in the formula:
=hyperlink("File:////c:\my documents\excel\"& a2,"Click me")
and use Edit|Replace, but that seems like more work to me.

========

If you used Insert|Hyperlink, then you've noticed that edit|replace won't touch
those hyperlink addresses.

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.)

Michael wrote:
> 
> Hi, I have a spreadsheet with a list of about 100 hyperlinks to files I use
> and, because some of our folders have been moved and the folder names have
> changed, the links now no longer work.
> 
> I have tried using edit/replace to change the links but that is only
> changing the name of the hyperlink not the actual link itself. Does anyone
> know of a way of changing the links for all of the files at all please?
> 
> All of the links have the same root for example
> 
> The files
> 
> U:\CommercialServices\book1
> U:\CommercialServices\book2
> U:\CommercialServices\book3
> U:\CommercialServices\book4
> U:\CommercialServices\book5
> 
> Are now all called
> 
> U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book1
> U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book2
> U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book3
> U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book4
> U:\ LCCG\Business Support & Services\Fin & ICT\Commercial Finance\book5

-- 

Dave Peterson
0
Dave
5/24/2010 11:52:08 AM
Reply:

Similar Artilces:

Changing a text field to a number
Hello there! I imported a MS Project file in excel and I have values in cell like 3.5d (3 and a half days)...I would like to browse all cells and remove the day to get only the numbers... The 'd' is always the last character in the cell... I tried using a macro but failed... Has anyone any idea about how to browse a column (top to bottom), remove all the "d" and go to the next column on top Thank you so muchfor your precious help Michel On Mon, 4 Aug 2003 09:17:29 -0500, "Michel Khennafi" <mkhennafi@mke.catalystwms.com> wrote: >Hello there! > >...

Applescript question: changing format of selection...
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Greetings! I entered a long text in an active cell. Then I selected a part of text in the active cell. Now I want to write a script which changes format of selected text or part of the cell. Can you help me to do it please? Here is the needed code for characters XXX-YYY in an active cell: tell application "Microsoft Excel" tell font object of (characters XXX thru YYY of active cell) set subscript to true end tell end tell But how to extract XXX and YYY from the current selec...

How do I change the color scheme of the autoformat for table 2?
I am applying an autoformat to a pivot table and I want to know how to change the colors, fonts, and bold to the autoformat. How do I accomplish that? ...

Change area to bar and bar to area
Is there a way to switch the bar to area and area to bar. I have a custom type using column-area ... my display shows 2009 as area and 2008 as column. I want to switch look so the 2009 has the column ... is there a way to switch. Thanks much! Hi, You can select a series and changes it's chart type. Try selecting the area and changing to bar. Then select the original bar and change to area. Alternatively change the layout of your data so the 2008 data comes before the 2009. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Annette" <sh...

Change existing task tab on form
I would like to alter the task tab on the defualt task form. The form editor seems to only allow altering seperate tabs. Is there a way to change the fields on the defulat task tab on a form? Thanks ...

Application.HyperLink vs controlname.HyperlinkAddress
Any comments on the use of one or the other in a form that opens a linked file? or on other methods like fHandleFile by Dev Ashish? Reason I ask: I need to make some changes in a database originally created in Access 2, then upgraded to '97 then upsized to a SQL server back end, and the front end upgraded to Access 2003. A lot of the code is archaic. some behaves erratically. -- Bob Quintal PA is y I've altered my email address. ...

How do I change the order of items in a graph legend?
Hello, I am trying to create charts for different timframes, have listed the data in similar fashion in two different spread sheets. When I create graphs, the order of legend is not identical in the two graphs. It needs to be consistent. Appreciate any help along these lines. Thanks, Hi, You can use the series order option in the format dialog to change the order. Double click a series to display the format dialog. Note though that the series need to be of the same chart type and on the same axis in order to manipulate the series order. Cheers Andy NsShak wrote: > Hello, I am...

How do I change outlook settings to open a weblink from Email?
Since I installed windows7 I am unable to open weblinks from outlook emails. Ask me to contact the administrator as this operation has been cancelled or restricted. I am the administrator. There was no problems with windows Vista. How and where should I change the security settings for this. I can open another link from IE with no problem. I am grateful for any help. Thank you. This is an issue with IE. See below http://www.slipstick.com/problems/link_restrict.htm -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will ...

XML 2005 Late-Breaking and Product Presentation Deadline
XML 2005 Late-Breaking and Product Presentation Deadline - Tutorials Added/ changed XML 2005, November 14-18, Atlanta Hilton Hotel, Atlanta, GA http://www.xmlconference.org The deadline for late-breaking, product presentations, and town hall submissions is Friday, September 16. http://2005.xmlconference.org/node/31 The late-breaking presentations are intended to be filled with late- breaking news; however good presentations on other topics that don't duplicate material already on the program will also be considered for these slots. Submissions on fresh topics such as Ajax, alternatives ...

i want to change dollar sign to rands on excel invoice?
i want to currency on to rands on excel invoice and my format cell is not activeted Sylivia, Choose a defined currency format that you like, and then click on custom (at the bottom of the list). You'll see the format in its code, simply replace each currency symbol within that line with R. Cheers, A "sylvia" wrote: > i want to currency on to rands on excel invoice and my format cell is not > activeted > Hi Sylivia Clearly you are in South Africa. Go to Start|(Settings)|Control Panel|Regional Options, and select English(South Africa) as input locale. You...

Default font when changing from Plain text to HTML
In Outlook 2003: When I change a plain text mail to HTML, the font automatically becomes Times Roman 10 Point. How do I set the font I'd like it to change to Change your default Plain Text font in; Tools-> Options...-> tab Mail Format -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Allan TFF" <AllanTFF@discussions.microsoft.com> wrote in message news:2A8D931E-...

multiple drawers or 2 cashiers
Hello, Have you any experience setting up multiple drawers for 2 cashiers on the same POS register using RMS 2.0? Also since the drawer is connected to the receipt printer, I am wondering how one would best configure a second drawer? I see that RMS 2.0 has the ability to assign a drawer number to a cashier but do not see the X/Z reports or process to account for the transactions/tenders of 2 separate cashiers. Thanks for any insigts, Jim jfiset@aol.com ...

HOW DO i SYNCHRONIZE MULTIPLE WORKSHEETS IN EXCEL?
I have 7 worksheets in a workbook. all the worksheets have a lastname, firstname, employee ID in the A,B,C columns of every worksheet. I need to synchronize the worksheets so that when I click on an employee name in any worksheet and select a different worksheet I see the same row as the original employee name. try this by right click on excel icon in upper left of workbook(next to FILE)>insert this. However, it will not fire until you goto the next worksheet. Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Sheets.Select ActiveCell.EntireRow.Select ActiveSheet.Select -- Don ...

Multiple profiles with diff. exchange domains
Hi. I want to be able to open two instances/sessions of Outlook and then be able to open different profiles on those instances/sessions. Today the second opened Outlook will default to the profile opened by the first Outlook if you understand what I mean. Please help me. Rgds Patrick Arkley PAtrick Arkley <patrick@arkley.se> wrote: > I want to be able to open two instances/sessions of > Outlook and then be able to open different profiles on > those instances/sessions. Not possible. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapid...

Global edit of mutiple transactions
In my bank register I have many identical bank deposit transactions each of which needs the same split of categories, i.e. gross salary, housing, auto allowance, etc. Is there a way in Money 07 to change all of these transactions at once? Nope. "Superior Shepherd" <Superior Shepherd@discussions.microsoft.com> wrote in message news:38B43DE3-F10A-4249-9BE0-42C03658AA98@microsoft.com... > In my bank register I have many identical bank deposit transactions each > of > which needs the same split of categories, i.e. gross salary, housing, auto > allowance, etc. Is ...

Why do my jpg files change to gif on the web?
Hoping someone out there can help me. My pictures are saved as jpg but when I do "web preview" or view my website the pictures are in gif. How can I fix this? I use Publisher98. Thanks Karin wrote: > Hoping someone out there can help me. My pictures are saved as jpg but when > I do "web preview" or view my website the pictures are in gif. How can I fix > this? I use Publisher98. > Thanks Karin, I use pub98 all the time, but NEVER FOR WEBSITES. I'll get flamed for saying it, but use an actual website program or learn html to make a site to your ...

How do you change the font size and color in insert caption?
When inserting a caption it comes out in blue and a different size font - how do you change it? Modify the Caption style to reflect however you want it to appear. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "Victoria M." <Victoria M.@discussions.microsoft.com>...

Accept or reject changes in shared files
I have a shared file on a server drive which is shared by a work group of five users. I can see the hchanges highlighted for the different users, and I can accept or reject. The problem is that so can all the users.!! I need exclusive ability, as the "Master" of the situation, to acccept or reject changes. I do not want the rest of the group to have this ability. What am I doing wrong?? Thanks for any advice Jim Normile Jim, When a workbook is shared, it means more than one person can open it for update. This is normally disallowed in a multi-user environment. They both star...

Manage Multiple Mailboxes
My manager likes me to manage his Mailbox. I have permissions to his entire mailbox. He has a .pst folder where we store e-mails that we want to keep. It is just more convenient. Is there anyway we can share these without putting them on the Exchange Server. We are using Outlook 2003. Any help is appreciated. Not really. Placing .PST files on a network share is not supported because this often leads to corruption, plus they can only be accessed by one user at a time. Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvo...

Changing text in title bar
I want to change the display in my opportunity title bars. Basically, I want to be able to do something similar to contacts. In contacts form, both the first name and surname are displayed in the title bar as a full name. In the opportunity form, I want to change the display so both the opportunity title and the job number it has been assigned are shown. I realise this may be difficult to change in an existing form, but I'm hoping someone can help. Failing that, I have another custom form that I want the same functionality for (i.e. the primary field of the entity plus the value of the...

how to change Administrator mailbox Exchange 2003
We have migrated from an NT 4 domain to a new Windows 2003 Active Directory domain. We have an Exchange 5.5 and Exchange 2003 site with ADC running between the mail servers. ADC created an account for the old domain Administrator (which has a mailbox on the 5.5 server) in the AD Recepients container (and disabled it) and associated the Exchange 5.5 mailbox with it. The new domain already has an Administrator account (built-in account) in the User container. How can I associate the mailbox with the enabled Administrator account in the Users container, and get rid of the disabled Administrat...

create MULTIPLE IM accounts for corporation?
My corporation wishes to use Windows Live Messenger in-house. The software comes pre-installed on all the CPUs we buy. However, as IT Manager I need to create about 40 IM *accounts*. I sucessfully created 5 using our company's email addresses but then I got the error that I had exceed the number of allowed accounts created. I think it's somehow noting our firewall's IP address. Any computer I use within the building refuses to make new accounts. The support site said the problem would resolve itself after 72 hours but it has not. I have posted to many Microsoft f...

Help with hyperlink
Hi, Someone else posted a topic a while ago that is similiar to something I would like to do - but not quite! (Topic is now close by a moderator as it was in 2004). The original post was - " Is it there a formula, that if you click on a Cell, you automatically go to another Cell in that worksheet? (without using a macro?) And the reply suggested using - =HYPERLINK("#"&CELL("address",C5),C5) =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5) =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet t...

Change font on dropdown menu items
Hello, I would like to change the font, positioning of text etc. on my dropdown menu items. The items have been stored in a cell range on another sheet. Is there a way to format the text? Many thanks in advance. --- Johanna Unfortunately, that is not possible in XL. See link for further details, and possible work-around: http://www.contextures.com/xlDataVal08.html#Font -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Johanna Gronlund" wrote: > Hello, > > I would like to change the font, positioning of...

change pointer
hot to change it from a "hand" to an "arrow" when selecting a box or button? ...