Solved

How to combine or merge 50 excel spreadsheets

Posted on 2016-07-29
19
83 Views
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?
0
Comment
Question by:wfcrr
  • 8
  • 6
  • 5
19 Comments
 
LVL 29

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")
dws.Cells.Clear
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)
   Else
      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
dws.Columns.AutoFit
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

CombineFilesIntoOneFile.xlsm
0
 
LVL 18

Expert Comment

by:Roy_Cox
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, _
                                                              rToCopy.Columns.Count)
                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, _
                  DataOption1:=xlSortNormal
exithandler:
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

Open in new window

0
 

Author Comment

by:wfcrr
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.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 18

Expert Comment

by:Roy_Cox
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.
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:wfcrr
ID: 41735780
0
 
LVL 29

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.
0
 
LVL 29

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.
CombineFilesIntoOneFile.xls
0
 

Author Comment

by:wfcrr
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?
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735789
@wfcrr

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
0
 

Author Comment

by:wfcrr
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?
0
 
LVL 29

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.
0
 
LVL 18

Expert Comment

by:Roy_Cox
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?
0
 
LVL 18

Expert Comment

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

Author Comment

by:wfcrr
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?
0
 
LVL 18

Expert Comment

by:Roy_Cox
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.
0
 
LVL 18

Accepted Solution

by:
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

Merge-Files-Tab.png
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.

FoldersMerge-Files.xlsm
0
 
LVL 18

Expert Comment

by:Roy_Cox
ID: 41740149
Pleased to help
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now