Mass edit Hyperlinks
 

I have a spread sheet with over 270 hyperlinks, have had to move the
spreadsheet and associated links to a new location but the hyperlinks are
still looking in the old location! Does anyone know a way to edit the
hyperlinks 'on mass' as opposed to me editing each link individually??
Thanks

Answer: Mass edit Hyperlinks
 

Yes, there is a way to edit hyperlinks in bulk in Microsoft Excel. Here are the steps:

1. Select all the cells that contain hyperlinks that you want to edit. You can do this by clicking on the first cell and then holding down the Shift key while clicking on the last cell.
2. Right-click on one of the selected cells and choose "Edit Hyperlinks" from the context menu.
3. In the Edit Hyperlink dialog box, you can change the hyperlink address to the new location. You can either type in the new address directly or use the "Browse for File" button to navigate to the new location.
4. Once you have entered the new address, click on the "OK" button to save the changes.
5. Excel will then update all the selected hyperlinks with the new address.

Note: If you have a lot of hyperlinks to edit, you may want to consider using the "Find and Replace" feature in Excel. This will allow you to search for the old address and replace it with the new one in one go. To do this, press Ctrl + H on your keyboard to open the Find and Replace dialog box, enter the old address in the "Find what" field and the new address in the "Replace with" field, and then click on the "Replace All" button.

Mass edit Hyperlinks
 

Try this:

Sub ReplaceHyperlinksInActiveWorkbook()
Dim oSheet As Object
Dim H As Hyperlink
Dim stFind As String
Dim stReplace As String
stFind = InputBox("What is the initial path to replace?", , "\\Old\")
If stFind = "" Then Exit Sub
stReplace = InputBox("What should the path become?", , "\\New\")
If stReplace = "" Then Exit Sub
For Each oSheet In ActiveWorkbook.Sheets
For Each H In oSheet.Hyperlinks
If InStr(H.Address, stFind) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

I created a VBA routine with this, Bill, and then assigned it to a button.
It is not working for me. the routine runs correctly, but the hyperlinks
aren't updating. The files are on our business network, so I am wondering
if that has anything to do with it??


"Bill Manville" wrote in message
...
Try this:

Sub ReplaceHyperlinksInActiveWorkbook()
Dim oSheet As Object
Dim H As Hyperlink
Dim stFind As String
Dim stReplace As String
stFind = InputBox("What is the initial path to replace?", , "\\Old\")
If stFind = "" Then Exit Sub
stReplace = InputBox("What should the path become?", , "\\New\")
If stReplace = "" Then Exit Sub
For Each oSheet In ActiveWorkbook.Sheets
For Each H In oSheet.Hyperlinks
If InStr(H.Address, stFind) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

TR Young wrote:
the routine runs correctly, but the hyperlinks
aren't updating

Presumably the stFind you specified is not matching the start of the
hyperlink addresses you wanted to change. Could be a case-sensitivity
issue. Try:
If InStr(LCase(H.Address), LCase(stFind)) = 1 Then

If that still doesn't do it, get it to tell you what the Address is
that it is finding:

For Each H In oSheet.Hyperlinks
Debug.Print H.Address
If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next

I assume you are remembering to save the modified workbook having made
the changes<g.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

Where do I learn this secret language? (Dim, etc)?

"Bill Manville" wrote:

Try this:

Sub ReplaceHyperlinksInActiveWorkbook()
Dim oSheet As Object
Dim H As Hyperlink
Dim stFind As String
Dim stReplace As String
stFind = InputBox("What is the initial path to replace?", , "\\Old\")
If stFind = "" Then Exit Sub
stReplace = InputBox("What should the path become?", , "\\New\")
If stReplace = "" Then Exit Sub
For Each oSheet In ActiveWorkbook.Sheets
For Each H In oSheet.Hyperlinks
If InStr(H.Address, stFind) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

Rebecca sage wrote:
Where do I learn this secret language? (Dim, etc)?

No great secret.
The language is Visual Basic for Applications (VBA).

A good book to try for starters would be Excel NNNN Visual Basic for
Applications Step by Step.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

Thanks, Bill. Unfortunately I need to resolve this issue sooner than later. I
was wondering if I could get some help not using VBA?
I need to edit hyperlinks en masse as well. Can I do that using windows
dialogue boxes in Excel?
thanks!
Rebecca

"Bill Manville" wrote:

TR Young wrote:
the routine runs correctly, but the hyperlinks
aren't updating

Presumably the stFind you specified is not matching the start of the
hyperlink addresses you wanted to change. Could be a case-sensitivity
issue. Try:
If InStr(LCase(H.Address), LCase(stFind)) = 1 Then

If that still doesn't do it, get it to tell you what the Address is
that it is finding:

For Each H In oSheet.Hyperlinks
Debug.Print H.Address
If InStr(LCase(H.Address), LCase(stFind)) = 1 Then
H.Address = stReplace & Mid(H.Address, Len(stFind) + 1)
End If
Next

I assume you are remembering to save the modified workbook having made
the changes<g.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Mass edit Hyperlinks
 

Rebecca wrote:
I
was wondering if I could get some help not using VBA?
I need to edit hyperlinks en masse as well. Can I do that using windows
dialogue boxes in Excel?

Excel does not provide help for mass edits of hyperlink addresses.
You will either have to use a macro similar to the one I posted on each
workbook whose hyperlinks you need to change, or you could email me at
Bill underscore Manville at Compuserve dot com for a free copy of
LinkManager, a utility which will do this and more.

To run the macro is quite simple;
Start Excel
File New
Alt+F11 to the visual basic editor
Insert Module
Paste the code into the big white space that appears
Alt+F11 back to Excel
Open the workbook containing the links
Tools Macro Macros (select the only macro) Run

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup