Collect Excel Files From A folder
Private Sub CommandButton1_Click()
Dim source As String
source = "D:\Consolidation\"
Dim filename As String
filename = Dir(source & "*.x??")
'Extensions:
'(i) .xls for workbook without macros upto 2003
'(ii) .xlsx for workbook without macros from 2007
'(ii) .xlsm for workbook withmacros
Dim e As Integer
Do While filename <> ""
e = e + 1
Cells(e, 1) = filename
filename = Dir
Loop
Cells.EntireColumn.AutoFit
End Sub