Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Yes, there is a way to edit hyperlinks in bulk in Microsoft Excel. Here are the steps:
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.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#8
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#9
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can't edit hyperlinks | Excel Discussion (Misc queries) | |||
Can't edit hyperlinks | Excel Discussion (Misc queries) | |||
Can't edit hyperlinks | Excel Discussion (Misc queries) | |||
Edit Hyperlinks | Excel Discussion (Misc queries) | |||
Edit Hyperlinks | Excel Worksheet Functions |