Workaround for ChDir/CurDir when path has unicode characters
 

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

Workaround for ChDir/CurDir when path has unicode characters
 

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

Workaround for ChDir/CurDir when path has unicode characters
 

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

Workaround for ChDir/CurDir when path has unicode characters
 

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

Workaround for ChDir/CurDir when path has unicode characters
 

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.