Converting External Links to Relative Reference

[RustEE2020]

Hello,
I have an addon that copies template pages from itself into a user specified workbook when requested.
I have been using the code below to replace the external link portion of references with the name of the workbook where the page is copied.
However there is one page where only some of the references are converted.
I receive no errors so I am unsure as to why the code does not replace everything.
Is there a different method that I should be using?

For Each WS In ActiveWorkbook.Worksheets
WS.Cells.Replace what:=ThisWorkbook.Name, Replacement:=ActiveWorkbook.Name
Next

 

[JoeMo]

Could be some missing arguments are causing this by default. Try this- adjust the LookAt and MatchCase arguments to suit.

For Each WS In ActiveWorkbook.Worksheets
    WS.Cells.Replace what:=ThisWorkbook.Name, Replacement:=ActiveWorkbook.Name,[B] lookat:=xlPart, MatchCase:=False[/B]
Next

 

[RustEE2020]

Joe,
I tried your change in the code, unfortunately I get the same results I was getting originally.
I noticed a change when I went from searching by columns or rows.
Is there some numerical limit to how far through a worksheet that this will check?

 

[JoeMo]

Joe,
I tried your change in the code, unfortunately I get the same results I was getting originally.
I noticed a change when I went from searching by columns or rows.
Is there some numerical limit to how far through a worksheet that this will check?

No limit that I know of. In general, it's best to set all the arguments each time you use the Range.Replace method. If you don't, the last saved values are used.

 

[RustEE2020]

No limit that I know of. In general, it's best to set all the arguments each time you use the Range.Replace method. If you don't, the last saved values are used.

The issue seems to be due to creating invalid references when replacing the string, this seems to break the logic at that point however I see no error getting thrown.
I switched the logic over to the following and it seemed to get all the way through the page but left the formulas that would have turned into invalid references alone.

Set Rng = ActiveWorkbook.Worksheets("Billing Sheet").Range("A1:BB50")
For Each RngC In Rng
      If InStr(1, RngC.Formula, ThisWorkbook.Name) Then
            RngC.Replace what:=ThisWorkbook.Name, Replacement:=ActiveWorkbook.Name
      End If
Next

 

[RustEE2020]

I believe I have this problem solved with the following code:

Public Sub Elink_Remover( Optional BreakAll As Boolean)
 
    Dim Ws as Worksheet
    Dim Rng as Range
    Dim RngVal as Variant

20  For Each WS In Activeworkbook.Worksheets
21      For Each Rng In WS.Range("A1:AA50")
22          If BreakAll = True Then GoTo SkipReplace
23          If InStr(1, Rng.Formula, Thisworkbook.name) Then
24              Rng.Replace what:=Thisworkbook.Name, Replacement:=Activeworkbook.Name
25          End If
SkipReplace:
26          If InStr(1, Rng.Formula, ThisWorkbook.Name) Then
27              RngVal = Rng.Value
28              Rng.Formula = ""
29              Rng.Value = RngVal
30          End If
31      Next
32  Next

This code is being run from an addon on a workbook that is actively being used by the user. Therefore I have used "Thisworkbook" to refer to the addon where the code is being run form and "Activeworkbook" to refer to the workbook the user is editing. The code scans through a specific range in every page of a workbook in order to save time instead of checking every cell. It checks each cell in that specific range to see if the formula has a reference to the addon where the page was copied from. If it finds the name of the addon workbook in the formula and the "BreakAll" variable is not set to True then we replace the name of the addon workbook with the activeworkbook. This causes Excel to turn the formula into a relative reference if the page referenced in the formula exists within the activeworkbook, if it does not the formula reverts to its original value. Then we run another check for the name of the addon worksheet in the same range in order to check if the replacement logic worked. If it did not work we buffer the value of the cell, remove the formula, and restore the last known value to the cell.