Do while loop and date sorting

[excel_sunsan]

I've a macro to read the file names in a folder and move the file names to a cell. 1 cell per file name. File names has dates but the single digit dates don't have leading zeros so they are not sorted correctly and I've to insert the leading zeros for this to work correctly. Is there a way to make excel sort the file name correctly.

File names appear as below in explorer:
abcd 10-8-10
abcd 10-9-10
abcd 10-10-10

but after running the macro they appear as below in the cell:
abcd 10-10-10
abcd 10-8-10
abcd 10-9-10

Macro:
FNames = Dir("*abcd*.xls")
Do While FNames <> ""
....
.....
....
loop

[pike]

Hi excel_sunsan
welcome to the forum,
you could modify this ...

Please Login or Register  to view this content.

[excel_sunsan]

Thanks Pike for a quick response.

However, I'm still getting the result as
abcd 10-10-10.xls
abcd 10-8-10.xls
abcd 10-9-10.xls

However, I need it as follows:
abcd 10-8-10.xls
abcd 10-9-10.xls
abcd 10-10-10.xls

[pike]

Hi excel_sunsan

One way would be add the values to a collection the do a quick sort

Please Login or Register  to view this content.

[pike]

Hi excel_sunsan

It finaly clicked on what you meant... this will sort by the date
it would be easier just to filter the cells

Please Login or Register  to view this content.

[squiggler47]

As a tip, its better to name files with dates YYYY-MM-DD Name.XLS that way explorer can sort them in order!

[excel_sunsan]

pike - Thanks for the code. Looks like its never entering the IF..ENDIF. But thats OK, I think we will change the filename format so it will sort as in Explorer.

[pike]

hi excel_sunsan
The "abcd" needs to be changed

Please Login or Register  to view this content.

try the code from the post as I did tweak it

[excel_sunsan]

I meant its not entering the below IF statement.

Please Login or Register  to view this content.

I tried both your above codes and I'm still not getting it like:
abcd 10-8-10.xls
abcd 10-9-10.xls
abcd 10-10-10.xls

[pike]

arr. are the date formated day month year?

[excel_sunsan]

No, they are month day year.

[pike]

hi excel_sunsan
change the "Short Date" to 0 as per below this will make the date a serial number

Please Login or Register  to view this content.

[excel_sunsan]

pike.....Thank You! Thank You! Thank You!

It worked as I was expecting it.