VBA file = Dir() retrieving a gibberish filename

Question

Block of code handling looping through files in a directory:

Dim file As Variant
file = Dir(directory)
While (file <> "")
  If (InStr(file, ".xlsx") > 0) And (InStr(file, "Percentage") = 0) And (InStr(file, aggregateFilename) = 0) Then
        Call fight_dynamics_by_percentage_buckets(aggregateFilename, directory, file, folderToRunMacro, num_buckets)
  End If
 file = Dir
Wend

Earlier in my code, the user selects a directory storing a set of files. In my test directory, I have the following files:

Analyzed.11.14.383.Chamber1.xlsx
Analyzed.11.14.383.Chamber2.xlsx
Analyzed.11.14.383.Chamber3.xlsx

When I run the macro, the program extracts the filenames as:

Analyzed.11.14.383#494E5A0.xlsx
Analyzed.11.14.383#494E5A1.xlsx
Analyzed.11.14.383#494E5A2.xlsx

I have used this exact code before on previous directories, and have had no trouble extracting the correct filenames.

What's going on here?

What (other than Excel) software automatically generated the workbooks and what is the code behind the generation of the filenames? It seems to me that you have some unicode in there. — user4039065
@Jeeped I wondered that too -- but it seems like the garbled portion is a hex representation of a number and it would be weird if a Unicode string which decodes to "Chamber1" in one encoding decodes to a hex string in another. — John Coleman
Actually the problem doesn't seem to originate at Chamber but at the period (i.e. full stop) just prior to Chamber. It could also be zero length spaces or some other rare character wrongly used as an atom delimiter. FWIW, A0 (#160;) is a non-breaking space. — user4039065

Nand Kishor Swami Nand Kishor Swami · Accepted Answer · 2015-07-06T19:49:31

I do not see any issue with your code and it is working fine for me as well. It may be a specific system of excel issue. You can use another option to get the same result and here are the codes for your reference. Have a good day

Set filesys = CreateObject("Scripting.FileSystemObject")
For Each sfile In filesys.GetFolder(directory).Files
    file = filesys.GetFileName(sfile)
    If (InStr(file, ".xlsx") > 0) And (InStr(file, "Percentage") = 0) And (InStr(file, aggregateFilename) = 0) Then
        Call fight_dynamics_by_percentage_buckets(aggregateFilename, directory, file, folderToRunMacro, num_buckets)
    End If
Next

VBA file = Dir() retrieving a gibberish filename

1 Answers