Updating excel hyperlinks

[xaser]

Hi folks,

I have an issue with excel hyperlinks not working. The spreadsheet was created with a different program and it has used "#" in the hyperlinks. Is there a simple solution (hopefully VBA) that can search and replace characters within a hyperlink? Usually I would manually update them but there are a few hundred.

 

[MUKESHY12390]

I think you can use find and replace function to replace "#" in cell which is quite easy.

CTR+H --> click to options button-->> uncheck box ( Match case and match entire cell contents).

 

[xaser]

Thanks for the reply. I have tried that but it doesn't work. Even if it could find the cell value it would not update the hyperlink. Ultimately I would like to screentip (text to display) to stay the same and only update the hyperlink. Find and replace would do almost the exact opposite.

 

[MUKESHY12390]

post some sample I will look into.

 

[xaser]

Hi Mukeshy, don't know what you mean by sample but please see below information. Hyperlink location and Updated link is NOT a column of data. basically i need to change the hyperlink location to the updated link.

Item Name	Hyperlink location	Updated Link
Test #1	Folder 1\Test #1	Folder%201\Test%20%231
Test #2	Folder 1\Test #2	Folder%201\Test%20%232
Test #3	Folder 1\Test #3	Folder%201\Test%20%233
Test #4	Folder 1\Test #4	Folder%201\Test%20%234
Test #5	Folder 1\Test #5	Folder%201\Test%20%235
Test #6	Folder 1\Test #6	Folder%201\Test%20%236
Test #7	Folder 1\Test #7	Folder%201\Test%20%237

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[hiker95]

xaser,

We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[MUKESHY12390]

perhaps this..

Sub Trythis()
'''''' to test  take new worksheet copy and paste entire code to module  by pressing (ALT+F11)
'''''create hyperlink www.home.comon sheet1  then Run this code
Dim h As Excel.Hyperlink
Dim ws As Worksheet


Set ws = Worksheets("Sheet1")


For Each h In ws.Hyperlinks
    h.Address = Replace(h.Address, "home", "work")
    h.TextToDisplay = Replace(h.TextToDisplay, "home", "work")
Next


End Sub

 

[xaser]

Hi Folks,

The issue has been solved now. The # was interrupting the hyperlinks. The VBA used is below.

[COLOR=#101094][FONT=Consolas]For[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Each[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] lk [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]In[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"Sheet1"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"A:A"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Hyperlinks[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] lk[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SubAddress [/COLOR][COLOR=#303336]<>[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
        lk[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Address [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] lk[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Address [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]"%23"[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] lk[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SubAddress
        lk[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SubAddress [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] [/COLOR]</code>[COLOR=#101094][FONT=Consolas]Next[/FONT][/COLOR]

 

[MUKESHY12390]

glad you found solution.