How to combine or merge 50 excel spreadsheets

Posted on 2016-07-29
Last Modified: 2016-08-02
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?
Question by:wfcrr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 5
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735442
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

LVL 19

Expert Comment

ID: 41735446
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


Author Comment

ID: 41735740
@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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Expert Comment

ID: 41735746
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.

Author Comment

ID: 41735765
Thanks Roy, I am using Excel 2007.  If you could idiot proof it, that would be awesome!
LVL 19

Expert Comment

ID: 41735775
Do you have an example of the data and the resulting merge?

Author Comment

ID: 41735780
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735784
@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.
LVL 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41735787
Okay here is .xls version of the workbook see if you can open it.

Author Comment

ID: 41735788
@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?
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735789

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

Author Comment

ID: 41735790
@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?
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735793
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.
LVL 19

Expert Comment

ID: 41735794
I'm just downloading your files. They are opening OK for me. Why are you using.xls if your version is Excel 2007?
LVL 19

Expert Comment

ID: 41735797
I need to know if you are using .xls or .xlsm.

Author Comment

ID: 41735798
@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?
LVL 19

Expert Comment

ID: 41735805
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.
LVL 19

Accepted Solution

Roy_Cox earned 250 total points
ID: 41735873
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.

LVL 19

Expert Comment

ID: 41740149
Pleased to help

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question