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

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

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

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

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

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

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[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetNa me,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCultu re.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName[i] == Path.DirectorySeparatorChar)
{
// Found the wide separator, now replace them all
validSheetName =
validSheetName.Replace(validSheetName[i], 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;
}

"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

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

"Drew Lettington" wrote in
message ...[i]
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[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetNa me,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCultu re.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName[i] == 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;
}

"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

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[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetNa me,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCultu re.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[i]);

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;
}

"kounoike" wrote:
[i]
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

"Drew Lettington" wrote in
message ...[i]
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[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetNa me,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCultu re.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;
}

"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

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

NickHK

"Drew Lettington" wrote in
message ...
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 [] {':', '\\', '/', '?', '*', '[',
']'};[i]
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[i], replace);
}

try
{
string narrowSheetName =
Microsoft.VisualBasic.Strings.StrConv(validSheetNa me,
Microsoft.VisualBasic.VbStrConv.Narrow,
System.Threading.Thread.CurrentThread.CurrentCultu re.LCID);

if (narrowSheetName != validSheetName)
{
// Something was narrowed, check for separator again
for (int i = 0; i < narrowSheetName.Length; i++)
{
if (narrowSheetName[i] == 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;
}

"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