Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2010

Article
07/24/2014

Office Visual How To

Summary: Learn how to use VBA to take data from multiple workbooks and merge them into one summary workbook in Microsoft Excel 2010 or Microsoft Excel 2007.

Applies to: Excel 2007 | Excel 2010 | Office 2007 | Office 2010 | VBA

Published: January 2011

Provided by: Peter Gruenbaum, SDK Bridge, LLC

Some users store data in multiple worksheets. Often, it is useful to merge specific data elements into one workbook. However, merging data manually can be time-consuming. You can use VBA code to automate this task.

This Visual How To explains how to:

Merge data from all files in a specified folder.
Merge data from selected files in a specified folder.
Apply advanced VBA techniques in merging.

This Visual How To is based on Merging Data from Multiple Workbooks into a Summary Workbook in Excel by Ron de Bruin and Frank Rice. The code examples included in this Visual How To are simplified versions of code examples in that article.

Consider the scenario of a small service company that creates an Excel workbook for each invoice that they create. The company has a folder that contains all invoices, and they want to summarize the invoice data in one workbook. The invoices each have a summary box that has information in cells A9 through C9.

This section will describe how to use VBA to take the values of the summary cells from each of the invoices and put the values into a new workbook.

Enabling the Developer Tab

You will use the Developer tab to access the Visual Basic Editor and other developer tools. However, Office 2010 does not display the Developer tab by default. Use the following procedure to enable the Developer tab.

To enable the Developer tab

On the File tab, select Options to open the Options dialog box.
Click Customize Ribbon on the left side of the dialog box.
Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop-down list, and then select the Developer check box.
Click OK.

Note

In the 2007 Microsoft Office system, use the Office Button to open the Options dialog box. Under the Popular category, click Show Developer tab in the Ribbon.

After you enable the Developer tab, click the Macro button on the Developer tab, type in MergeAllWorkBooks as the macro name, and then click Create.

Note

In all recent versions of Excel, you can use the shortcut keys Alt+F8 to open the Macro Dialog box.

Merging all Workbooks in a Folder

The following code example creates a new workbook. The code then takes each Excel workbook in the folder C:\Users\Peter\invoices and copies information from cells A9 through C9 into the new workbook. In addition, the code puts the workbook file name in row A.

To loop through all of the files in the folder, use the Dir function. The first time that you call Dir, enter the path of the files that you want as an argument and retrieve the first file. Next, call Dir without arguments, and you retrieve the next file. When there are no more files, the process returns an empty string.

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1
    
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    
    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
        
        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
        
        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    
    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

The new workbook that you create with this code example will contain a row for each file in the directory. Column A will contain the file name and columns B through D will contain the information in cells A9 through C9 from the first sheet of each workbook.

To configure this macro, change the value that FolderPath is set to and change the range where SourceRange is set.

Merging Selected Workbooks in a Folder

Now, you can modify the code so that a user could select certain files in that folder. To do this, you must present an Open File dialog box and display the files in your folder. Set the current directory to be your folder path. Set the directory with the ChDrive and ChDir functions.

Note

Using ChDrive and ChDir will not work for network drives. To set the current directory for network drives, call the Windows function SetCurrentDirectoryA, which is illustrated in Merging Data from Multiple Workbooks into a Summary Workbook in Excel.

The code uses Application.GetOpenFilename to open the file dialog box, which has the filters set to view Excel workbooks only. GetOpenFileNames returns an array of Variant objects, which are objects that can be any type. In this case, GetOpenFilenames returns an array of strings, one for each selected file name.

Sub MergeSelectedWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    
    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"
    
    ' Set the current directory to the the folder path.
    ChDrive FolderPath
    ChDir FolderPath
    
    ' Open the file dialog box and filter on Excel files, allowing multiple files
    ' to be selected.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1
    
    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)
        
        ' Open the current workbook.
        Set WorkBk = Workbooks.Open(FileName)
        
        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName
        
        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
        
        ' Set the destination range to start at column B and be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)
           
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
    Next NFile
    
    ' Call AutoFit on the destination sheet so that all data is readable.
    SummarySheet.Columns.AutoFit
End Sub

Running the macro will open a file dialog box. Use the shortcut keys Ctrl+A to select all the files or click with the Ctrl key pressed to select multiple files. You may also click with the Shift key pressed to select a range of files.

As before, you can modify this macro for your own purposes. To do this, change the value that FolderPath is set to and change the range where SourceRange is set.

Copying a Range that Extends Down to the Last Row

In the previous examples, you knew exactly which rows to copy. However, you may want to copy cells from a starting row down to the last row where you do not know what row is the last. The following example shows how to modify the code so that the macro copies columns A through K, from row 8 to the last row.

To copy all rows, you first have to find the last row. You can find the last row with the Find method, which returns a range of cells that match a specified criterion. In this case, the code starts at the bottom of the worksheet and searches upward, row-by-row, until the code finds a cell that is not blank. Replace the line that sets the variable SourceRange to the following code example.

    Dim LastRow As Long
    LastRow = WorkBk.Worksheets(1).Cells.Find(What:="*", _
                 After:=WorkBk.Worksheets(1).Cells.Range("A1"), _
                 SearchDirection:=xlPrevious, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows).Row
    Set SourceRange = WorkBk.Worksheets(1).Range("A8:K" & LastRow)

To find any cell that is not blank, set the What argument to the wildcard "*" and the LookIn argument to xlFormulas. To start from the bottom up, set the After argument to the first cell and the SearchDirection argument to xlPrevious, which will cause the search to wrap around to the bottom. To search by rows, set the SearchOrder argument to xlByRows. Finally, use the Row property to find the row number of the last row. Then, use the row number to define a range that starts at A8 and goes to column K and the last row number.

In this case, you know that the last column is K. If you do not know the last column, use the RDB_Last function in Merging Data from Multiple Workbooks into a Summary Workbook in Excel, which can provide the last column, last row, or last cell.

The code examples in this Visual How To are simplified. In contrast, the article written by de Bruin contains more complex VBA code examples. His code examples check for when files are not found, when the target workbook has insufficient rows, and other errors. In particular, de Bruin includes code for error handling so that if you are processing many files and one fails, the code will not fail. Consider the following code example that opens a workbook.

On Error Resume Next
     Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
     On Error GoTo 0

On Error Resume Next means that if an error occurs when you open the workbook, the code will go to the next line. On Error GoTo 0 returns error handling to the default condition.

De Bruin’s code examples also provide a better user experience by turning off screen updating and disabling events. CalcMode in the application is set to manual calculation. However, the code captures the original CalcMode state and restores the value at the end of the subroutine.

Filtering the Data

Another useful example in the de Bruin article is a sample that uses a filter. The filter enables you to limit which cells are copied into the summary workbook. Once the application finds a source range, the code turns off the autofilter of its parent range. Then, the code sets the range autofilter to look in a specific column for cells that match a specific criteria. (In his example, the cells have the value "ron".) If the cell in that column fits the criteria, the code copies the row into the destination workbook.

Merge Add-In and Other Merge Macros

You may need merge capability, but not have the need or want to write VBA code. In this case, de Bruin has created a utility for merging in Excel that is in the form of an add-in called RDB_Merge. His web page contains instructions for downloading, installing, and using the add-in.

Also, de Bruin's website has several VBA Macros that cover various merge situations. These macros include the following:

Merging a range from each worksheet in multiple workbooks.
Merging whole worksheets from each workbook.
Merging filter results from a sheet from each workbook.
Merging data from files in a folder and its subfolders.

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/ac6b9785-f876-43ef-83c7-0cd8b9adc55b]

Length: 00:07:52

Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2010

To enable the Developer tab

Additional resources