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" wrote:

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

keizi

"Drew Lettington" wrote in
message ...
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

"Die_Another_Day" wrote:

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

"Die_Another_Day" wrote:

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