K
Kam
I have seen this problem but I have never seen anyone
correctly address it. Now I have finally encountered it.
How do I delete an undefined name in MS Excel? The name
is invalid; it contains undefined characters. I have
posted a typical reply to this problem but again, no one
has been able to solve it.
(followup to private e-mail exchange)
My original response incorrectly assumed that
the "illegal" characters were
in the RefersTo reference, not the Name reference itself.
Bad assumption.
Jeff:
1) After working with your actual file, I don't think its
possible to delete
defined names containing illegal characters. (It may be
that only one of
the two illegal characters, Char(21) & Char(0), in your
names are actually
causing the problem, but I don't know of a way to test
that possibility.)
Two free addins that I checked won't touch them either:
Microsoft's Delete Links Wizard
(http://support.microsoft.com/support/kb/articles/Q188/4/49
..asp)
and John Walkenbach's LinkLister
(http://www.j-walk.com/ss/excel/files/namelist.htm)
I know there's at least one other add-in/wizard out there
(Bill
Manville's??), but I didn't get a chance to check it.
2) The offending names also resist having their RefersTo
properties changed
via VBA.
However, what you CAN do is manually re-direct the source
reference to the
current workbook so those links will no longer cause
the "Update Link?"
prompt when opening the workbook: Edit>Links;
ChangeSource; Browse to
current workbook; OK; (Save workbook).
The bad names will still be in the workbook (with bad
references as it
happens), but at least they won't trigger that prompt
anymore.
3) I've adjusted my code so that it doesn't attempt to
delete names with
illegal characters, but will otherwise delete any name
that contains #REF in
its RefersTo. Now that I've tried them, I can also
recommend either of the
two add-ins above as being a lot more flexible. MS's
allows you to select
multiple files for clean-up, and also provides a record of
what got deleted.
J.Walkenbach's has a simple UI that allows you to select
specific types of
links to review & optionally delete (and it stays open
until you close it,
unlike Excel's Insert>Name>Define dialog).
Sorry I couldn't be of more help.
George
(revised Code follows)
Sub delete_REF_links()
Dim defined_name As Name
Dim CharCode As Variant
Dim x As Integer
Dim y As Integer
Dim bolValidName As Boolean
y = 0
CharCode = Array
("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", _
"11", "12", "13", "14", "15", "16", "17", "18", "1
9", "20", _
"21", "22", "23", "24", "25", "26", "27", "28", "2
9", _
"30", "31", "127", "128", "129", "141", "142", _
"143", "144", "157", "158")
For Each defined_name In ActiveWorkbook.Names
bolValidName = True
For x = LBound(CharCode) To UBound(CharCode)
' Check defined_name for illegal characters
If InStr(defined_name.Name, Chr(CharCode(x)))
' attempting to delete will cause an IPF.
bolValidName = False
Exit For ' Skip To next defined_name
End If
Next x
'Check RefersTo if name is valid
If bolValidName And _
InStr(defined_name.RefersTo, "#REF") > 0 Then
' Delete Bad reference
defined_name.Delete
y = y + 1
End If
Next defined_name
If y = 0 Then
MsgBox "No legal defined names with bad references
were found."
Else
MsgBox y & " defined names deleted."
End If
End Sub
Jeff Lefebvre wrote in message
correctly address it. Now I have finally encountered it.
How do I delete an undefined name in MS Excel? The name
is invalid; it contains undefined characters. I have
posted a typical reply to this problem but again, no one
has been able to solve it.
(followup to private e-mail exchange)
My original response incorrectly assumed that
the "illegal" characters were
in the RefersTo reference, not the Name reference itself.
Bad assumption.
Jeff:
1) After working with your actual file, I don't think its
possible to delete
defined names containing illegal characters. (It may be
that only one of
the two illegal characters, Char(21) & Char(0), in your
names are actually
causing the problem, but I don't know of a way to test
that possibility.)
Two free addins that I checked won't touch them either:
Microsoft's Delete Links Wizard
(http://support.microsoft.com/support/kb/articles/Q188/4/49
..asp)
and John Walkenbach's LinkLister
(http://www.j-walk.com/ss/excel/files/namelist.htm)
I know there's at least one other add-in/wizard out there
(Bill
Manville's??), but I didn't get a chance to check it.
2) The offending names also resist having their RefersTo
properties changed
via VBA.
However, what you CAN do is manually re-direct the source
reference to the
current workbook so those links will no longer cause
the "Update Link?"
prompt when opening the workbook: Edit>Links;
ChangeSource; Browse to
current workbook; OK; (Save workbook).
The bad names will still be in the workbook (with bad
references as it
happens), but at least they won't trigger that prompt
anymore.
3) I've adjusted my code so that it doesn't attempt to
delete names with
illegal characters, but will otherwise delete any name
that contains #REF in
its RefersTo. Now that I've tried them, I can also
recommend either of the
two add-ins above as being a lot more flexible. MS's
allows you to select
multiple files for clean-up, and also provides a record of
what got deleted.
J.Walkenbach's has a simple UI that allows you to select
specific types of
links to review & optionally delete (and it stays open
until you close it,
unlike Excel's Insert>Name>Define dialog).
Sorry I couldn't be of more help.
George
(revised Code follows)
Sub delete_REF_links()
Dim defined_name As Name
Dim CharCode As Variant
Dim x As Integer
Dim y As Integer
Dim bolValidName As Boolean
y = 0
CharCode = Array
("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", _
"11", "12", "13", "14", "15", "16", "17", "18", "1
9", "20", _
"21", "22", "23", "24", "25", "26", "27", "28", "2
9", _
"30", "31", "127", "128", "129", "141", "142", _
"143", "144", "157", "158")
For Each defined_name In ActiveWorkbook.Names
bolValidName = True
For x = LBound(CharCode) To UBound(CharCode)
' Check defined_name for illegal characters
If InStr(defined_name.Name, Chr(CharCode(x)))
' Skip names with illegal characters since
' attempting to delete will cause an IPF.
bolValidName = False
Exit For ' Skip To next defined_name
End If
Next x
'Check RefersTo if name is valid
If bolValidName And _
InStr(defined_name.RefersTo, "#REF") > 0 Then
' Delete Bad reference
defined_name.Delete
y = y + 1
End If
Next defined_name
If y = 0 Then
MsgBox "No legal defined names with bad references
were found."
Else
MsgBox y & " defined names deleted."
End If
End Sub
Jeff Lefebvre wrote in message
defined_name.Delete fails and I
