• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

Command to combine many Excel files into one

I have a bunch of excel files with the same headers.  They are all in one windows folder.

Specifically where one excel file ends, the next one is appended to the bottom of that one.  Or, similarly it combines all of the excel files into one new "Master" excel file.

Is there a command that will combine all the excel files into one.  Excel 2007  Windows 2007.

Thanks

Rowby
0
Rowby Goren
Asked:
Rowby Goren
  • 13
  • 8
1 Solution
 
BullmanTechCommented:
There is no standard Excel funcionality that accomplishes this. There are Excel VBA / macro options that are available, however. Are you interested into breaking into code to solve this problem, or is that not something that interests you?
0
 
Rowby GorenAuthor Commented:
Hi BullmanTech,

Yes, a VBA/macro option seems the way I would like it then.

I assume I would paste the VBA / Macro into Excel's VB editor....

My files are all Excel 2007 with xlsx extensions.

Thanks!
0
 
BullmanTechCommented:
Here is a collection of functions I wrote to accomplish this.

Instructions:
2) Create a new Module in the VB Editor of a given workbook (Insert > Module).
3) Paste the below code into the new module.
4) Save the workbook as a Macro-Enabled workbook (.xlsm).
5) Enter the full folder path of the folder containing the files you want to combine into cell A3 of any worksheet in the workbook with the macros.
6) In the VB Editor, click (place the cursor) anywhere in the 'CombineWorkooks' procedure and press F5 to run the code.

This should combine all workbook tabs into one 'Master' Excel file.

Option Explicit

Dim iArr As Integer
Dim origWB As Workbook

Public Sub CombineWorkbooks()
    Application.ScreenUpdating = False
    Dim MyPath As String
    Dim origWS As String
    MyPath = ActiveSheet.Range("A3").Value  'Set the path.
    Set origWB = ActiveWorkbook
    origWS = ActiveSheet.Name

    If Not FolderExists(MyPath) Then
        MsgBox "Please enter a folder path in cell A3.", vbCritical, "Enter Folder Path"
        Exit Sub
    End If
    If Not Right(MyPath, 1) = "\" Then
            MyPath = MyPath & "\"
            ActiveSheet.Range("A3").Value = MyPath
    End If

    Const FileType = "*.xls*" ' or "*.doc"
    iArr = 0
    Call ProcessFiles(MyPath, FileType)
    Worksheets(origWS).Select
    Application.ScreenUpdating = True
    MsgBox "Process completed successfully.", vbInformation, "Process Complete"
End Sub

Public Sub ProcessFiles(strFolder As String, strFilePattern As String)
    Dim strFileName As String
    Dim strFolders() As String
    Dim arrFolderPaths()
    Dim arrFileNames()
    Dim iFolderCount As Integer
    Dim i As Integer
    Dim openWB As Workbook
    Dim openWBfull As String
    Dim intOrigShtCount As Integer
    Dim wks As Worksheet

    If Not Right(strFolder, 1) = "\" Then
        'Add a slash
        strFolder = strFolder & "\"
    End If

    'Collect child folders
    strFileName = Dir$(strFolder, vbDirectory)
    Do Until strFileName = ""
        If (GetAttr(strFolder & strFileName) And vbDirectory) = vbDirectory Then
            If Left$(strFileName, 1) <> "." Then
                ReDim Preserve strFolders(iFolderCount)
                strFolders(iFolderCount) = strFolder & strFileName
                iFolderCount = iFolderCount + 1
            End If
        End If
        strFileName = Dir$()
    Loop

    If Not Right(strFolder, 1) = "\" Then
        'Add a slash
        strFolder = strFolder & "\"
    End If

    iArr = 0
    'Process files in current folder
    strFileName = Dir$(strFolder & "\" & strFilePattern)
    Do Until strFileName = ""
        iArr = iArr + 1
        ReDim Preserve arrFolderPaths(iArr)
        ReDim Preserve arrFileNames(iArr)
        arrFolderPaths(iArr) = strFolder
        arrFileNames(iArr) = strFileName

        openWBfull = arrFolderPaths(iArr) & arrFileNames(iArr)

On Error GoTo FileWrongType:

        'If the workbook is already open, don't open it. Regardless, set openWB object = the open workbook.
        If WorkbookIsOpen(CStr(arrFileNames(iArr))) Then
            'Only copy the sheets from the new workbook into the original if they are not the same workbook.
            'i.e. Don't re-copy the original workbook's worksheets back into itself.
                Set openWB = Workbooks(CStr(arrFileNames(iArr)))
                If Not origWB.Name = openWB.Name Then
                    For Each wks In openWB.Worksheets
                        intOrigShtCount = origWB.Sheets.Count
                        openWB.Sheets(wks.Name).Copy After:=origWB.Sheets(intOrigShtCount)
                    Next wks
            End If
        Else
            Set openWB = Workbooks.Open(openWBfull)
            For Each wks In openWB.Worksheets
                intOrigShtCount = origWB.Sheets.Count
                openWB.Sheets(wks.Name).Copy After:=origWB.Sheets(intOrigShtCount)
            Next wks
            openWB.Close
        End If

        strFileName = Dir$()
    Loop

     'Look through child folders
    For i = 0 To iFolderCount - 1
        ProcessFiles strFolders(i), strFilePattern
    Next i
    Exit Sub
FileWrongType:
    MsgBox "Destination spreadsheet can not be an Excel 97-2003 Workbook (*.xls). " _
    & "Please use the file format '*.xlsx' or '*.xlsm'."
    Exit Sub
End Sub

Public Function FolderExists(strFullPath As String) As Boolean
    On Error GoTo ErrCatch:
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FolderExists = True
ErrCatch:
    On Error GoTo 0
End Function

Public Function WorkbookIsOpen(wkbName As String) As Boolean
    Dim wb1 As Workbook
    For Each wb1 In Application.Workbooks
        If wb1.Name = wkbName Then
            WorkbookIsOpen = True
        End If
    Next wb1
End Function

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rowby GorenAuthor Commented:
Thanks BullmanTech!  I will be trying this out later today.

Rowby
0
 
Rowby GorenAuthor Commented:
Hi

On Excel Menu > Insert I can't find "Module".   Can I use Alt F10 and paste it into the VB Editor?  Or must this be done only via the Insert Menu.



See attached. screen capture.
insert-module.jpg
0
 
BullmanTechCommented:
Apologies - I was talking about the Insert menu within the VB Editor. Find the VB Editor in Excel 2007 by:

1) Using the Developer tab > Visual Basic (Developer tab made visible by Office Button > Excel Options > Popular > Show Developer tab in the Ribbon).
2) Pressing Alt + F11
0
 
Rowby GorenAuthor Commented:
Got it.  Will try when I get to a computer.
what will the name of the new "Master" folder be.  And will I find it in the same folder as the other files?
0
 
BullmanTechCommented:
There will not be a master 'folder,' but there will be a master file, one file containing all of the worksheets from each of the workbooks within the folder (full folder path listed in Cell A3).
0
 
Rowby GorenAuthor Commented:
Hi

Ran the command and saw a bunch of activity.  Ran smoothly.  Ended with "Process completed successfully".


But cannot find a "master" file in the folder.  All of the files are  the same size (KB) as before.

Is there a new excel filename I should be looking for?  I assume it should be in the same folder as the other files?

FYI here is the folder info I put in Cell A3

C:\convert\9-13 files to combine and print\

BTW I have the macro file in a separate folder:  C:\convert\9-13 files to combine and print\special folderfor combining macro



Thanks!

Rowby
0
 
Rowby GorenAuthor Commented:
AH, I see now.  They were all added as separate tabs in the macro workbook.

I thought it would be appended into one long spreadsheet.

But this might work for what I am doing.  

However, just in case,  is there a way to make all of the spreadsheets one new long spreadsheet?

Rowby
0
 
BullmanTechCommented:
Let me know if this definitely doesn't work for you and we can go down that path only we have to.
0
 
Rowby GorenAuthor Commented:
Stay tuned.  Am trying it in a few min!  :)
0
 
Rowby GorenAuthor Commented:
Well, I can't seem to get Microsoft Publisher to import individual tabs.  It's only accepting one tab at a time.  I guess I could live with it.

However if there is a way to combine  it so where one excel file ends, the next one is appended to the bottom of that one.   Making one long file.

Again if it's more trouble that it's worth I suppose I could import one table / tab at a time.

Rowby
0
 
Rowby GorenAuthor Commented:
Hi

For my current situation your solution will be okay.

However for the future,  is there a way to  to combine so where one excel file ends, the next one is appended to the bottom of that one.   Making one long file.
0
 
Rowby GorenAuthor Commented:
Hi  

Update.

As I attempt to merge the files into Microsoft publisher I am running into problems with having to do  the individual tabs.  

So even if it requires me to take extra steps, I will need a way to combine all of the excel sheets into one long 'spreadsheet" or similar datasourc.e


For example I have Access, if that's a way to combine the excel sheets and then export them, for example, to a csv file for import as one big file into Publisher.

Rowby
0
 
BullmanTechCommented:
I won't be by a computer until Monday, so I won't be able to get you any code to accomplish that, but I am quite confident there is a suitable solution somewhere on Experts Exchange, so if you search for "combine multiple Excel worksheets," you should be able to find what you need.

Otherwise, I can help next week.
0
 
Rowby GorenAuthor Commented:
Hi BullmanTech...  I've been googling it this morning and have found some solutions.

Stay tuned I'll share the results when you get back.

And will award you the points for helping me troubleshoot this!

Rowby
0
 
BullmanTechCommented:
Did you find a solution?
0
 
Rowby GorenAuthor Commented:
Yes.

This one worked for me.  See if you have any modifications for the future, but otherwise it works fine.

BTW this works after I have used your code to combine them all into one workboook.

Rowby


Sub ConsolidateSheets()
Dim TargetSh As Worksheet
Dim DestCell As Range
Dim LastRow As Long
Dim sh As Worksheet

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Set TargetSh = Worksheets("Consolidated")
On Error GoTo 0
If TargetSh Is Nothing Then
    Set TargetSh = Worksheets.Add(before:=Sheets(1))
    TargetSh.Name = "Consolidated"
Else
    TargetSh.Cells.Clear
End If
Set DestCell = TargetSh.Range("A1")
Sheets(2).Range("A1:AZ1").Copy DestCell 'copy header
Set DestCell = DestCell.Offset(1)

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "Consolidated" Then
        LastRow = sh.Cells.SpecialCells(xlCellTypeLastCell).Row
        sh.Range("A2", sh.Range("AZ1" & LastRow)).Copy Destination:=DestCell
        Set DestCell = DestCell.Offset(LastRow - 1)
    End If
Next

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window

0
 
BullmanTechCommented:
Glad to hear it worked!
0
 
Rowby GorenAuthor Commented:
Thanks  BullmanTech!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 13
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now