Combine Multiple Excel Workbooks into one Workbook with multiple sheets

Question

I have about 70 different excel files that I need to combine into one master workbook. I would like each excel file to get its own worksheet in the master workbook. The name of the worksheet generated in the master workbook doesn't matter.

I retrieved this code off of another website, but cannot make it work for my needs. This code stipulates that all files to be combined are located in the same directory. I have them located here "C:\Users\josiahh\Desktop\cf"

Below is the code as it is now

Sub GetSheets()
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

It will not import anything. I will run the macro and nothing will happen. — Josiah Hulsey
This code is looking ONLY for *.xls files -- that's probably the problem (assuming your files are the newer xlsm/xlsx format, they will not be picked up by this function). — David Zemens
Are the ' 70 different excel files' 97-2003 format workbooks? Your file mask looks for *.xls, not *.xls? (e.g. *.xlsx, *.xlsm, *.xlsb, etc). — user4039065

David Zemens David Zemens · Accepted Answer · 2015-01-26T23:11:17

This is tested and works as expected. You would be wise to use Option Explicit and declare your variables appropriately in the future, although that did not cause any problems with your code.

As indicated in comments above, the likely failure is that the argument you're passing to the Dir function is unnecessarily restrictive:

=Dir(path & "*.xls") will look ONLY for files ending exactly in ".xls", and will not account for newer file formats. To resolve that, do =Dir(path & "*.xls*")

Code below:

Option Explicit
Const path As String = "C:\Users\dt\Desktop\dt kte\"
Sub GetSheets()
Dim FileName As String
Dim wb As Workbook
Dim sheet As Worksheet

FileName = Dir(path & "*.xls*")
Do While FileName <> ""
    Set wb = Workbooks.Open(FileName:=path & FileName, ReadOnly:=True)
    For Each sheet In wb.Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next sheet
    wb.Close
    FileName = Dir()
Loop
End Sub

Combine Multiple Excel Workbooks into one Workbook with multiple sheets

1 Answers