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
    [CODE][


    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
    sourceWb.Close
    Next i
    End Sub
    /CODE]


  • 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?