Editing HyperLinks

**simba_cubs** · 07-31-2007, 07:31 AM

Hi

I need to edit the hyperlinks in my excel sheet. I know how to do this manually and edit one at a time, but I need to change 3000 of them!

Is there a way I can do this without going through each one?

Thanks in advance

Simba

**dominicb** · 07-31-2007, 09:49 AM

Good afternoon simba_cubs

...and welcome to the forum!!

I need to edit the hyperlinks in my excel sheet

What do you mean by "edit"? Can you provide some examples as to what you have to do to each one?

DominicB

**simba_cubs** · 07-31-2007, 10:51 AM

Yes of course.

Currently I have 3000 hyperlinks which point to http://webserver/dir1/dir2/filename

I need to change this to c:\inetpub\dir1\dir2 - keeping the same filenames

Hope that make sense?

Thanks

**dominicb** · 07-31-2007, 11:24 AM

Hi simba_cubs

**BACK UP YOUR WORK**

Then, on a copy of your workbook, arrow to the first hyperlink, then hold down shift and navigate to the last hyperlink and left click on it. This should now have selected all the hyperlinks.

Now run this macro and it should go through all links replacing the path and leaving the file unchanged.

Sub Test()
oldlink = "http://webserver/dir1/dir2/"
newlink = "c:\inetpub\dir1\dir2\"
For Each usrcell In Selection
yourfile = Right(usrcell.Hyperlinks(1).Address, Len(usrcell.Hyperlinks(1).Address) - Len(oldlink))
usrcell.Hyperlinks(1).Address = newlink & yourfile
Next usrcell
End Sub

HTH

DominicB

**simba_cubs** · 08-01-2007, 04:30 AM

Hi DominicB

Thanks for your quick reply.

I created a macro and ran it against the worksheet, but I received the following error :^(

Run-time error '5':

Invalid procedure call or argument.

Any idea's?

Thanks

Simba

**simba_cubs** · 08-01-2007, 04:34 AM

Hi DominicB

It's worked! It changed the links even though I received the error I posted previous to this message.

Although it seems to have changed the links, why would I receive the error message. Sorry but I'm a bit of a newbie when it comes to macro's of that nature :^|

Thanks again in advance

Simba

Editing HyperLinks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Editing HyperLinks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions