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

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