Found basically the same question here:http://stackoverflow...-a-single-excel-file
With the following vba (?) code solution:
Application.DisplayAlerts = False
Dim strFilename As String
Dim strPath As String
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbFiles As Workbook
Dim i As Integer
i = 1
Set wbMaster = ThisWorkbook
Set wsMaster = wbMaster.Sheets(2)
strPath = "C:\path\to\your\files\"
strFilename = Dir(strPath & "*.xls")
Do While strFilename <> ""
Set wbFiles = Workbooks.Open(strPath & strFilename, False)
wsMaster.Cells(RowIndex:=i, ColumnIndex:=1).PasteSpecial Paste:=xlPasteAll
strFilename = Dir
i = i + 1
Application.DisplayAlerts = True
"A couple of things to note:
- You'll want to change the Sheet(x) references to the appropriate values for your needs
- Ensure there is a "\" at the end of strPath when you put in your own path
- I've turned DisplayAlerts to false during the execution to avoid a pop-up on every file asking if you want to clear the clipboard or not."
Unfortunately I don't understand how to use his solution. Wish he had have given an example, or explained it more extensively.