Hi
I'm using Excel 2003 and would like to know if the following is possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3 and
now contain loads of defined names! I can remove most of the names but some
are a bit more tricky as they contain characters such as %, $, & and even
squares.

Reading through past threads I have figured out that by switching to R1C1
notation through the Tools, Options, General tab I can rename the invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and then
the Name Conflict dialog box is displayed and I'm able to enter new names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

Taking a slightly different approach, you can rename the 'bad' names using
the following macro (ChangeRangeNames) that uses the user-defined function
(CheckForLegalAnsi).


'/=============================================/
Sub ChangeRangeNames()
'change 'illegal' range names to 'legal' ones
Dim nRng As Name

On Error Resume Next

For Each nRng In ActiveWorkbook.Names
nRng.Name = CheckForLegalAnsi(nRng.Name)
Debug.Print nRng.Name
Next nRng

End Sub

'/=============================================/
Private Function CheckForLegalAnsi(strCheckName As String) _
As String
'strip out 'bad' characters from range names
Dim i As Integer, iTest As Integer, iChecker As Integer
Dim strBuildLegalName As String

If Len(strCheckName) = 0 Then
CheckForLegalAnsi = "Unknown"
Exit Function
End If

If Asc(Left(strCheckName, 1)) = 48 And _
Asc(Left(strCheckName, 1)) <= 57 Then
strCheckName = "A_" & strCheckName
End If

strBuildLegalName = ""

'Test for illegal DOS characters in name
For i = 1 To Len(strCheckName)
iTest = 0
iChecker = Asc(Mid(strCheckName, i, 1))
If iChecker = 48 Then
If iChecker <= 57 Then
iTest = 1
Else
If iChecker = 65 Then
If iChecker <= 90 Then
iTest = 1
Else
If iChecker = 95 Then
iTest = 1
Else
If iChecker = 97 Then
If iChecker <= 122 Then
iTest = 1
End If
End If
End If
End If
End If
End If
End If
If iTest = 0 Then
If i = 1 Then
strBuildLegalName = strBuildLegalName & "a_"
Else
strBuildLegalName = strBuildLegalName & "_"
End If
Else
strBuildLegalName = strBuildLegalName & _
Mid(strCheckName, i, 1)
End If
Next i

CheckForLegalAnsi = strBuildLegalName
' i = i
End Function
'/=============================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3 and
now contain loads of defined names! I can remove most of the names but some
are a bit more tricky as they contain characters such as %, $, & and even
squares.

Reading through past threads I have figured out that by switching to R1C1
notation through the Tools, Options, General tab I can rename the invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and then
the Name Conflict dialog box is displayed and I'm able to enter new names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

If you really want to rename the ranges 'Junk', try this...

'/=========================================/
Sub ChangeRangeNames()
'change 'illegal' range names to 'legal' ones
Dim iJunk As Integer
Dim nRng As Name

On Error Resume Next

For Each nRng In ActiveWorkbook.Names
If CheckForLegalAnsi(nRng.Name) < "OK" Then
nRng.Name = "Junk" & Text(iJunk, "_000")
iJunk = 1 + iJunk
End If
Next nRng

End Sub

'/=========================================/
Private Function CheckForLegalAnsi(strCheckName As String) _
As String
'rename 'bad' range names to 'Junk'
Dim i As Integer, iTest As Integer, iChecker As Integer

On Error Resume Next

CheckForLegalAnsi = ""

If Asc(Left(strCheckName, 1)) = 48 And _
Asc(Left(strCheckName, 1)) <= 57 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If

'Test for illegal DOS characters in name
For i = 1 To Len(strCheckName)
iTest = 0
iChecker = Asc(Mid(strCheckName, i, 1))
If iChecker = 48 Then
If iChecker <= 57 Then
iTest = 1
Else
If iChecker = 65 Then
If iChecker <= 90 Then
iTest = 1
Else
If iChecker = 95 Then
iTest = 1
Else
If iChecker = 97 Then
If iChecker <= 122 Then
iTest = 1
End If
End If
End If
End If
End If
End If
End If
If iTest = 0 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If
Next i

CheckForLegalAnsi = "OK"

End Function
'/=========================================/


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3 and
now contain loads of defined names! I can remove most of the names but some
are a bit more tricky as they contain characters such as %, $, & and even
squares.

Reading through past threads I have figured out that by switching to R1C1
notation through the Tools, Options, General tab I can rename the invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and then
the Name Conflict dialog box is displayed and I'm able to enter new names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

Hi Gary

Many thanks for your help - it's really appreciated.

I've tried both of the routines and am having problems. The routines create
new names but do not delete the invalid ones - is it possible to delete the
invalid names?

TIA
Preeti


"Gary L Brown" wrote in message
...
If you really want to rename the ranges 'Junk', try this...

'/=========================================/
Sub ChangeRangeNames()
'change 'illegal' range names to 'legal' ones
Dim iJunk As Integer
Dim nRng As Name

On Error Resume Next

For Each nRng In ActiveWorkbook.Names
If CheckForLegalAnsi(nRng.Name) < "OK" Then
nRng.Name = "Junk" & Text(iJunk, "_000")
iJunk = 1 + iJunk
End If
Next nRng

End Sub

'/=========================================/
Private Function CheckForLegalAnsi(strCheckName As String) _
As String
'rename 'bad' range names to 'Junk'
Dim i As Integer, iTest As Integer, iChecker As Integer

On Error Resume Next

CheckForLegalAnsi = ""

If Asc(Left(strCheckName, 1)) = 48 And _
Asc(Left(strCheckName, 1)) <= 57 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If

'Test for illegal DOS characters in name
For i = 1 To Len(strCheckName)
iTest = 0
iChecker = Asc(Mid(strCheckName, i, 1))
If iChecker = 48 Then
If iChecker <= 57 Then
iTest = 1
Else
If iChecker = 65 Then
If iChecker <= 90 Then
iTest = 1
Else
If iChecker = 95 Then
iTest = 1
Else
If iChecker = 97 Then
If iChecker <= 122 Then
iTest = 1
End If
End If
End If
End If
End If
End If
End If
If iTest = 0 Then
CheckForLegalAnsi = "NoGood"
Exit Function
End If
Next i

CheckForLegalAnsi = "OK"

End Function
'/=========================================/


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was
this
Post Helpfull to you?''.


"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3
and
now contain loads of defined names! I can remove most of the names but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for those invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3 and
now contain loads of defined names! I can remove most of the names but some
are a bit more tricky as they contain characters such as %, $, & and even
squares.

Reading through past threads I have figured out that by switching to R1C1
notation through the Tools, Options, General tab I can rename the invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and then
the Name Conflict dialog box is displayed and I'm able to enter new names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

Hi Dave

Thanks for the link. My query is it possible to automate the manual method
you've stated?

Regards
Preeti

"Dave Peterson" wrote in message
...
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for those
invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3
and
now contain loads of defined names! I can remove most of the names but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

Depends on what you mean by automate.

Jan Karel kind of automates it in his addin--but the user still needs to respond
to the dialog.

If you mean that, then you could do the same kind of thing--toggle that setting
and have the user ready to answer the prompt.



"P. Dua-Brown" wrote:

Hi Dave

Thanks for the link. My query is it possible to automate the manual method
you've stated?

Regards
Preeti

"Dave Peterson" wrote in message
...
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for those
invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus 1-2-3
and
now contain loads of defined names! I can remove most of the names but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to do is
automate the renaming of the invalid names in the Name Conflict dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

--

Dave Peterson

What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?


"Dave Peterson" wrote in message
...
Depends on what you mean by automate.

Jan Karel kind of automates it in his addin--but the user still needs to
respond
to the dialog.

If you mean that, then you could do the same kind of thing--toggle that
setting
and have the user ready to answer the prompt.



"P. Dua-Brown" wrote:

Hi Dave

Thanks for the link. My query is it possible to automate the manual
method
you've stated?

Regards
Preeti

"Dave Peterson" wrote in message
...
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for
those
invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus
1-2-3
and
now contain loads of defined names! I can remove most of the names
but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over
and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to
do is
automate the renaming of the invalid names in the Name Conflict
dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

--

Dave Peterson

Hi,

I do have a utility that is able to do batch fixing of corrupt names.
Send me an email if you want it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

P. Dua-Brown wrote:
What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?


"Dave Peterson" wrote in message
...
Depends on what you mean by automate.

Jan Karel kind of automates it in his addin--but the user still needs to
respond
to the dialog.

If you mean that, then you could do the same kind of thing--toggle that
setting
and have the user ready to answer the prompt.



"P. Dua-Brown" wrote:

Hi Dave

Thanks for the link. My query is it possible to automate the manual
method
you've stated?

Regards
Preeti

"Dave Peterson" wrote in message
...
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for
those
invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus
1-2-3
and
now contain loads of defined names! I can remove most of the names
but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over
and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to
do is
automate the renaming of the invalid names in the Name Conflict
dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

--

Dave Peterson

Hi,

I do have a utility that is able to do batch fixing of corrupt names.
Send me an email if you want it.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

P. Dua-Brown wrote:
What I'd like to do automate the renaming process. Let say I have 32 invalid
names is there a way to use vba to rename each of the invalid names to new
valid names let's say Junk1, Junk2 ... Junk32?


"Dave Peterson" wrote in message
...
Depends on what you mean by automate.

Jan Karel kind of automates it in his addin--but the user still needs to
respond
to the dialog.

If you mean that, then you could do the same kind of thing--toggle that
setting
and have the user ready to answer the prompt.



"P. Dua-Brown" wrote:

Hi Dave

Thanks for the link. My query is it possible to automate the manual
method
you've stated?

Regards
Preeti

"Dave Peterson" wrote in message
...
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager has
the ability to fix these invalid names.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can also do it manually.

Tools|options|General|check r1c1 reference style
then
Tools|options|General|uncheck r1c1 reference style

You'll be prompted at one of these steps to supply a new name for
those
invalid
names. (I forget which one!)

"P. Dua-Brown" wrote:

Hi
I'm using Excel 2003 and would like to know if the following is
possible.

Some of the spreadsheets used in the office started life in Lotus
1-2-3
and
now contain loads of defined names! I can remove most of the names
but
some
are a bit more tricky as they contain characters such as %, $, & and
even
squares.

Reading through past threads I have figured out that by switching to
R1C1
notation through the Tools, Options, General tab I can rename the
invalid
ones. So I use Application.ReferenceStyle = xlR1C1 to switch over
and
then
the Name Conflict dialog box is displayed and I'm able to enter new
names
(one at a time) for each of the invalid ones. What I would like to
do is
automate the renaming of the invalid names in the Name Conflict
dialog
box
to Junk1, Junk2 etc.

Is this possible???

Many thanks
Preeti

--

Dave Peterson

--

Dave Peterson