If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 changesg. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 changesg. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#8
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|