Editing HyperLinks

[simba_cubs]

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]

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]

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]

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.

Please Login or Register  to view this content.

HTH

DominicB

[simba_cubs]

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]

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

[dominicb]

Hi simba_cubs

The code worked fine on my machine without any errors, but I only tested it on a range of 4 (consecutive) very simple hyperlinks. Without seeing your file and exactly where the macro stopped I couldn't say what the problem is, but I hope it is now sorted and has saved you a few hours of laborious work.

DominicB

[simba_cubs]

It certainly has thanks

I've just noticed it hasn't kept the filenames. The link now appears
as c:\inetpub\dir1\dir2\.pdf instead of c:\inetpub\dir1\dir2\filename.pdf

Although it does show the data in the "text to display", but in the hyperlink it just states the c:\inetpub\dir1\dir1.pdf

Is there a where I can keep the filenames but just change the path to the file?

Thanks

Simba

[dominicb]

Hi simba_cubs

OK. Go back to your original files - again remembering to try this on copies only.

After I posted the code yesterday I thought of a slightly simpler way of doing things. Try using this code instead and see how things go :

Please Login or Register  to view this content.

HTH

DominicB

[simba_cubs]

Hi DominicB

It's worked! Thanks ever so much, really appreciate it.

Thanks again

Simba