[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Command to combine many Excel files into one

Posted on 2012-09-13
21
Medium Priority
?
501 Views
Last Modified: 2012-10-11
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
Comment
Question by:Rowby Goren
  • 13
  • 8
21 Comments
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38395694
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38395714
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
 
LVL 5

Accepted Solution

by:
BullmanTech earned 2000 total points
ID: 38396115
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
Independent Software Vendors: 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 9

Author Comment

by:Rowby Goren
ID: 38396137
Thanks BullmanTech!  I will be trying this out later today.

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396199
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38396303
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396387
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38396448
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396625
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396704
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38396732
Let me know if this definitely doesn't work for you and we can go down that path only we have to.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396736
Stay tuned.  Am trying it in a few min!  :)
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396759
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396850
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38396971
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38399465
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38399490
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38406794
Did you find a solution?
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38406859
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
 
LVL 5

Expert Comment

by:BullmanTech
ID: 38406990
Glad to hear it worked!
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 38407087
Thanks  BullmanTech!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 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