#1
|
|||
|
|||
Trying to create a macro to batch edit hyperlinks
Hi. I am no VBA expert, so have had to rely on what I have found by Googling. But I am still getting errors. Can anyone kindly help?
Macros are enabled and the file saved with an xlsm extension. I first created a new module with the script A, below. Then I created my macro, using a second module window (Alt+F11) which after saving looks like text B: but when I run it I get 'Compile error. Invalid Outside Procedure' Can anyone advise what is wrong? Many thanks! The original is here (and I replied, but it's an old thread so am probably not going to get answers by this stage): http://superuser.com/questions/65735...in-excel-cells Martin A: Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL, PreStr, PostStr, NewURL As String Dim FindPos, ReplaceLen, URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub B: Call ReplaceHyperlinkURL("W:\", "E:\My Documents\") Sub ReplaceHyperlinkURL() End Sub |
#2
|
|||
|
|||
Ok First and foremost we have to make your code readable. I have done this below.
The issue you were most likely experiencing was the duplicate sub names. I have adjusted this below as well. I would be surprised if this discovery fixes the issue but if it does not post a sample workbook with the desired result and I will be happy to figure this out for you. Properly formatted code below. Code:
Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL, PreStr, PostStr, NewURL As String Dim FindPos, ReplaceLen, URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub Sub RunReplaceURL() Call ReplaceHyperlinkURL("W:\", "E:\My Documents\") End Sub |
#3
|
||||
|
||||
The basic process works as intended here. I agree that if the code is an accurate representation, the problem is
Call ReplaceHyperlinkURL("W:\", "E:\My Documents\") Sub ReplaceHyperlinkURL() End Sub You are using a sub with the same name as the main sub to call it and yet the call is outside the second sub. Code:
Sub RunReplaceURL() Call ReplaceHyperlinkURL("W:\", "E:\My Documents\") End Sub Note that the process is case sensitive, and while it is unlikely that your hyperlinks contain 'w:\' in lower case, it is not impossible and it won't work for those links. In which case either correct for case in the main sub or call it a second time with lower case 'w:\'. Use the # button and paste your code between the markers in inserts to keep the format or your code.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#4
|
|||
|
|||
I forgot about the possibility of case sensitivity. Nice find GMayor. Thanks
|
#5
|
|||
|
|||
Am just off on holiday for a week this morning - will test and reply when I get back! Thanks for your help.
|
#6
|
|||
|
|||
OK, I adjusted the macro as you said - it now runs and does exactly what was required. The hyperlinks now all work again.
Thank you both very much for helping me with this.. Martin |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Batch edit mail merge macros? | kbash | Word | 0 | 04-30-2014 08:32 AM |
Batch create Word documents | cdfj | Word VBA | 6 | 11-07-2012 01:03 PM |
Batch Edit Links | tosti | PowerPoint | 5 | 01-31-2012 12:51 PM |
Using hyperlinks to create interactive menus | hongkongphooey | PowerPoint | 0 | 01-25-2011 07:30 AM |
Create Hyperlinks from Word to specific location in PDF | sukanyae | Word | 0 | 02-25-2010 04:08 PM |