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

"NickHK" wrote:

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

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

"kounoike" wrote:

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

"kounoike" wrote:

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

keizi