Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 "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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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#? "kounoike" wrote: 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 "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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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/lib...s.strconv.aspx keizi "Drew Lettington" wrote in message ... 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#? "kounoike" wrote: 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 "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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Invalid Characters in Worksheet Name (International)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting international characters in English in Excel 2003 | Excel Discussion (Misc queries) | |||
Formula to replace invalid filename characters | Excel Worksheet Functions | |||
List of invalid sheet tab characters? | Excel Programming | |||
Removing invalid characters from proposed sheet name | Excel Programming | |||
Invalid characters in Excel | Excel Programming |