Invalid Characters in Worksheet Name (International)

[Drew Lettington]

I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(ï¿¥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[Die_Another_Day]

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles

 

[Drew Lettington]

Thanks for the reply but just knowing the proposed worksheet name is invalid
isn't helpful. The code needs to create a valid name. For example, the user
provides a name of abc/def and my code converts it to abc_def. My code needs
to know which characters are invalid so they can be replaced.

- Drew

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles

I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(ï¿¥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[Die_Another_Day]

Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Thanks for the reply but just knowing the proposed worksheet name is invalid
isn't helpful. The code needs to create a valid name. For example, the user
provides a name of abc/def and my code converts it to abc_def. My code needs
to know which characters are invalid so they can be replaced.

- Drew

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles

I have some code that creates a new worksheet from a name provided bya user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(ï¿¥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[Drew Lettington]

Thank you again but as you note this brute-force method is slow, especially
since there could be multiple invalid characters and in my case the Excel
error comes back as an exception passed through COM interops. There's got to
be a better way. Surely at a minimum Microsoft must publish a list of
characters that are invalid in worksheet names.

- Drew

Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Thanks for the reply but just knowing the proposed worksheet name is invalid
isn't helpful. The code needs to create a valid name. For example, the user
provides a name of abc/def and my code converts it to abc_def. My code needs
to know which characters are invalid so they can be replaced.

- Drew

Try setting an object to the worksheet that you want to name, then use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles
Drew Lettington wrote:
I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(ï¿¥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[kounoike]

How about using Application.PathSeparator? This indicates ï¿¥character in the
Japanese environment.

keizi

Thank you again but as you note this brute-force method is slow,
especially
since there could be multiple invalid characters and in my case the Excel
error comes back as an exception passed through COM interops. There's got
to
be a better way. Surely at a minimum Microsoft must publish a list of
characters that are invalid in worksheet names.

- Drew

Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Thanks for the reply but just knowing the proposed worksheet name is
invalid
isn't helpful. The code needs to create a valid name. For example,
the user
provides a name of abc/def and my code converts it to abc_def. My code
needs
to know which characters are invalid so they can be replaced.

- Drew

:

Try setting an object to the worksheet that you want to name, then
use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles
Drew Lettington wrote:
I have some code that creates a new worksheet from a name provided
by a user.
The code checks the user input for invalid characters before
creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running
English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained
the yen
(ï¿¥) character. My code checked for \ which is the directory path
separator
in Japanese and displays as the yen symbol. But since the user's
name had
the actual yen character my code failed to create a valid worksheet
name.

I could modify my code to explicitly check for the yen character
and it
would fix the problem. However, yen character is valid in English
Excel so I
don't want to replace it in that environment. I also can't just
check thread
locale or regional settings or something similar as English Excel
could be
running on a Japanese system.

My question then, is it possible to programatically determine at
runtime
which characters are invalid in worksheet names? For example, is
there an
Excel call that would return a list of invalid characters which I
could then
removed from any proposed worksheet names?

- Drew

 

[Drew Lettington]

That was my first attempt to fix the problem but path separator is actually
'\' on the Japanese system; it just displays as yen. And Japanese Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to Excel.
That is, yen character would be invalid in Japanese Excel even if it was
running on an English system.

- Drew

How about using Application.PathSeparator? This indicates ï¿¥character in the
Japanese environment.

keizi

Thank you again but as you note this brute-force method is slow,
especially
since there could be multiple invalid characters and in my case the Excel
error comes back as an exception passed through COM interops. There's got
to
be a better way. Surely at a minimum Microsoft must publish a list of
characters that are invalid in worksheet names.

- Drew

Well, you can use the same approach to find the invalid character,
however inefficient it may be....
For cnt = 1 to Len(Name)
ws.name = Mid(Name,cnt,1)
if err.Number = 1004 then Exit For
Next
Name = Left(Name,cnt - 1) & "_" & Right(Name,Len(Name) - cnt)

Maybe one of the MVP's will have a better solution but that should get
you going.

Charles

Drew Lettington wrote:
Thanks for the reply but just knowing the proposed worksheet name is
invalid
isn't helpful. The code needs to create a valid name. For example,
the user
provides a name of abc/def and my code converts it to abc_def. My code
needs
to know which characters are invalid so they can be replaced.

- Drew

:

Try setting an object to the worksheet that you want to name, then
use
error checking to test if the name was valid or not. Something like
this:
Sub TestName()
Dim Name As String
Dim ws As Object
Name = InputBox("Test Name")
On Error Resume Next
Set ws = Sheets(2)
ws.Name = Name
If Err.Number = 1004 Then '1004 is the error number returned for
invalid charactor
MsgBox "Invalid Name"
End If
On Error GoTo 0
End Sub

HTH

Charles
Drew Lettington wrote:
I have some code that creates a new worksheet from a name provided
by a user.
The code checks the user input for invalid characters before
creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running
English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained
the yen
(ï¿¥) character. My code checked for \ which is the directory path
separator
in Japanese and displays as the yen symbol. But since the user's
name had
the actual yen character my code failed to create a valid worksheet
name.

I could modify my code to explicitly check for the yen character
and it
would fix the problem. However, yen character is valid in English
Excel so I
don't want to replace it in that environment. I also can't just
check thread
locale or regional settings or something similar as English Excel
could be
running on a Japanese system.

My question then, is it possible to programatically determine at
runtime
which characters are invalid in worksheet names? For example, is
there an
Excel call that would return a list of invalid characters which I
could then
removed from any proposed worksheet names?

- Drew

 

[kounoike]

I'm using Japanese Version, and not having English version. so, i'm not sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

 

[Drew Lettington]

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then the code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (ï¿¥) then it doesn't get replaced and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all of them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and disallow all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "ï¿¥" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (ï¿¥)
are not the same character"
End If
End Sub

I'm using Japanese Version, and not having English version. so, i'm not sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to Excel.
That is, yen character would be invalid in Japanese Excel even if it was
running on an English system.

- Drew

 

[kounoike]

Thank you for your explanation. As your explanation, a yen character (ï¿¥) (i
call this wide yen) and the path separator
character(i call this narrow yen) are not same in Japanese version. but in
Japanese version the test under returns "match"

Sub Test()
If Application.PathSeparator = StrConv("ï¿¥", vbNarrow) Then
MsgBox "match"
Else
MsgBox "not match"
End If
End Sub

so, as for yen character, something like - Replace(StrConv(somesheetname,
vbNarrow), Application.PathSeparator , "_")
will replace yen characters (ï¿¥) in somesheetname with underscore (_) in
"Japanese version".

keizi

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then the
code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (ï¿¥) then it doesn't get replaced
and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese
would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all of
them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and disallow
all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "ï¿¥" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (ï¿¥)
are not the same character"
End If
End Sub

I'm using Japanese Version, and not having English version. so, i'm not
sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese
Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to
Excel.
That is, yen character would be invalid in Japanese Excel even if it
was
running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates ï¿¥character
in
the
Japanese environment.

keizi

 

[NickHK]

AFAIK, Excel does not expose a list, but you can generate a list of invalid
chars for that system, when your WB opens.

Then replace any of those chars with an underscore when renaming sheet.

NickHK

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then the code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (?) then it doesn't get replaced and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all of them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and disallow all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "?" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (?)
are not the same character"
End If
End Sub

I'm using Japanese Version, and not having English version. so, i'm not sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to Excel.
That is, yen character would be invalid in Japanese Excel even if it was
running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates ?character in
the
Japanese environment.

keizi

 

[Drew Lettington]

It sounds like you are on the right track. Narrowing the wide character and
checking for path separator is probably the right way to go.

Since my code is not VB, do you know how to do the equivalent character
narrowing in C#?

Thank you for your explanation. As your explanation, a yen character (ï¿¥) (i
call this wide yen) and the path separator
character(i call this narrow yen) are not same in Japanese version. but in
Japanese version the test under returns "match"

Sub Test()
If Application.PathSeparator = StrConv("ï¿¥", vbNarrow) Then
MsgBox "match"
Else
MsgBox "not match"
End If
End Sub

so, as for yen character, something like - Replace(StrConv(somesheetname,
vbNarrow), Application.PathSeparator , "_")
will replace yen characters (ï¿¥) in somesheetname with underscore (_) in
"Japanese version".

keizi

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then the
code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (ï¿¥) then it doesn't get replaced
and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese
would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all of
them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and disallow
all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "ï¿¥" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (ï¿¥)
are not the same character"
End If
End Sub

I'm using Japanese Version, and not having English version. so, i'm not
sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

message That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese
Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to
Excel.
That is, yen character would be invalid in Japanese Excel even if it
was
running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates ï¿¥character
in
the
Japanese environment.

keizi

 

[Drew Lettington]

Do you have some clever way to generate the list of inavlid characters at
runtime? I can't think of any way that wouldn't be painfully slow.

I could generate the list in advance by brute force but I could only do it
for versions of Excel to which I have access.

- Drew

AFAIK, Excel does not expose a list, but you can generate a list of invalid
chars for that system, when your WB opens.

Then replace any of those chars with an underscore when renaming sheet.

NickHK

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then the code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (?) then it doesn't get replaced and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all of them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and disallow all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "?" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (?)
are not the same character"
End If
End Sub

I'm using Japanese Version, and not having English version. so, i'm not sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

message That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to Excel.
That is, yen character would be invalid in Japanese Excel even if it was
running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates ?character in
the
Japanese environment.

keizi

 

[NickHK]

You could get the value of
Application.International (xlCountryCode)

If you have the info that language in your .INI file, use that.
If not, generate a listing and add to your INI file in an initialisation
routine.
That way you don't have to test all characters everytime.

But it seems Keizi knows more about this than me, so that may well be a
suitable route.

NickHK

Do you have some clever way to generate the list of inavlid characters at
runtime? I can't think of any way that wouldn't be painfully slow.

I could generate the list in advance by brute force but I could only do it
for versions of Excel to which I have access.

- Drew

AFAIK, Excel does not expose a list, but you can generate a list of invalid
chars for that system, when your WB opens.

Then replace any of those chars with an underscore when renaming sheet.

NickHK

Thank you for the post. The code you included is very similar to my first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my Japanese
system. So, if the proposed worksheet name contains backslash then

the
code

replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (?) then it doesn't get

replaced
and

the name is invalid.

I could modify my code to explicitly check for yen character; Japanese would
work and English would not be impacted in most cases. But there are other
international versions of Excel and if I want my code to work in all

of
them

I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and

disallow
all

bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are not
the same character:

Sub Test()
If Application.PathSeparator <> "?" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen (?)
are not the same character"
End If
End Sub

:

I'm using Japanese Version, and not having English version. so, i'm

not
sure

whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

message That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to Excel.
That is, yen character would be invalid in Japanese Excel even if

it
was

running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates

?character
in

the
Japanese environment.

keizi

 

[kounoike]

I'm sorry, but i'm quite unfamiliar with C# and don't have C#. so, i have no
idea about narrowing in C#.
i'm not sure, but the document below, thought, it is .NET, might be a help
for you.

http://msdn2.microsoft.com/en-US/library/microsoft.visualbasic.strings.strconv.aspx

keizi

It sounds like you are on the right track. Narrowing the wide character
and
checking for path separator is probably the right way to go.

Since my code is not VB, do you know how to do the equivalent character
narrowing in C#?

Thank you for your explanation. As your explanation, a yen character (ï¿¥)
(i
call this wide yen) and the path separator
character(i call this narrow yen) are not same in Japanese version. but
in
Japanese version the test under returns "match"

Sub Test()
If Application.PathSeparator = StrConv("ï¿¥", vbNarrow) Then
MsgBox "match"
Else
MsgBox "not match"
End If
End Sub

so, as for yen character, something like - Replace(StrConv(somesheetname,
vbNarrow), Application.PathSeparator , "_")
will replace yen characters (ï¿¥) in somesheetname with underscore (_)
in
"Japanese version".

keizi

Thank you for the post. The code you included is very similar to my
first
attempt to solve this problem. But what I found was that the separator
character is backslash (\) even though it displays as yen on my
Japanese
system. So, if the proposed worksheet name contains backslash then the
code
replaces it with underscore (_) and the name is good. However, if the
proposed name contains a yen character (ï¿¥) then it doesn't get
replaced
and
the name is invalid.

I could modify my code to explicitly check for yen character; Japanese
would
work and English would not be impacted in most cases. But there are
other
international versions of Excel and if I want my code to work in all of
them
I need to know what characters they won't accept.

I thought about disallowing currency symbol but dollar ($) is valid in
English Excel. Plus, without knowing what Excel will allow and
disallow
all
bases may still not be covered.

By the way, here's a macro you can run to see if separator and yen are
not
the same character:

Sub Test()
If Application.PathSeparator <> "ï¿¥" Then
MsgBox "Separator (" + Application.PathSeparator + ") and yen
(ï¿¥)
are not the same character"
End If
End Sub

:

I'm using Japanese Version, and not having English version. so, i'm
not
sure
whether the code of yen character is some or not in both version. but
somthing like this seems to work for me in my Japanese version, though
i
think StrConv(nm, vbNarrow) is not valid in English version.

Function makeshname(ByVal nm As String)
Dim nlist
Dim i As Long
nlist = Array(" ", "[", "]", ":", "*", "/", "?",
Application.PathSeparator)
For i = 0 To UBound(nlist)
nm = Replace(StrConv(nm, vbNarrow), nlist(i), "_")
Next
makeshname = nm
End Function

keizi

message That was my first attempt to fix the problem but path separator is
actually
'\' on the Japanese system; it just displays as yen. And Japanese
Excel
doesn't allow either backslash or yen characters in worksheet names.

Also I'm pretty sure that the invalid characters are 'built-in' to
Excel.
That is, yen character would be invalid in Japanese Excel even if it
was
running on an English system.

- Drew

:

How about using Application.PathSeparator? This indicates ï¿¥character
in
the
Japanese environment.

keizi

 

[Drew Lettington]

Thank you to everyone who replied. For your information, here is the final
C# code I added to my application. It uses Microsoft.VisualBasic to access
the VB string conversion code to 'narrow' the wide yen character. This works
for Japanese and I assume will work for all Asian languages but I can't test
since I don't have access to those Office versions.

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[', ']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on sheet names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen
-truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in worksheet names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars, replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName, replace);
break;
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(ï¿¥) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[kounoike]

Hi Drew

I don't have C#, so i can't test your code. but your code seems like to
check only path separator(yen charactor in Japanese) wide or not. but
besides yen charactor, Japanese version has a wide charactor to each {':',
'/', '?', '*', '[', ']'} and these wide charactors are also not valid in
sheet name in Japanese version.

keizi

Thank you to everyone who replied. For your information, here is the
final
C# code I added to my application. It uses Microsoft.VisualBasic to
access
the VB string conversion code to 'narrow' the wide yen character. This
works
for Japanese and I assume will work for all Asian languages but I can't
test
since I don't have access to those Office versions.

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[',
']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on sheet names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen
-truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in worksheet names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars, replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName, replace);
break;
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

I have some code that creates a new worksheet from a name provided by a
user.
The code checks the user input for invalid characters before creating
the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the
yen
(ï¿¥) character. My code checked for \ which is the directory path
separator
in Japanese and displays as the yen symbol. But since the user's name
had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel
so I
don't want to replace it in that environment. I also can't just check
thread
locale or regional settings or something similar as English Excel could
be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there
an
Excel call that would return a list of invalid characters which I could
then
removed from any proposed worksheet names?

- Drew

 

[NickHK]

I don't have C#, but if you can make a DLL callable from VB, I can test on
Chinese Windows/Office.

NickHK

Thank you to everyone who replied. For your information, here is the final
C# code I added to my application. It uses Microsoft.VisualBasic to access
the VB string conversion code to 'narrow' the wide yen character. This works
for Japanese and I assume will work for all Asian languages but I can't test
since I don't have access to those Office versions.

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[', ']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on sheet names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen
-truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in worksheet names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars, replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName, replace);
break;
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

I have some code that creates a new worksheet from a name provided by a user.
The code checks the user input for invalid characters before creating the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the yen
(?) character. My code checked for \ which is the directory path separator
in Japanese and displays as the yen symbol. But since the user's name had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel so I
don't want to replace it in that environment. I also can't just check thread
locale or regional settings or something similar as English Excel could be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there an
Excel call that would return a list of invalid characters which I could then
removed from any proposed worksheet names?

- Drew

 

[Drew Lettington]

Here's the updated code that checks for the wide versions of all invalid
worksheet characters:

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[', ']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on sheet names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen -
truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in worksheet names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars, replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

// If narrowing changed something, look for the narrowed invalid
characters
if (narrowSheetName.Length > 0 && narrowSheetName != validSheetName)
{
for (int i = 0; i < invalidChars.Length; i++)
{
int invalidIndex = narrowSheetName.IndexOf(invalidChars);

if (invalidIndex >= 0)
{
// Found a wide version of the invalid character,
replace them all
validSheetName =
validSheetName.Replace(validSheetName[invalidIndex], replace);
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

Hi Drew

I don't have C#, so i can't test your code. but your code seems like to
check only path separator(yen charactor in Japanese) wide or not. but
besides yen charactor, Japanese version has a wide charactor to each {':',
'/', '?', '*', '[', ']'} and these wide charactors are also not valid in
sheet name in Japanese version.

keizi

Thank you to everyone who replied. For your information, here is the
final
C# code I added to my application. It uses Microsoft.VisualBasic to
access
the VB string conversion code to 'narrow' the wide yen character. This
works
for Japanese and I assume will work for all Asian languages but I can't
test
since I don't have access to those Office versions.

private string ValidSheetName(string sheetName)
{
string validSheetName = String.Empty;
char [] invalidChars = new char [] {':', '\\', '/', '?', '*', '[',
']'};
const string truncate = "...";
const char replace = '_';
const int maxSheetNameLen = 31;

if (sheetName.Length > maxSheetNameLen)
{
// Character limit on sheet names would be exceeded, truncate
validSheetName = sheetName.Substring(0, maxSheetNameLen
-truncate.Length) + truncate;
}
else
{
validSheetName = sheetName;
}

// Some characters are invalid in worksheet names in all locales
for (int i = 0; i < invalidChars.Length; i++)
{
validSheetName = validSheetName.Replace(invalidChars, replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetName,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCulture.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName, replace);
break;
}
}
}
}
catch
{
// Narrowing doesn't apply to all locales, nothing to do
}

// Check for blank name
if (validSheetName.Trim().Length == 0)
{
validSheetName = replace.ToString();
}

return validSheetName;
}

I have some code that creates a new worksheet from a name provided by a
user.
The code checks the user input for invalid characters before creating
the
name, eliminating : @ \ / ? * [ ]. The code worked fine running English
Excel but when I ran using Japanese Excel I had a problem.

In the Japanese environment the user provided a name that contained the
yen
(ï¿¥) character. My code checked for \ which is the directory path
separator
in Japanese and displays as the yen symbol. But since the user's name
had
the actual yen character my code failed to create a valid worksheet name.

I could modify my code to explicitly check for the yen character and it
would fix the problem. However, yen character is valid in English Excel
so I
don't want to replace it in that environment. I also can't just check
thread
locale or regional settings or something similar as English Excel could
be
running on a Japanese system.

My question then, is it possible to programatically determine at runtime
which characters are invalid in worksheet names? For example, is there
an
Excel call that would return a list of invalid characters which I could
then
removed from any proposed worksheet names?

- Drew