Code to remove external references from range names
 

Just an offering to the world...a while back I had to remove all the
external references from all my range names in the active workbook, which is
something Name Manager does not do. This code worked for me, but I make no
guarantees that it will do anything for anyone else.

Bruce Cooley


-------------------------------
Sub InternalNames()

Dim oName As Name
Dim strName As String
Dim strOldName As String ' Original Name
Dim strExRef As String ' External Reference
Dim strNewName As String ' New Name
Dim intExRefStart As Integer ' position of [
Dim intExRefEnd As Integer ' position of ]
Dim intExRefLength As Integer ' length of External Reference

For Each oName In ActiveWorkbook.Names

' OFFSET FORMULAS SOMETIMES HAVE MULTIPLE EXTERNAL REFERENCES...IN THE
MIDDLE!
' =OFFSET('*:\*\[*.xls]*'!$A$21,0,'*:\*\[*.xls]*'!$CZ$1,'*'!COUNT,1)
'
=OFFSET('*:\*\[*.xls]*'!$F$94,0,0,COUNTIF('*:\*\[*.xls]*'!$G$94:$G$105,"0"))

If InStr(1, oName.RefersTo, ":\", vbTextCompare) 0 Or InStr(1,
oName.RefersTo, "[", vbTextCompare) 0 Then
Do While InStr(1, oName.RefersTo, "[", vbTextCompare) 0 '
Assuming that where there's a [ there' a :\
' MsgBox "Name: " & oName.Name
strName = oName.Name
' MsgBox "RefersTo: " & oName.RefersTo
strOldName = oName.RefersTo
intExRefStart = InStr(1, oName.RefersTo, ":\", vbTextCompare) -
1 ' Back off to start of path
intExRefEnd = InStr(1, oName.RefersTo, "[", vbTextCompare)
intExRefLength = intExRefEnd - intExRefStart
strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength)
' MsgBox "Deleting: " & strExRef
strNewName = Replace(oName.RefersTo, strExRef, "",
vbTextCompare)
If InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then
strOldName = oName.RefersTo
intExRefStart = InStr(1, oName.RefersTo, "[", vbTextCompare)
intExRefEnd = InStr(1, oName.RefersTo, "]", vbTextCompare) +
1
intExRefLength = intExRefEnd - intExRefStart
strExRef = Mid(oName.RefersTo, intExRefStart,
intExRefLength)
' MsgBox "Deleting: " & strExRef
strNewName = Replace(strNewName, strExRef, "",
vbTextCompare)
ThisWorkbook.Names.Add Name:=strName, RefersTo:=strNewName,
Visible:=True
' MsgBox oName.Name & " Now RefersTo: " & oName.RefersTo
End If
Loop
' MsgBox "Next name"
End If
Next

MsgBox "All External References have been removed. In theory."
Set oName = Nothing
End Sub

Code to remove external references from range names
 

To remove references here is an excellent addin... Fully debugged and tested...

Look at Bill Manville's Find Links...
http://www.oaltd.co.uk/MVP/
--
HTH...

Jim Thomlinson


"Bruce Cooley" wrote:

Just an offering to the world...a while back I had to remove all the
external references from all my range names in the active workbook, which is
something Name Manager does not do. This code worked for me, but I make no
guarantees that it will do anything for anyone else.

Bruce Cooley


-------------------------------
Sub InternalNames()

Dim oName As Name
Dim strName As String
Dim strOldName As String ' Original Name
Dim strExRef As String ' External Reference
Dim strNewName As String ' New Name
Dim intExRefStart As Integer ' position of [
Dim intExRefEnd As Integer ' position of ]
Dim intExRefLength As Integer ' length of External Reference

For Each oName In ActiveWorkbook.Names

' OFFSET FORMULAS SOMETIMES HAVE MULTIPLE EXTERNAL REFERENCES...IN THE
MIDDLE!
' =OFFSET('*:\*\[*.xls]*'!$A$21,0,'*:\*\[*.xls]*'!$CZ$1,'*'!COUNT,1)
'
=OFFSET('*:\*\[*.xls]*'!$F$94,0,0,COUNTIF('*:\*\[*.xls]*'!$G$94:$G$105,"0"))

If InStr(1, oName.RefersTo, ":\", vbTextCompare) 0 Or InStr(1,
oName.RefersTo, "[", vbTextCompare) 0 Then
Do While InStr(1, oName.RefersTo, "[", vbTextCompare) 0 '
Assuming that where there's a [ there' a :\
' MsgBox "Name: " & oName.Name
strName = oName.Name
' MsgBox "RefersTo: " & oName.RefersTo
strOldName = oName.RefersTo
intExRefStart = InStr(1, oName.RefersTo, ":\", vbTextCompare) -
1 ' Back off to start of path
intExRefEnd = InStr(1, oName.RefersTo, "[", vbTextCompare)
intExRefLength = intExRefEnd - intExRefStart
strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength)
' MsgBox "Deleting: " & strExRef
strNewName = Replace(oName.RefersTo, strExRef, "",
vbTextCompare)
If InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then
strOldName = oName.RefersTo
intExRefStart = InStr(1, oName.RefersTo, "[", vbTextCompare)
intExRefEnd = InStr(1, oName.RefersTo, "]", vbTextCompare) +
1
intExRefLength = intExRefEnd - intExRefStart
strExRef = Mid(oName.RefersTo, intExRefStart,
intExRefLength)
' MsgBox "Deleting: " & strExRef
strNewName = Replace(strNewName, strExRef, "",
vbTextCompare)
ThisWorkbook.Names.Add Name:=strName, RefersTo:=strNewName,
Visible:=True
' MsgBox oName.Name & " Now RefersTo: " & oName.RefersTo
End If
Loop
' MsgBox "Next name"
End If
Next

MsgBox "All External References have been removed. In theory."
Set oName = Nothing
End Sub