Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2011 for the Mac
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:
-
Click Excel on the Menu bar.
-
Click Preferences from the menu.
-
In the Excel Preferences window, click Ribbon.
-
In the Ribbon page, scroll down the Customize list, and then select Developer.
-
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:
Programmatically Selecting Files in Excel for Windows and Excel for the Mac
Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2010
Merging Data from Multiple Workbooks into a Summary Workbook in Excel
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.