Merging Data from Multiple Workbooks into a Summary Workbook in Excel 2010
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:
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
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.
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.
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.
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.
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.
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 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:
> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/ac6b9785-f876-43ef-83c7-0cd8b9adc55b]