Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)

I am iterating through files in a folder (which means I do not know the names in the folder), and have a file with a Polish ł character.

The Dir function converts this to an l which means the filename can't be found at a later date. I've declared the var that I'm assigning the dir value to as a string.

I've also tried FSO and getfolder which also has the same issue.

I've also noticed the file dialog (set to folder select mode) converts the character above too.


Is this a bug, or is it something that can be worked around?

like image 589
user3791372 Avatar asked Nov 13 '15 04:11

user3791372


1 Answers

It sounds like you are being misled by the fact that while VBA itself supports Unicode characters, the VBA development environment does not. The VBA editor still uses the old "code page" character encodings based on the locale setting in Windows.

Certainly FileSystemObject et. al. do in fact support Unicode characters in file names, as illustrated by the following example. With a folder containing three plain text files

Filename: 1_English.txt
Contents: London is a city in England.

Filename: 2_French.txt
Contents: Paris is a city in France.

Filename: 3_Połish.txt
Contents: Warsaw is a city in Poland.

The following VBA code ...

Option Compare Database
Option Explicit

Sub scanFiles()
    Dim fso As New FileSystemObject, fldr As Folder, f As File
    Set fldr = fso.GetFolder("C:\__tmp\so33685990\files")
    For Each f In fldr.Files
        Debug.Print f.Path
    Next
    Set f = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

... produces the following output in the Immediate window ...

C:\__tmp\so33685990\files\1_English.txt
C:\__tmp\so33685990\files\2_French.txt
C:\__tmp\so33685990\files\3_Polish.txt

Note that the Debug.Print statement converts the ł character to l because the VBA development environment cannot display ł using my Windows locale (US English).

However, the following code does open all three files successfully ...

Option Compare Database
Option Explicit

Sub scanFiles()
    Dim fso As New FileSystemObject, fldr As Folder, f As File, ts As TextStream
    Set fldr = fso.GetFolder("C:\__tmp\so33685990\files")
    For Each f In fldr.Files
        Set ts = fso.OpenTextFile(f.Path)
        Debug.Print ts.ReadAll
        ts.Close
        Set ts = Nothing
    Next
    Set f = Nothing
    Set fldr = Nothing
    Set fso = Nothing
End Sub

... displaying

London is a city in England.
Paris is a city in France.
Warsaw is a city in Poland.
like image 53
Gord Thompson Avatar answered Oct 19 '22 23:10

Gord Thompson