I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.

Try this code to fix all hyperlinks in the workbook.

You'll need to change the values for BadString and GoodString - be careful
in making those entries to properly identify the bad section of the URL and
that when the second GoodString is inserted into it, that it comes out
correctly.

I recommend that you try this on a COPY of the workbook - it if works, fine,
if the URLs get messed up, you have the original to make another copy with
and try again.

Sub FixHyperlinks()
Dim anySheet As Worksheet
Dim anyHyperlink As Hyperlink
Const BadString = "/bad/part/bad/part"
Const GoodString = "/bad/part"

For Each anySheet In Worksheets
For Each anyHyperlink In ActiveSheet.Hyperlinks
anyHyperlink.Address = _
Replace(anyHyperlink.Address, BadString, GoodString)
Next
Next
End Sub

If you're unfamiliar with using the VB Editor, use [Alt]+[F11] to open the
editor, choose Insert | Module from the Editor's menus, and just paste the
code into the code area that appears. Close the editor and use Tools | Macro
| Macros and choose the FixHyperlinks entry and click the [Run] button.



"SLKoelker" wrote:

I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.

Maybe I am not doing this correctly, cause I couldn't get it to work. Does it
make a difference that this is not a connection to a website, but a
connection to a document on a shared drive? Here is what the correct map
looks like when I fix it:
file:///\\MIAVS02\MIAFS03\MIA\Directorate_Shared\QA\M119 FAT\12591203.doc
Granted the number at the end chages according to the document I am linking
to, but the problem with the links that don't work: ......\M119 FAT\M119
FAT\......
I have just been having to delete the second M119 FAT and then they work.
Did I not ask the right question to begin with??
Hope you can help.

"JLatham" wrote:

Try this code to fix all hyperlinks in the workbook.

You'll need to change the values for BadString and GoodString - be careful
in making those entries to properly identify the bad section of the URL and
that when the second GoodString is inserted into it, that it comes out
correctly.

I recommend that you try this on a COPY of the workbook - it if works, fine,
if the URLs get messed up, you have the original to make another copy with
and try again.

Sub FixHyperlinks()
Dim anySheet As Worksheet
Dim anyHyperlink As Hyperlink
Const BadString = "/bad/part/bad/part"
Const GoodString = "/bad/part"

For Each anySheet In Worksheets
For Each anyHyperlink In ActiveSheet.Hyperlinks
anyHyperlink.Address = _
Replace(anyHyperlink.Address, BadString, GoodString)
Next
Next
End Sub

If you're unfamiliar with using the VB Editor, use [Alt]+[F11] to open the
editor, choose Insert | Module from the Editor's menus, and just paste the
code into the code area that appears. Close the editor and use Tools | Macro
| Macros and choose the FixHyperlinks entry and click the [Run] button.



"SLKoelker" wrote:

I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.

It should not make a difference, The code I provided should go into the
workbook with the links in it.
BadString would be "\M119 FAT\M119 FAT"
and GoodString would be "\M119 FAT"

Any hyperlink has two parts, text that is displayed, and the address to go
to when the link is clicked. Sometimes these are the same thing - kind of
like twins: two different things that look exactly alike.

So if your links do those documents on the shared drive look like what you
are showing in your post, that is, when you look at the cell, you see all of
the File:///\\...M119 FAT\M119 FAT\... text, that is the text that is
displayed, and the code may have actually changed the underlying hyperlink
address. Have you tried clicking on any of the links since running the code
(and did you make sure things were exactly the same, such as the direction of
the / or \ characters in use)?

And if this does turn out to be the case (hyperlink now works, but displayed
text is not correct) then add this line to the code right before the Next
statement and run it again:

anyHyperlink.TextToDisplay = _
Replace(anyHyperlink.TextToDisplay, BadString, GoodString)

It won't alter any already repaired hyperlinks, but will repair the way the
text displayed looks for them. Make sure the worksheets are not protected
when you run this or it (probably) won't be able to change the text displayed
in the cells.

"SLKoelker" wrote:

Maybe I am not doing this correctly, cause I couldn't get it to work. Does it
make a difference that this is not a connection to a website, but a
connection to a document on a shared drive? Here is what the correct map
looks like when I fix it:
file:///\\MIAVS02\MIAFS03\MIA\Directorate_Shared\QA\M119 FAT\12591203.doc
Granted the number at the end chages according to the document I am linking
to, but the problem with the links that don't work: ......\M119 FAT\M119
FAT\......
I have just been having to delete the second M119 FAT and then they work.
Did I not ask the right question to begin with??
Hope you can help.

"JLatham" wrote:

Try this code to fix all hyperlinks in the workbook.

You'll need to change the values for BadString and GoodString - be careful
in making those entries to properly identify the bad section of the URL and
that when the second GoodString is inserted into it, that it comes out
correctly.

I recommend that you try this on a COPY of the workbook - it if works, fine,
if the URLs get messed up, you have the original to make another copy with
and try again.

Sub FixHyperlinks()
Dim anySheet As Worksheet
Dim anyHyperlink As Hyperlink
Const BadString = "/bad/part/bad/part"
Const GoodString = "/bad/part"

For Each anySheet In Worksheets
For Each anyHyperlink In ActiveSheet.Hyperlinks
anyHyperlink.Address = _
Replace(anyHyperlink.Address, BadString, GoodString)
Next
Next
End Sub

If you're unfamiliar with using the VB Editor, use [Alt]+[F11] to open the
editor, choose Insert | Module from the Editor's menus, and just paste the
code into the code area that appears. Close the editor and use Tools | Macro
| Macros and choose the FixHyperlinks entry and click the [Run] button.



"SLKoelker" wrote:

I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.

IT WORKED!! Thank you so much!!

"JLatham" wrote:

It should not make a difference, The code I provided should go into the
workbook with the links in it.
BadString would be "\M119 FAT\M119 FAT"
and GoodString would be "\M119 FAT"

Any hyperlink has two parts, text that is displayed, and the address to go
to when the link is clicked. Sometimes these are the same thing - kind of
like twins: two different things that look exactly alike.

So if your links do those documents on the shared drive look like what you
are showing in your post, that is, when you look at the cell, you see all of
the File:///\\...M119 FAT\M119 FAT\... text, that is the text that is
displayed, and the code may have actually changed the underlying hyperlink
address. Have you tried clicking on any of the links since running the code
(and did you make sure things were exactly the same, such as the direction of
the / or \ characters in use)?

And if this does turn out to be the case (hyperlink now works, but displayed
text is not correct) then add this line to the code right before the Next
statement and run it again:

anyHyperlink.TextToDisplay = _
Replace(anyHyperlink.TextToDisplay, BadString, GoodString)

It won't alter any already repaired hyperlinks, but will repair the way the
text displayed looks for them. Make sure the worksheets are not protected
when you run this or it (probably) won't be able to change the text displayed
in the cells.

"SLKoelker" wrote:

Maybe I am not doing this correctly, cause I couldn't get it to work. Does it
make a difference that this is not a connection to a website, but a
connection to a document on a shared drive? Here is what the correct map
looks like when I fix it:
file:///\\MIAVS02\MIAFS03\MIA\Directorate_Shared\QA\M119 FAT\12591203.doc
Granted the number at the end chages according to the document I am linking
to, but the problem with the links that don't work: ......\M119 FAT\M119
FAT\......
I have just been having to delete the second M119 FAT and then they work.
Did I not ask the right question to begin with??
Hope you can help.

"JLatham" wrote:

Try this code to fix all hyperlinks in the workbook.

You'll need to change the values for BadString and GoodString - be careful
in making those entries to properly identify the bad section of the URL and
that when the second GoodString is inserted into it, that it comes out
correctly.

I recommend that you try this on a COPY of the workbook - it if works, fine,
if the URLs get messed up, you have the original to make another copy with
and try again.

Sub FixHyperlinks()
Dim anySheet As Worksheet
Dim anyHyperlink As Hyperlink
Const BadString = "/bad/part/bad/part"
Const GoodString = "/bad/part"

For Each anySheet In Worksheets
For Each anyHyperlink In ActiveSheet.Hyperlinks
anyHyperlink.Address = _
Replace(anyHyperlink.Address, BadString, GoodString)
Next
Next
End Sub

If you're unfamiliar with using the VB Editor, use [Alt]+[F11] to open the
editor, choose Insert | Module from the Editor's menus, and just paste the
code into the code area that appears. Close the editor and use Tools | Macro
| Macros and choose the FixHyperlinks entry and click the [Run] button.



"SLKoelker" wrote:

I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.

Whew! <wipes sweat from brow Had me worried there. I hate it when
something works here and doesn't seem to when it gets 'there'. Glad to have
been able to help out.

"SLKoelker" wrote:

IT WORKED!! Thank you so much!!

"JLatham" wrote:

It should not make a difference, The code I provided should go into the
workbook with the links in it.
BadString would be "\M119 FAT\M119 FAT"
and GoodString would be "\M119 FAT"

Any hyperlink has two parts, text that is displayed, and the address to go
to when the link is clicked. Sometimes these are the same thing - kind of
like twins: two different things that look exactly alike.

So if your links do those documents on the shared drive look like what you
are showing in your post, that is, when you look at the cell, you see all of
the File:///\\...M119 FAT\M119 FAT\... text, that is the text that is
displayed, and the code may have actually changed the underlying hyperlink
address. Have you tried clicking on any of the links since running the code
(and did you make sure things were exactly the same, such as the direction of
the / or \ characters in use)?

And if this does turn out to be the case (hyperlink now works, but displayed
text is not correct) then add this line to the code right before the Next
statement and run it again:

anyHyperlink.TextToDisplay = _
Replace(anyHyperlink.TextToDisplay, BadString, GoodString)

It won't alter any already repaired hyperlinks, but will repair the way the
text displayed looks for them. Make sure the worksheets are not protected
when you run this or it (probably) won't be able to change the text displayed
in the cells.

"SLKoelker" wrote:

Maybe I am not doing this correctly, cause I couldn't get it to work. Does it
make a difference that this is not a connection to a website, but a
connection to a document on a shared drive? Here is what the correct map
looks like when I fix it:
file:///\\MIAVS02\MIAFS03\MIA\Directorate_Shared\QA\M119 FAT\12591203.doc
Granted the number at the end chages according to the document I am linking
to, but the problem with the links that don't work: ......\M119 FAT\M119
FAT\......
I have just been having to delete the second M119 FAT and then they work.
Did I not ask the right question to begin with??
Hope you can help.

"JLatham" wrote:

Try this code to fix all hyperlinks in the workbook.

You'll need to change the values for BadString and GoodString - be careful
in making those entries to properly identify the bad section of the URL and
that when the second GoodString is inserted into it, that it comes out
correctly.

I recommend that you try this on a COPY of the workbook - it if works, fine,
if the URLs get messed up, you have the original to make another copy with
and try again.

Sub FixHyperlinks()
Dim anySheet As Worksheet
Dim anyHyperlink As Hyperlink
Const BadString = "/bad/part/bad/part"
Const GoodString = "/bad/part"

For Each anySheet In Worksheets
For Each anyHyperlink In ActiveSheet.Hyperlinks
anyHyperlink.Address = _
Replace(anyHyperlink.Address, BadString, GoodString)
Next
Next
End Sub

If you're unfamiliar with using the VB Editor, use [Alt]+[F11] to open the
editor, choose Insert | Module from the Editor's menus, and just paste the
code into the code area that appears. Close the editor and use Tools | Macro
| Macros and choose the FixHyperlinks entry and click the [Run] button.



"SLKoelker" wrote:

I have a shared workbook with over 2000 hyperlinks. At some point over the
weekend someone (who's rights have since been dissolved) messed up the path
to the documents I had linked. Is there any easy way to remove one section of
the link without having to go through and do each one individually? It looks
like it is mapping to the same folder twice and now nothing will open. So I
have been going in when I can and deleting that extra section to get the
hyperlink to work.

There has to be an easier way. Please help.Thanks.