Out of Memory Error 7 on text replace

[Candyman8019]

I have a workbook with about 40 sheets. Many of them contain anywhere from 2 to 15 hyperlinks (not hyperlink formulas). We are migrating the files to a new sharepoint site and I need to update all of the hyperlinks. I have a script that will do a REPLACE, so I can specify the starting portion of the URL and change it to the new URL. At one time this script worked for me, but now I'm getting an out of memory error 7 on this line:

hlink.Address = Replace(hlink.Address, oldtxt, newtxt, Compare:=vbTextCompare)

Complete script is as follows:

Sub UpdateLinks()

    Dim hlink As Hyperlink
    Dim ws As Worksheet
    Dim oldtxt As String
    Dim newtxt As String
    
    oldtxt = Sheet253.Range("OLDTXT")
    newtxt = Sheet253.Range("NEWTXT")
    
    For Each ws In ThisWorkbook.Worksheets
        
        For Each hlink In ws.Hyperlinks
            If InStr(1, hlink.Address, oldtxt) > 0 Then
                hlink.Address = Replace(hlink.Address, oldtxt, newtxt, Compare:=vbTextCompare)
            End If
        Next hlink
      
    Next ws
End Sub

Any ideas would be appreciated.

 

[Rijnsent]

What are the values of the variables, most probably there is an error in there? As in: hlink.Address, oldtxt, newtxt ? You could Debug.Print the variables just before so you can check them, most probably there's an error in there.

 

[Candyman8019]

The variables are simply for text replacements in hyperlinks. For example:

replace this:
../../../../..//r/personal/jrie/Documents/Working Docs/Network Sustainment/

with this

https://xxxxxx.sharepoint.com/❌/r/s...s/Location Info/Network Sustainment Playbook/

 

[Rijnsent]

Mmm, okay, what about these ideas (found them online):

• Start your code with Application.Screenupdating = False and Application.Calculation = xlCalculationManual at set them back at the end?
• Check if your hyperlink won't get too long (nr of characters, don't know what the Excel limitation is, maybe 255 characters)
• Check before you run it that you have only one instance of Excel running ( VBA Out of Memory Error - Automate Excel )
• Allocate more memory to Excel - Fix Runtime Error 7 Out Of Memory - Excel Macro

 

[Candyman8019]

I have tried the screenupdating and calculation previously with no success. Interesting about the 255 characters...there may be something to that. I do have at least one URL (and most likely more) that ends up being 285 characters; however, opening the link in SharePoint works fine, and also manually creating the hyperlink with CTRL-K works.

It turns out there were two hyperlinks it was failing on. As soon as I removed those, it worked flawlessly. Those two links were over 255 characters. Is there a way around that limitation since they are valid links that work in SharePoint?

 

[Rijnsent]

I think you'd have to change it to a HYPERLINK formula, like this thread: How do I do a hyperlink that exceeds 255 characters?

 

[Candyman8019]

Thanks Rijnsent.