Trying to create a macro to batch edit hyperlinks

martinlest · #1 01-01-2015, 10:08 AM

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

excelledsoftware · #2 01-01-2015, 02:53 PM

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

Thanks

gmayor · #3 01-02-2015, 12:07 AM

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

as suggested will fix it.

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.

excelledsoftware · #4 01-02-2015, 12:43 AM

I forgot about the possibility of case sensitivity. Nice find GMayor. Thanks

martinlest · #5 01-02-2015, 01:24 AM

Am just off on holiday for a week this morning - will test and reply when I get back! Thanks for your help.

martinlest · #6 01-09-2015, 09:34 AM

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

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