How to combine or merge 50 excel spreadsheets

I have approximately 50 different excel files I need to turn into one file. Is there a way to do that?  I am not sure if I should be saying merge, or combine, or convert...what I need to do is have all the data that is in 50 separate excel files somehow now be in one excel file.  If I can't find a method for doing that, I will simply copy/paste from each file into one very large file.  However, I am thinking you guys probably have a formula or method for doing that?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Though the obvious choice is to use Power Query if you are using Excel 2010 or later, here is a VBA code that will do the trick.

In the attached file, you will find a button on Sheet1 called Combine Files, you may click this button to run the code. The code will prompt you to select the folder where all your excel files are saved.
Sub CombineFilesIntoOneFile()
Dim dwb As Workbook, swb As Workbook
Dim dws As Worksheet, sws As Worksheet
Dim slr As Long, slc As Long
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim SeletedFolder As String

Application.ScreenUpdating = False
Set dwb = ThisWorkbook
Set dws = dwb.Sheets("Sheet1")
Set fso = CreateObject("Scripting.FileSystemObject")

With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = "Select A Folder!"
   .ButtonName = "Confirm"
   If .Show = -1 Then
      SelectedFolder = .SelectedItems(1)
      Set folder = fso.GetFolder(SelectedFolder)
      MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
      Exit Sub
   End If
End With
For Each file In folder.Files
   If InStr(file.Name, dwb.Name) = 0 And Left(fso.GetExtensionName(file), 2) = "xl" Then
      Workbooks.Open file
      Set swb = ActiveWorkbook
      Set sws = swb.Sheets(1)
      slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
      slc = sws.Cells(1, Columns.Count).End(xlToLeft).Column
      If dws.Range("A1").Value = "" Then
         sws.Rows(1).Copy dws.Range("A1")
      End If
      sws.Range("A2", sws.Cells(slr, slc)).Copy dws.Range("A" & Rows.Count).End(3)(2)
      swb.Close True
   End If
   Set swb = Nothing
Next file
Application.ScreenUpdating = True
MsgBox "Data from all the Files have been copied into one Sheet successfully copied to all the Workbooks.", vbInformation, "Done!"
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
Here's some code that I have used for years.

Option Explicit

' Module    : Data
' Author    : Roy Cox (royUK)
' Website   : for more examples and Excel Consulting
' Date      : 19/11/2011
' Purpose   : Combine data from several workbooks
' Disclaimer: Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

Sub CombineData()
    Dim oWbk As Workbook
    Dim rRng As Range
    Dim rToCopy As Range
    Dim rNextCl As Range
    Dim lCount As Long
    Dim bHeaders As Boolean
    Dim sFil As String
    Dim sPath As String

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        '   On Error GoTo exithandler
        ' assumes workbooks are in a sub folder named "Data"
        sPath = ThisWorkbook.Path & Application.PathSeparator & "Data"
        ChDir sPath
        sFil = Dir("*.xl**")    'file type
        Do While sFil <> ""    'will start LOOP until all files in folder sPath have been looped through

            With ThisWorkbook.Worksheets(1)
                Set rRng = .Range("A1").CurrentRegion
                If rRng.Cells.Count = 0 Then
                    'no data in master sheet
                    bHeaders = False
                Else: bHeaders = True
                End If

                Set oWbk = Workbooks.Open(sPath & Application.PathSeparator & sFil)    'opens the file
                'A1 must be within the data, if not amend the Range below
                Set rToCopy = oWbk.ActiveSheet.Range("A1").CurrentRegion
                If Not bHeaders Then
                    Set rNextCl = .Cells(1, 1)
                    bHeaders = True
                Else: Set rNextCl = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                    'headers exist so don't copy
                    Set rToCopy = rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                End If
                rToCopy.Copy rNextCl
            End With
            oWbk.Close False     'close source workbook
            sFil = Dir
        Loop    ' End of LOOP
        'sort to remove empty rows
        Set rRng = ThisWorkbook.Worksheets(1).UsedRange
        rRng.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
                  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

wfcrrAuthor Commented:
@Neeraj, I can't open the file. Says it is not a valid format or extension?  I am using Excel 2007...
@Roy, can you sort of idiot proof some instruction on how to use that code?  I really am not much of a tech guy...but I can follow 2nd grader instructions.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Roy CoxGroup Finance ManagerCommented:
Which version of Excel are you using? I can  provide you with a suitable workbook with the code in if you let me know which version.

The code that I have posted will work in any version of Excel.

It requires the source workbooks to be in a sub folder called Data within the folder that contains the workbook with the code in.
wfcrrAuthor Commented:
Thanks Roy, I am using Excel 2007.  If you could idiot proof it, that would be awesome!
Roy CoxGroup Finance ManagerCommented:
Do you have an example of the data and the resulting merge?
wfcrrAuthor Commented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Neeraj, I can't open the file. Says it is not a valid format or extension?  I am using Excel 2007

That's a temporary bug here. I also have the same issue at my end and report this bug to tech team and hopefully it will resolved soon.

To deal with this, download and SAVE this workbook on your system and then open it.

The workbook attached belongs to the Excel 2013,

Another option is, you can just copy the code into a blank workbook's standard module and run the code from there.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay here is .xls version of the workbook see if you can open it.
wfcrrAuthor Commented:
@Roy....Is the file I uploaded downloading as an Excel file? I uploaded the Excel file, but when I tried to download it just now, it keeps coming in as a Word doc.  Were you able to download it as an Excel file?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:

I tried downloading one of your sample workbook and it was opened in Excel application. So no issue at my end. That seems to be another bug. Lol
wfcrrAuthor Commented:
@Neeraj, I was able to open the file you uploaded, but when I hit the button "Combine Files" and navigate to the folder that has the excel files, nothing in the folder if viewable.  All the excel files in the folder are excel 2007 files, but, none of them appear in the folder when I navigate using the "Combine Files" button...what should I do?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
While navigating to the source folder, you don't need to open or double click the folder instead you just select the source folder and click on Confirm to proceed.
Remember it's a folder picker dialog box not the file picker.
Roy CoxGroup Finance ManagerCommented:
I'm just downloading your files. They are opening OK for me. Why are you using.xls if your version is Excel 2007?
Roy CoxGroup Finance ManagerCommented:
I need to know if you are using .xls or .xlsm.
wfcrrAuthor Commented:
@Roy, I don't know why I'm using .xls.  What should I be doing? I just saved the file...
Also, I have to leave the office, can you upload the file you want me to use? I'll plan to look at it either tomorrow, or for sure on Monday morning. Will you be around on Monday?
Roy CoxGroup Finance ManagerCommented:
I have added the code to workbook based on your downloads.

Basically the code will import data from a sub folder  named Data which stores the data files. This folde should be in the main folder that houses the master workbook.

To run the code there is a new section in the right of the Data Tab of the Ribbon named Merge Files with a button to run the code.

As you won't be testing immediately I'm going to add some features and attach the example soon.
Roy CoxGroup Finance ManagerCommented:
I've created a template file that contains the code and a a Merge Files added to the Data Tab of the Ribbon.

Before running the code select the data sheet

The code will run automatically if the source files are within a sub folder named Data otherwise the user is offered the chance to select a different Folder.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.