excel2003 and unicode

[nodari]

for renaming or moving files I use: name a1 as a2

but I found that if the file name contains unicode letters, it doesn't work, says: bad file name or number.

hope on your help.

 

[Ruddles]

You need to strip away any invalid characters before you do the rename. The following function does that:-

Option Explicit
 
Public Function Sanitise(ByVal InString As String) As String
 
  Dim cPtr As Integer
  Dim sChar As String
  
  Sanitise = ""
  
  For cPtr = 1 To Len(InString)
    sChar = Mid(InString, cPtr, 1)
    Select Case sChar
      Case "[COLOR=red]0[/COLOR]" To "[COLOR=red]9[/COLOR]", "[COLOR=red]a[/COLOR]" To "[COLOR=red]z[/COLOR]", "[COLOR=red]A[/COLOR]" To "[COLOR=red]Z[/COLOR]" [COLOR=green]' normal alphanumerics[/COLOR]
        Sanitise = Sanitise & sChar
      Case "[COLOR=red]([/COLOR]", "[COLOR=red])[/COLOR]", "[COLOR=red]_[/COLOR]", "[COLOR=red]-[/COLOR]", "[COLOR=red][[/COLOR]", "[COLOR=red]][/COLOR]", "[COLOR=red]~[/COLOR]", "[COLOR=red]#[/COLOR]", "[COLOR=red].[/COLOR]" [COLOR=green]' special characters[/COLOR]
        Sanitise = Sanitise & sChar
      Case Else
        [COLOR=green]' character not allowed - do not save
[/COLOR]    End Select
  Next cPtr
 
End Function

I knocked this up on the fly so you'll need to check the 'special characters' line to include characters you want and to exclude the characters you don't want.

Then instead of name a1 as a2 you'd go name a1 as sanitise(a2).

 

[nodari]

You need to strip away any invalid characters before you do the rename. The following function does that:-

Option Explicit
 
Public Function Sanitise(ByVal InString As String) As String
 
  Dim cPtr As Integer
  Dim sChar As String
 
  Sanitise = ""
 
  For cPtr = 1 To Len(InString)
    sChar = Mid(InString, cPtr, 1)
    Select Case sChar
      Case "[COLOR=red]0[/COLOR]" To "[COLOR=red]9[/COLOR]", "[COLOR=red]a[/COLOR]" To "[COLOR=red]z[/COLOR]", "[COLOR=red]A[/COLOR]" To "[COLOR=red]Z[/COLOR]" [COLOR=green]' normal alphanumerics[/COLOR]
        Sanitise = Sanitise & sChar
      Case "[COLOR=red]([/COLOR]", "[COLOR=red])[/COLOR]", "[COLOR=red]_[/COLOR]", "[COLOR=red]-[/COLOR]", "[COLOR=red][[/COLOR]", "[COLOR=red]][/COLOR]", "[COLOR=red]~[/COLOR]", "[COLOR=red]#[/COLOR]", "[COLOR=red].[/COLOR]" [COLOR=green]' special characters[/COLOR]
        Sanitise = Sanitise & sChar
      Case Else
        [COLOR=green]' character not allowed - do not save[/COLOR]
    End Select
  Next cPtr
 
End Function

I knocked this up on the fly so you'll need to check the 'special characters' line to include characters you want and to exclude the characters you don't want.

Then instead of name a1 as a2 you'd go name a1 as sanitise(a2).

thank you,

but I need to save original name . . .

 

[Ruddles]

What do you mean by "save original name"?

If the original name has invalid characters in it, you can't use it as a file name.

 

[xenou]

I think the problem is in VBA - it doesn't use unicode by default (?). We need some kind of function to convert a unicode cell contents to a unicode string value (for use by the VBA name ... as ...). It's been a while since I've looked at this kind of thing though - I'm a little fuzzy on it.

 

[pgc01]

Hi

If you need to use Unicode characters use the FileSystemObject (if you're not used to working with it you can check the help).

With

s1: c:\tmp\élève.txt
s2: c:\tmp\учащийся.txt

this worked with no problem in excel 2000 (W7):

   CreateObject("Scripting.FileSystemObject").MoveFile s1, s2

 

[nodari]

Hi

If you need to use Unicode characters use the FileSystemObject (if you're not used to working with it you can check the help).

With

s1: c:\tmp\élève.txt
s2: c:\tmp\учащийся.txt

this worked with no problem in excel 2000 (W7):

   CreateObject("Scripting.FileSystemObject").MoveFile s1, s2

thank you very much, it's really what I need.