How to Merge Multiple xlsx and xls files in one workbook through VBA Code

  • I have many xlsx and xls files in a folder containing 2-3 worksheets in each file. I want to merge all these files into one workbook. I have a sample code but it is not merging xlsx file, it is picking only xls files of the selected folder. Sample code is mentioned below. Help me

    Sub MergeFiles ()
    Dim numberOfFilesChosen, i As Integer
    Dim tempFD As FileDialog
    Dim mainWb, sourceWb As Workbook
    Dim tempWS As Worksheet
    Set mainWb = Workbooks.Add 'Application.ActiveWorkbook
    Set tempFD = Application.FileDialog(msoFileDialogFilePicker)
    'Allow the user to select multiple workbooks
    tempFD.AllowMultiSelect = True
    numberOfFilesChosen = tempFD.Show
    'Loop through all selected workbooks
    For i = 1 To tempFD.SelectedItems.Count
    'Open each workbook
    Workbooks.Open tempFD.SelectedItems(i)
    Set sourceWb = ActiveWorkbook
    'Copy each worksheet to the end of the main workbook
    For Each tempWS In sourceWb.Worksheets
    tempWS.Copy after:=mainWb.Sheets(mainWb.Worksheets.Count)
    Next tempWS
    'Close the source workbook
    Next i
    End Sub

  • Hi,

    I tried to follow the tutorial on TrumpExcel blog (How to Combine Multiple Excel Files into One Excel Workbook) and get bad file name or number error. Has anyone experienced that?