Solved

How to combine or merge 50 excel spreadsheets

Posted on 2016-07-29
19
73 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 28

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 17

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
 
LVL 17

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 17

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 28

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 28

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 28

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 28

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 17

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 17

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 17

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 17

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 17

Expert Comment

by:Roy_Cox
ID: 41740149
Pleased to help
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now