I want to combine multiple Excel files

Question

I need assistance with the issue that I have multiple Excel files which are being updated on daily basis but for the analysis part I have to combine them all; and then perform the analysis.

Is there any way to automate this process, where all the data from the excel files will be copied to a single excel file.

All kinds of solutions welcome. I can't use Google sheets.

score 1 · Answer 1 · edited Feb 23 '21 at 09:33

Try adding macros, like this one to combine multiple Excel files

Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks 
(.xls;.xlsx;.xlsm),.xls;.xlsx;.xlsm", Title:="Choose Excel files to merge", 
MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) &gt; 0) Then
    countFiles = 0
    countSheets = 0

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set wbkCurBook = ActiveWorkbook

    For Each fnameCurFile In fnameList
        countFiles = countFiles + 1

        Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

        For Each wksCurSheet In wbkSrcBook.Sheets
            countSheets = countSheets + 1
            wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
        Next

        wbkSrcBook.Close SaveChanges:=False

    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox &quot;Processed &quot; &amp; countFiles &amp; &quot; files&quot; &amp; vbCrLf &amp; &quot;Merged &quot; &amp; countSheets &amp; &quot; worksheets&quot;, Title:=&quot;Merge Excel files&quot;
End If


Else
    MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

I want to combine multiple Excel files

1 Answers1