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

Office 2010

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

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 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.

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

  1. Start Excel 2011 for the Mac.

  2. Click Tools, click Macro, and then click Visual Basic Editor to open the Visual Basic Editor.

  3. Click Insert and then click Module to insert a standard module.

  4. 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
    
    

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

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

  1. 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
    
    TipTip

    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.

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

    2. 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
      
  2. To open the macro dialog box and run the macro, click Tools, click Macro, and then click Macros.

    Note 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.

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

Note 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

  1. 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
    
  2. To open the macro dialog box and run the macro, click Tools, click Macro, and then click Macros.

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)
Caution noteCaution

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.

NoteNote

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

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.

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.

Show:
© 2014 Microsoft