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

Question

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?

Imagery

My bad, I indeed did this in a hurry but it wasn't to point out that it was a duplicate, it was to point to a possible angle to work with, but after a closer look it won't apply to your issue. Let me take a closer look. Can you post your code? Or how did you Dim the variable that you use for the Dir? Have you tried Files Scripting Object too? – Depopulate

This link vbforums.com/… seems interesting but it's VB and not only VBA. There is also a method to convert the special character afterwards but it'll work only for the character that you have done a transcodification for. Anyways, it seems that the FSO (Files Scripting Object) doesn't have the same limitation than Dir, so personally I'd go that way if it's not to complex to implement in your code! – Depopulate

Damn... The FSO has the same issue... Have you tried to declare your Dir value as Variant? (wild guess but at this point, it's seems (to me at least) that there is only VB or Transcodification left...). Here are the links for transco/convert : pcreview.co.uk/threads/… and vbforums.com/… – Depopulate

best not to use non-standard characters in filenames. Three options - fix the filenames or use the Dir's function ability to load partial filenames using the * character or use AppLocale Utility – Klepht

@Depopulate Yes, FSO also has the same issue. Secondly I have tried variant as the variable for dir with the same problem, and only code I can find seems to be VB "Magic" which won't work in vba. Can't believe no one else has faced this issue! I've also tried setting the string value to use the ł character, but Access converts it back to 'l' – Imagery

@Meehow using * is of no help whatsoever. The conversion of characters is the problem meaning the file can't be opened. And, [a-z0-9_]+ filenames kinda died out a long time ago, especially for non-English users. – Imagery

Answer 1

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.

Slippy

@Imagery - When I Debug.Print f.Size in the loop I get the correct (non-zero) values. I'm not sure what you mean by "LOF", so maybe you're doing something quite different. – Slippy

@Imagery - Does f.Size return zero for all files, or just for ones with "funny characters" in their names? – Slippy

A hidden file, perhaps? – Slippy