Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2011 for the Mac

Article
07/24/2014

Summary: Learn how to merge data from multiple workbooks into a summary workbook in Excel 2011 for the Mac.

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

In this article
Introduction to Merging Excel Workbooks
Custom Helper Functions
Determining the Current Operating System
Merging Range Data from all Workbooks in a Folder and Subfolders by Row
Merging Range Data from Multiple Workbooks by Column
Helpful Tips
Conclusion
Additional Resources
About the Authors

Published: August 2012

Updated: February 2013

Provided by: Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation

Contents

Introduction to Merging Excel Workbooks
Custom Helper Functions
Determining the Current Operating System
Merging Range Data from all Workbooks in a Folder and Subfolders by Row
Merging Range Data from Multiple Workbooks by Column
Helpful Tips
Conclusion
Additional Resources
About the Authors

Introduction to Merging Excel Workbooks

When you work with multiple Microsoft Excel workbooks, you must often roll-up or merge the data from two or more workbooks into a master workbook. The procedures and commands that you use work differently, depending on whether you are working on an Apple Mac or in Windows.

Note

For examples of how to merge multiple Excel workbooks in Windows (manually or programmatically), see the Additional Resources at the end of this article. Additionally, I have created an Excel add-in that provides a user-friendly way to merge data from multiple Excel workbooks into a summary workbook by using a comprehensive user interface.

The Dir command that is used on a Mac does not work the same as it does on a computer that is running Windows. For example, you cannot use a filter and wildcard characters on a Mac to return all Excel files, such as in the following statement: FilesInPath = Dir(folderpath & "*.xl*")

Similarly, you cannot use the following statement to return.xlsm files only: FilesInPath = Dir(folderpath & "*.xlsm")

The examples in this article programmatically consolidate the data from multiple workbooks into a summary workbook in Microsoft Excel 2011 for the Mac by using Microsoft Visual Basic for Applications (VBA). The different procedures demonstrate techniques that you can use to add the data by row or by column. These examples use Apple script to filter on the file name extension(s) and to specify whether subfolders are included.

Custom Helper Functions

The procedures to merge data from rows and columns detailed in this article use a couple of custom helper functions. For example, the following statement is in every code example: Call GetWorkbooksOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=1, FileNameFilterStr:= "Profit")

This custom function calls the GetWorkbooksOnMacWithOrWithoutSubfolders function that enables you to specify a file name extension filter and search scope where ExtChoice and Level have the following values.

ExtChoice:=0 Search for .xls, .xlsx, .xlsm, .xlsb files.
ExtChoice:=1 Search only for .xls files.
ExtChoice:=2 Search only for .xlsx files.
ExtChoice:=3 Search only for .xlsm files.
ExtChoice:=4 Search only for.xlsb files.
ExtChoice:=5 Search for .csv files.
ExtChoice:=6 Search for .txt files.
ExtChoice:=7 Search for all files with extension.
ExtChoice:=8 Search for .xlsx, .xlsm, .xlsb files.
ExtChoice:=9 Search for .csv and .txt files.

You can add more filter options in the function as desired.

Level:=1 Search only for the files in the single-level folder that you select.
Level:=2 Search Level 1 plus files in the first level subfolder.
Level:=3 to x Level 1 plus files in x subfolders.

FileFilterOption:=0 No filename filter is used.
FileFilterOption:=1 The filename begins with the string specified in the FileNameFilterStr parameter.
FileFilterOption:=2 The filename ends with the string specified in FileNameFilterStr parameter.
FileFilterOption:=3 The filename contains the string specified in the FileNameFilterStr parameter.
FileNameFilterStr Specifies the string value to use if the FileFilterOption is set to option 1, 2, or 3.

Thus, the previous statement returns all .xls, .xlsx, .xlsm, and .xlsb files in the single-level folder that you select (no subfolders) that begin with the string "Profit".

Another function that is used here is RDB_Last, which enables you to find the last cell, column, or row in a range of data.

To add these functions to a Visual Basic project, follow these steps.

To add the custom functions to a standard module

Start Excel 2011 for the Mac.
Click Tools, click Macro, and then click Visual Basic Editor to open the Visual Basic Editor.
Click Insert and then click Module to insert a standard module.

Paste or type the following functions into the module. After you have finished, press CMD + q, or, on the Excel menu, click Close and Return to Microsoft Excel to close the Visual Basic Editor and return to Excel.

Function GetFilesOnMacWithOrWithoutSubfolders(Level As Long, ExtChoice As Long, _
                                              FileFilterOption As Long, FileNameFilterStr As String)
'Ron de Bruin,Version 2 : 9 Nov 2012
'http://www.rondebruin.nl/mac.htm
'Thanks to DJ Bazzie Wazzie(poster on MacScripter) for his great help.
    Dim ScriptToRun As String
    Dim folderPath As String
    Dim FileNameFilter As String
    Dim Extensions As String

    On Error Resume Next
    folderPath = MacScript("choose folder as string")
    If folderPath = "" Then Exit Function
    On Error GoTo 0

    Select Case ExtChoice
    Case 0: Extensions = "(xls|xlsx|xlsm|xlsb)"  'xls, xlsx , xlsm, xlsb
    Case 1: Extensions = "xls"   'Only  xls
    Case 2: Extensions = "xlsx"  'Only xlsx
    Case 3: Extensions = "xlsm"  'Only xlsm
    Case 4: Extensions = "xlsb"  'Only xlsb
    Case 5: Extensions = "csv"   'Only csv
    Case 6: Extensions = "txt"   'Only txt
    Case 7: Extensions = ".*"    'All files with extension, use *.* for everything
    Case 8: Extensions = "(xlsx|xlsm|xlsb)"  'xlsx, xlsm , xlsb
    Case 9: Extensions = "(csv|txt)"   'csv and txt files
        'You can add more filter options if you want.
    End Select

    Select Case FileFilterOption
    Case 0: FileNameFilter = "'.*/[^~][^/]*\\." & Extensions & "$' "  'No Filter
    Case 1: FileNameFilter = "'.*/" & FileNameFilterStr & "[^~][^/]*\\." & Extensions & "$' "    'Begins with
    Case 2: FileNameFilter = "'.*/[^~][^/]*" & FileNameFilterStr & "\\." & Extensions & "$' "    ' Ends With
    Case 3: FileNameFilter = "'.*/([^~][^/]*" & FileNameFilterStr & "[^/]*|" & FileNameFilterStr & "[^/]*)\\." & Extensions & "$' "   'Contains
    End Select

    folderPath = MacScript("tell text 1 thru -2 of " & Chr(34) & folderPath & _
                           Chr(34) & " to return quoted form of it's POSIX Path")
    ScriptToRun = ScriptToRun & _
                  "set streamEditorCommand to " & _
                  Chr(34) & " |  tr  [/:] [:/] " & Chr(34) & Chr(13)
    ScriptToRun = ScriptToRun & _
                  "set streamEditorCommand to streamEditorCommand & " & _
                  Chr(34) & " | sed -e " & Chr(34) & "  & quoted form of (" & _
                  Chr(34) & " s.:." & Chr(34) & _
                "  & (POSIX file " & Chr(34) & "/" & Chr(34) & "  as string) & " & _
                  Chr(34) & "." & Chr(34) & " )" & Chr(13)
    ScriptToRun = ScriptToRun & "do shell script """ & "find -E " & _
                  folderPath & " -iregex " & FileNameFilter & "-maxdepth " & _
                  Level & """ & streamEditorCommand without altering line endings"

    On Error Resume Next
    MyFiles = MacScript(ScriptToRun)
    On Error GoTo 0
End Function

Insert the following function as well.

Function RDB_Last(choice As Integer, rng As Range)
'Ron de Bruin, 5 May 2008
'Case 1 = last row
'Case 2 = last column
'Case 3 = last cell
    Dim lrw As Long
    Dim lcol As Integer

    Select Case choice

    Case 1:
        On Error Resume Next
        RDB_Last = rng.Find(What:="*", _
                            after:=rng.Cells(1), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        RDB_Last = rng.Find(What:="*", _
                            after:=rng.Cells(1), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       after:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        after:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        RDB_Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            RDB_Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
    End Select
End Function

Determining the Current Operating System

If you are not sure whether the workbook will open in Excel for Windows or Excel for the Mac, use the following macro to run the correct code in your workbook. This VBA code tests for the current operating system and version of Excel.

Sub WINorMAC()
'Test for the current operating system.
    If Not Application.OperatingSystem Like "*Mac*" Then
        'OS is Windows
        Call My_Windows_Macro
    Else
        'OS is Mac so test to see if you are using Excel 
        '2011 or later.
        If Val(Application.Version) > 14 Then
            Call My_Mac_Macro
        End If
    End If
End Sub

You can also use conditional compiler constants to test for the operating system, such as in the following procedure.

Sub WINorMAC_2()
'Test with conditional compiler constants.
    #If Win32 Or Win64 Then
        'The operating system is Windows
        Call My_Windows_Macro
    #Else
        'The operating system is Mac so test to see if you are using Excel 
        '2011 or later.
        If Val(Application.Version) > 14 Then
            Call My_Mac_Macro
        End If
    #End If
End Sub

Merging Range Data from all Workbooks in a Folder and Subfolders by Row

To merge data from all the workbooks that are contained in a specified folder or group of subfolders, use the following procedure. The ranges are concatenated into the target worksheet in rows, one after another.

Note

Be sure to read the information and the tips that follow the macro.

To add the code to merge all workbooks in a folder

In a standard module, paste or type the following code, including the two statements above the macro.

Option Explicit
Public MyFiles As String


Sub MacMergeCode()
    Dim BaseWks As Worksheet
    Dim rnum As Long
    Dim CalcMode As Long
    Dim MySplit As Variant
    Dim FileInMyFiles As Long
    Dim Mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim SourceRcount As Long

    ActiveWindow.WindowState = xlNormal

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    BaseWks.Range("A1").Font.Size = 36
    BaseWks.Range("A1").Value = "Please Wait"
    rnum = 3

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Clear MyFiles to be sure that it not return old info if no files are found
    MyFiles = ""

    'Get the files, set the level of folders and extension in the code line below
    Call GetFilesOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=0, FileNameFilterStr:="")
    'Level              :  1= Only the files in the folder you select, 2 to ? levels of subfolders
    'ExtChoice          :  0=(xls|xlsx|xlsm|xlsb), 1=xls , 2=xlsx, 3=xlsm, 4=xlsb, 5=csv, 6=txt, 7=all files, 8=(xlsx|xlsm|xlsb), 9=(csv|txt)
    'FileFilterOption   :  0=No Filter, 1=Begins, 2=Ends, 3=Contains
    'FileNameFilterStr  : Search string used when FileFilterOption = 1, 2 or 3

    ' Work with the files if MyFiles is not empty.
    If MyFiles <> "" Then

        MySplit = Split(MyFiles, Chr(10))
        For FileInMyFiles = LBound(MySplit) To UBound(MySplit) - 1

            Set Mybook = Nothing
            On Error Resume Next
            Set Mybook = Workbooks.Open(MySplit(FileInMyFiles))
            On Error GoTo 0

            If Not Mybook Is Nothing Then

                On Error Resume Next

                With Mybook.Worksheets(1)
                    Set sourceRange = .Range("A1:G10")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        Mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MySplit(FileInMyFiles)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                Mybook.Close savechanges:=False
            End If

        Next FileInMyFiles
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    BaseWks.Range("A1").Value = "Ready"
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

The MacMergeCode procedure fills a string with the path and name of each workbook in a folder. It then loops through the workbooks, and for each source file, checks the source and target ranges to see whether there are more columns used in the source range than are available in the target range. If this is true, the workbook is skipped and the code moves to the next workbook. If there are more rows in the source range then available in the target range the macro exits.

Next, the procedure copies the path and name of the source workbook into column A. Finally, the values in the source range are copied into the corresponding range in the target workbook, and then the code moves to the next file in the string.

The MacMergeCode procedure uses the first worksheet (index 1) of each workbook. To start with a different worksheet or to use a specific worksheet, change the index number, or change the index to the name of the worksheet with the following statement: mybook.Worksheets("YourSheetName")

You will probably also want to change the range A1:G10, shown in the following segment, to your own range values.

With mybook.Worksheets(1)
    Set sourceRange = .Range("A1:G10")
End With

Tip

If you want to copy from cell A2 to the last cell on the worksheet, use the following steps. For example, you might do this if there are headers in the first row.

Add the following line at the top of the macro: Dim FirstCell As String

Now replace the following code segment…

With mybook.Worksheets(1)         Set sourceRange = .Range("A1:G10")     End With

…with the following code segment.

With mybook.Worksheets(1)        FirstCell = "A2"        Set sourceRange = .Range(FirstCell & ":" & RDB_Last(3, .Cells))        'Test if the row of the last cell is equal to or greater than the row of the first cell.        If RDB_Last(1, .Cells) < .Range(FirstCell).Row Then           Set sourceRange = Nothing        End If     End With

To open the macro dialog box and run the macro, click Tools, click Macro, and then click Macros.
Note

Another way to run the macro in Excel 2011 is to add the Developer tab to the ribbon, if it is not already there, and then click the Macros button on the ribbon. To add the Developer tab, follow these steps:
1. Click Excel on the Menu bar.
2. Click Preferences from the menu.
3. In the Excel Preferences window, click Ribbon.
4. In the Ribbon page, scroll down the Customize list, and then select Developer.
5. Click OK to save the changes.

Merging Range Data from Multiple Workbooks by Column

You can also paste data from the columns in a source workbook to a target workbook.

Note

Change the following line in the code to set the range that you want to merge: Set sourceRange = Mybook.Worksheets(1).Range("A1:A10")

If you did not copy the first macro in this article into your test workbook, you must add the following line as the first statement in the module. This declaration statement must be in one of the modules but not in more than one: Public MyFiles As String

To merge data from columns in the source workbook to the target workbook

Type or paste the following code into a standard module.

Sub MacMergeCodeColumns()
    Dim BaseWks As Worksheet
    Dim Cnum As Long
    Dim CalcMode As Long
    Dim MySplit As Variant
    Dim FileInMyFiles As Long
    Dim Mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim SourceCcount As Long

    ActiveWindow.WindowState = xlNormal

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    BaseWks.Range("A1").Font.Size = 36
    BaseWks.Range("A1").Value = "Please Wait"
    Cnum = 2

    'Change the ScreenUpdating, Calculation and EnableEvents settings
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Clear MyFiles so that it does not return the old data if no files are found.
    MyFiles = ""

    'Get the files, set the level of folders and extension in the code line below
    Call GetFilesOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=0, FileNameFilterStr:="")
    'Level              :  1= Only the files in the folder you select, 2 to ? levels of subfolders
    'ExtChoice          :  0=(xls|xlsx|xlsm|xlsb), 1=xls , 2=xlsx, 3=xlsm, 4=xlsb, 5=csv, 6=txt, 7=all files, 8=(xlsx|xlsm|xlsb), 9=(csv|txt)
    'FileFilterOption   :  0=No Filter, 1=Begins, 2=Ends, 3=Contains
    'FileNameFilterStr  : Search string used when FileFilterOption = 1, 2 or 3

    ' Work with the files if MyFiles is not empty.
    If MyFiles <> "" Then

        MySplit = Split(MyFiles, Chr(10))
        For FileInMyFiles = LBound(MySplit) To UBound(MySplit) - 1

            Set Mybook = Nothing
            On Error Resume Next
            Set Mybook = Workbooks.Open(MySplit(FileInMyFiles))
            On Error GoTo 0

            If Not Mybook Is Nothing Then

                On Error Resume Next

                With Mybook.Worksheets(1)
                    Set sourceRange = .Range("A1:A10")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'If the source range uses all of the rows
                    'then skip this file.
                    If sourceRange.Rows.Count >= BaseWks.Rows.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceCcount = sourceRange.Columns.Count

                    If Cnum + SourceCcount >= BaseWks.Columns.Count Then
                        MsgBox "There are not enough columns in the sheet."
                        BaseWks.Columns.AutoFit
                        Mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in the first row.
                        With sourceRange
                            BaseWks.Cells(1, Cnum). _
                                    Resize(, .Columns.Count).Value = MySplit(FileInMyFiles)
                        End With

                        'Set the destination range.
                        Set destrange = BaseWks.Cells(2, Cnum)

                        'Copy the values from the source range
                        'to the destination range.
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        Cnum = Cnum + SourceCcount
                    End If
                End If
                Mybook.Close savechanges:=False
            End If

        Next FileInMyFiles
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    BaseWks.Range("A1").Value = "Ready"
    'Restore the ScreenUpdating, Calculation and EnableEvents settings.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

To open the macro dialog box and run the macro, click Tools, click Macro, and then click Macros.

Helpful Tips

If your workbooks are password protected, you can replace the Workbooks.Open arguments in the previous code samples with the following code to open them.

Set Mybook = Workbooks.Open(Filename:=MySplit(FileInMyFiles), _
     Password:="your password", WriteResPassword:=" your password ", UpdateLinks:=0)

Warning

Although you can set permissions on the workbook to restrict who can open it, placing your password in code this way makes it available to anyone who can open your workbook and the Visual Basic Editor.

If you have links in your workbook to other workbooks, use the setting UpdateLinks:=0 to avoid the prompt that asks whether you want to update the links. Use the value 3 if you do want to update the links.

Note

If you set the value to 3, do not set the Calculation property to manual (xlCalculationManual).

Conclusion

This article discusses two code procedures that enable you to merge data from one or more workbooks in a folder and subfolders into a master workbook in Excel for the Mac. The samples also illustrate the differences between filtering for files in Excel on Windows and filtering for Excel on the Mac. Finally, the article specifies how to determine the current operating system. Exploring and implementing these tools in your own applications can make your solutions more versatile.

Additional Resources

For more information about the concepts and techniques discussed in this article, see the following:

About the Authors

Ron de Bruin is an Excel Most Valuable Professional (MVP) and a frequent contributor to the newsgroups. For more information, see Ron's Excel webpage.

Frank Rice is a senior programming writer and frequent contributor to the Microsoft Office Developer Center.

Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2011 for the Mac

Introduction to Merging Excel Workbooks

Custom Helper Functions

To add the custom functions to a standard module

Determining the Current Operating System

Merging Range Data from all Workbooks in a Folder and Subfolders by Row

To add the code to merge all workbooks in a folder

Merging Range Data from Multiple Workbooks by Column

To merge data from columns in the source workbook to the target workbook

Helpful Tips

Conclusion

Additional Resources

About the Authors

Additional resources