Converting External Links to Relative Reference

RustEE2020

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
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?
VBA Code:
For Each WS In ActiveWorkbook.Worksheets
WS.Cells.Replace what:=ThisWorkbook.Name, Replacement:=ActiveWorkbook.Name
Next
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Could be some missing arguments are causing this by default. Try this- adjust the LookAt and MatchCase arguments to suit.

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

RustEE2020

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
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.
VBA Code:
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

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
I believe I have this problem solved with the following code:


VBA 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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,336
Messages
5,624,093
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top