Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do view and edit ALL hyperlinks in a sheet? | Excel Discussion (Misc queries) | |||
Edit mulitple hyperlinks | Excel Discussion (Misc queries) | |||
Edit Hyperlinks | Excel Worksheet Functions | |||
Can VBA edit hyperlink(s) address | Excel Discussion (Misc queries) | |||
Edit Hyperlinks Globally | Excel Worksheet Functions |