Need Help Compiling Data From Multiple excel Files in order of filename

[andocommanndo]

I have gotten code from other threads and for the most part it works great.
It is copying data from all excel files in a certain folder, then pasting them into one sheet.
The issue I am having is when it is copying data to new sheet, it is not in order of file name.
file name is in date format "YYYYMMDD HHMM"
I also don't know enough about the code to change my column "A" from coming back with "0" since the source files have no data there.
And, I would also like a space between each set of data to separate individual file information.
Here is what I have...

Option Explicit

Private Sub Workbook_Open()
MsgBox "This will compile all the operator rounds in the NH3 Daily Folder. Enjoy!" & vbNewLine & "Make Sure Your Macros Are Enabled."
Dim fPATH As String, fNAME As String
Dim LR As Long, NR As Long
Dim wbGRP As Workbook, wsDEST As Worksheet
Set wsDEST = ThisWorkbook.Sheets("Summary")
NR = wsDEST.Range("B" & Rows.Count).End(xlUp).Row + 1
fPATH = "\\smrt01-dc01-pr\Operator_Required_Rounds\NH3Daily\"       'remember the final \ in this string
fNAME = Dir(fPATH & "*.xls")        'get the first filename in fpath
Do While Len(fNAME) > 0
    Set wbGRP = Workbooks.Open(fPATH & fNAME)   'open the file
    LR = wbGRP.Sheets("Ammonia Skid (Daily)").Range("B" & Rows.Count).End(xlUp).Row  'how many rows of info?
    
    If LR > 3 Then
        wsDEST.Range("A" & NR) = Replace(Range("A1"), "Group ", "")
        wbGRP.Sheets("Ammonia Skid (Daily)").Range("B3:F" & LR).Copy
        wsDEST.Range("B" & NR).PasteSpecial xlPasteAll
        NR = wsDEST.Range("B" & Rows.Count).End(xlUp).Row + 1
    End If
    
    wbGRP.Close False   'close data workbook
        fNAME = Dir         'get the next filename
Loop
Range("A3:A" & NR - 1).SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
With Range("A3:A" & NR - 1)
    .Value = .Value
End With
End Sub

any help would be much appreciated

 

[John_w]

That error occurs if the array passed to BubbleSort is empty. We can handle this by changing the array index in the loop which populates the fileNames array:

    fNAME = Dir(fPATH & "*.xls")        'get the first filename in fpath
    i = -1
    Do While fNAME <> ""
        i = i + 1
        ReDim Preserve fileNames(i)
        fileNames(i) = fNAME
        fNAME = Dir
    Loop
    
    If i >= 0 Then
    
        BubbleSort fileNames
        
        For i = 0 To UBound(fileNames)

             'The rest of your code here




    Else
    
        'fileNames array is empty
        MsgBox "No .xls files found in " & fPATH
        
    End If

PS - it looks like your BubbleSort is functionally the same as mine.

 

[andocommanndo]

I didn't doubt your BubbleSort code, it was my understanding that didn't work . Your adjustment made it dummy proof, and gave me a head slapper when I read your messagebox. I was running this on a test folder and forgot to add any files!!!!! Thanks again, and again.