Hi guys, long time.

Problem:
VBA.Filesystem works with ansi paths, not with unicode paths.
e.g. CurDir/ChDir and several other methods return converted strings,
without the actual unicode characters.

For testing:
(English WindowsXP and Excel 97 thru 2007 (or actually VBA 6.5.1020))


Create a folder with a unicode character in the name: Arab?Test
where the ? is unicode x06E9, which looks nice in CharMap,
but any extended unicode character will do.

In that folder create a file with another "foreign" char.

Excel has no problems changing folders and opening/saving files.
However VBA is thoroughly confused.
When I've navigated to the "culprit" folder via excel's File/Open
dialog and i try following in VBE immediate:

ChDir CurDir

I get error 76 path not found!


I've done some research and experimenting:
created following workarounds:

With New Scripting.FileSystemObject
'Retrieve the full unicode string for CurDir.
szCurDir = .GetAbsolutePathName(Vba.CurDir$)
'Change to this folder using short 8.3 name.
vba.ChDir .GetShortPath(vba.CurDir$)
End With

I need a trick for ChDir without short (8.3) paths
(looks terrible AND can be turned of in NTFS)...

I dont mind a ton of apis but it must be foolproof.

--
keepITcool

Excel's VBA is converting strings to ANSI before calling windows API functions.
Try calling the "Unicode version" of the Win32 API directly.
Here is an example:

Option Explicit

Private Declare Function GetCurrentDirectory Lib "kernel32" _
Alias "GetCurrentDirectoryW" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Declare Function SetCurrentDirectory Lib "kernel32" _
Alias "SetCurrentDirectoryW" (ByVal lpBuffer As String) As Long

Sub test()
Dim buff As String * 420
Dim len_dir, str_end, old_dir

old_dir = CurDir
str_end = StrConv("\Arab" & ChrW(1769) & "Test" & Chr(0), vbUnicode)

Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
Debug.Print SetCurrentDirectory(Left(buff, len_dir + len_dir) & str_end)
Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
ChDir old_dir
End Sub
--
Regards,
Luc.

"Festina Lente"


"keepITcool" wrote:

Hi guys, long time.

Problem:
VBA.Filesystem works with ansi paths, not with unicode paths.
e.g. CurDir/ChDir and several other methods return converted strings,
without the actual unicode characters.

For testing:
(English WindowsXP and Excel 97 thru 2007 (or actually VBA 6.5.1020))


Create a folder with a unicode character in the name: Arab?Test
where the ? is unicode x06E9, which looks nice in CharMap,
but any extended unicode character will do.

In that folder create a file with another "foreign" char.

Excel has no problems changing folders and opening/saving files.
However VBA is thoroughly confused.
When I've navigated to the "culprit" folder via excel's File/Open
dialog and i try following in VBE immediate:

ChDir CurDir

I get error 76 path not found!


I've done some research and experimenting:
created following workarounds:

With New Scripting.FileSystemObject
'Retrieve the full unicode string for CurDir.
szCurDir = .GetAbsolutePathName(Vba.CurDir$)
'Change to this folder using short 8.3 name.
vba.ChDir .GetShortPath(vba.CurDir$)
End With

I need a trick for ChDir without short (8.3) paths
(looks terrible AND can be turned of in NTFS)...

I dont mind a ton of apis but it must be foolproof.

--
keepITcool

Fantastic. Thx.
--
Jurgen


PapaDos wrote:

Excel's VBA is converting strings to ANSI before calling windows API
functions. Try calling the "Unicode version" of the Win32 API
directly. Here is an example:

Option Explicit

Private Declare Function GetCurrentDirectory Lib "kernel32" _
Alias "GetCurrentDirectoryW" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Declare Function SetCurrentDirectory Lib "kernel32" _
Alias "SetCurrentDirectoryW" (ByVal lpBuffer As String) As Long

Sub test()
Dim buff As String * 420
Dim len_dir, str_end, old_dir

old_dir = CurDir
str_end = StrConv("\Arab" & ChrW(1769) & "Test" & Chr(0),
vbUnicode)
Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
Debug.Print SetCurrentDirectory(Left(buff, len_dir + len_dir) &
str_end) Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
ChDir old_dir
End Sub

You are welcome.

Just out of curiousity, which version of Windows and Excel have you tried it
on ?

I used Windows XP Pro SP2 and Excel 2002.
--
Regards,
Luc.

"Festina Lente"


"keepITcool" wrote:

Fantastic. Thx.
--
Jurgen


PapaDos wrote:

Excel's VBA is converting strings to ANSI before calling windows API
functions. Try calling the "Unicode version" of the Win32 API
directly. Here is an example:

Option Explicit

Private Declare Function GetCurrentDirectory Lib "kernel32" _
Alias "GetCurrentDirectoryW" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Declare Function SetCurrentDirectory Lib "kernel32" _
Alias "SetCurrentDirectoryW" (ByVal lpBuffer As String) As Long

Sub test()
Dim buff As String * 420
Dim len_dir, str_end, old_dir

old_dir = CurDir
str_end = StrConv("\Arab" & ChrW(1769) & "Test" & Chr(0),
vbUnicode)
Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
Debug.Print SetCurrentDirectory(Left(buff, len_dir + len_dir) &
str_end) Debug.Print CurDir
len_dir = GetCurrentDirectory(200, buff)
Debug.Print len_dir, "[ " & Left(buff, len_dir + len_dir) & " ]"
ChDir old_dir
End Sub

That VBA bug exists since xl97 and is still present in xl2007
I'm using XP Pro SP2.

Those APIs will not work on some Win95/Win98 systems, but those do not
matter to me. And I've no doubt these APIs will work on Vista.

I was so busy using SHLWAPI i forgot to do the obvious and properly
browse ApiGuide/ApiViewer.

--



PapaDos wrote:

You are welcome.

Just out of curiousity, which version of Windows and Excel have you
tried it on ?

I used Windows XP Pro SP2 and Excel 2002.