VBA to open files in order of date in filename?

Question

What I have at the moment:

Sub Merge()Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
    Application.ScreenUpdating = False
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("G:\loc\loc\loc\loc\loc\loc\loc")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
        Set bookList = Workbooks.Open(everyObj)

        Range("A4:I" & Range("A65536").End(xlUp).Row).Copy
        ThisWorkbook.Worksheets(1).Activate

        Range("B65536").End(xlUp).Offset(0, 0).PasteSpecial
        Application.CutCopyMode = False
        bookList.Close
    Next
End Sub

I want to change this so that it opens and copies from the files in date order (which is included in there filename)

eg it would open and copy from in an order like below:

"20150601 - Daily Update.xls"
"20150602 - Daily Update.xls"
"20150603 - Daily Update.xls"

Take a look here: http://stackoverflow.com/questions/16895525/order-of-files-collection-in-filesystemobject Does that solve it? — LocEngineer, Jun 30 '15 at 12:01
output the filenames to a temp sheet order them and read them back into a loop and open them in the second loop? — 99moorem, Jun 30 '15 at 12:03

score 0 · Answer 1 · answered Jun 30 '15 at 12:43

I have merged the sorting solution of user @Papasmile referenced in the comment of user @LocEngineer with your code (all credits to them):

Sub Merge()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object
Dim filesObj As Object, everyObj As Object, outputLines As Object
Dim outputLine
    Application.ScreenUpdating = False
    Set mergeObj = CreateObject("Scripting.FileSystemObject")
    Set outputLines = CreateObject("System.Collections.ArrayList")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("G:\loc\loc\loc\loc\loc\loc\loc")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
        outputLines.Add everyObj.Name
    Next
    outputLines.Sort

    For Each outputLine In outputLines
        Set bookList = Workbooks.Open(outputLine)

        Range("A4:I" & Range("A65536").End(xlUp).Row).Copy
        ThisWorkbook.Worksheets(1).Activate

        Range("B65536").End(xlUp).Offset(0, 0).PasteSpecial
        Application.CutCopyMode = False
        bookList.Close
    Next
End Sub

VBA to open files in order of date in filename?

1 Answers1