View Single Post
 
Old 08-12-2015, 10:25 AM
equalizer88 equalizer88 is offline Windows 7 32bit Office 2010 32bit
Novice
 
Join Date: Jul 2015
Posts: 15
equalizer88 is on a distinguished road
Default Naming files in folder, DOS vs Windows Explorer ASCII sequence

I converted excel sheets to text files. I tried to name output files as

FileName = "C:\Users\VBA-beginner\Table_" & x & ".txt"

This will apply to WORD VBA as well.

Problem is sorting for humans and ASCII order used by computers is different. My file opened were in the order Table_1, ...10, ...

instead of Table_1, ..2,...3,. I needed files to be saved in a certain sequence, the human order.


Using windows explorer, the files appeared in human order, but using the F8 debugger, I found our sequence was different order. When I opened DOS window and used the dir command, it displayed ASCII order, which is how files get opened with VBA.

See blog for further explanation http://blog.codinghorror.com/sorting...al-sort-order/

Only solution is to use trailing zeros.

So here is my fix with leading and trailing format:

000_Table_000
001_Table_001...


x = Format(i , "000")
FileName = "C:\Users\VBA-beginner\" & x & "__Table_" & x & ".txt"

Last edited by equalizer88; 08-12-2015 at 03:20 PM. Reason: Generic username
Reply With Quote