Excel new URL

[Guest]

Hallo,

i have a problem with Excel. What can I do with URLs in Excel if a part of
the URL is no more actual? That means in my case... I have about 400 URLs to
be edited. Every Link has a different URL, but... I need to update just a
part of the URL. I cannot use the find and replace tool to make it, becouse
every URL has a specific name which is shown in Excel. So I you can't see the
path directly only if you are editing it with rightclick....
In my case it is like this:
www.xyz/aaaaa
www.yxz/bbbbb
www.zyx/ccccc
....
where www.xyz is the server and path to file the direct link to a file/site.

Now the server has changed and it is for every link the same server .
In other words I need to replace www.xyz/aaaaa www.yxz/bbbbb ... to
www.abc/aaaaa www.abc/bbbbb www.abc/ccccc ...

If anyone has an idea ... please

PS: I am not familiar with VB or some other programming language.

thx in advance,


JoshLukas

 

[Arvi Laanemets]

Hi

Sub EditHyperlinks()
For Each h In ActiveSheet.Hyperlinks
h.Address = Replace(h.Address, "xyz", "abc")
Next
End Sub

 

[Guest]

I have a similar situation. It is a hyperlink that selects sheets and a cell
range. The name of the sheet has changed and I have a LOT of hyperlinks to
update. I attempted the method you mention below, but I must be doing
something wrong. Do I have to enter the entire hyperlink address between the
quotes as arguments? Can I just enter the sheet name? Old and then the new?

 

[Arvi Laanemets]

Hi

I have a similar situation. It is a hyperlink that selects sheets and a cell
range. The name of the sheet has changed and I have a LOT of hyperlinks to
update. I attempted the method you mention below, but I must be doing
something wrong. Do I have to enter the entire hyperlink address between the
quotes as arguments? Can I just enter the sheet name? Old and then the

new?

You just enter the part you want to replace, and the string you want to
replace it with. I.e.:
....
h.Address = Replace(h.Address, "OldSheetName", "NewSheetName")
....

Arvi Laanemets