Excell File Mereger

RDBMerge Add-in

RDBMerge Add-in Ron de Bruin (last update 5-Feb-2009) Go back to the Excel tips page

Merge data from all workbooks in a folder Install the RDBMerge utility 1. Download and extract it to a local directory. 2. Copy RDBMerge.xla(m) to the following directory: local_drive:\Program Files\Microsoft Office\OfficeNumber\Library Note Depending on the version of Excel, the OfficeNumber directory may named Office or may include a version number. For example: local_drive:\Program Files\Microsoft Office\Office\Library -ORlocal_drive:\Program Files\Microsoft Office\Office11\Library Once installed, to access it: 3. Start Excel and open a workbook. 4. (Excel 97-2003) Click Tools, click Add-Ins, Verify RDBMerge is checked in this list and then click OK. (Excel 2007) Click the Microsoft Office button, click Excel Options, click the Add-Ins tab. In the Manage drop-down, click Excel Add-ins, and click Go. Verify RDBMerge is checked in this list and then click OK.

http://www.rondebruin.nl/merge.htm[4/1/2009 10:28:04 PM]

RDBMerge Add-in

How do you use the add-in ? In Excel 2007 you find a button RDBMerge on the Ribbon on the Data tab to open the userform. In Excel 97-2003 you find the menu option RDBMerge in the Data menu to open the userform. 1) 2) 3) 4)

Use the Browse button to select the folder with the files(sub folders is optional) In the File type combo select the file extension (XL? in the 2007 version = all Excel files) Choose if you want to merge all files in the folder or want to filter (click on the Tips button) In the Which worksheet(s) section choose if you want to use the sheet index or worksheet name of a worksheet in each workbook (sheet index 1 is the first sheet of each workbook). Or Choose if you want to merge all worksheets or want to filter on the worksheet names in each workbook (click on the Tips button for more info) 5) the first option In the Which Range section is Fixed Range. If you fill in a range with more areas it will copy every cell in another column in the same row (like a database record).

http://www.rondebruin.nl/merge.htm[4/1/2009 10:28:04 PM]

RDBMerge Add-in

This example A1,C1:E2,G1,J1:J12 will use 20 columns in the same row. Remember that there is a column limit." If it is one area like A2:G10 it will copy the whole area below the last line with data on the merge worksheet. Tip: If you use A:G it will copy all cells in the UsedRange of column A:G. 6) The second option In the Which Range section is First cell ? till last cell on worksheet. Copy all cells of a worksheet, you can change the start cell (default = A1) Use A2 for example if you not want to copy your header each time. 7) Different options: The Add column with file name checkbox will add the file path/name in column A. To avoid problems with formulas check Paste as values If your workbooks have links to other workbooks use the UpdateLinks option to update the values. If your workbooks have a Open password you can fill in the open/modify password in the two text boxes, it is no problem if there are also workbooks with no password in the folder. When you click on the Merge button it will create a new workbook for you with two worksheets: Combine Sheet with all the data Log Sheet with copy/paste and error information Then it is up to you if you want to save this workbook.

Download Note: Download the add-in that is correct for your Excel version Excel 2007 version 1.21 File date (5-Feb-2009) Change in Version 1.21 for Excel 2007 : Because there is a bug in Workbooks.Open when you open files that are in the new 2007 file format I changed the code in the add-in to avoid problems.

Excel 97-2003 version 1.2 File date (5-May-2008)

More Information For VBA code go to my FSO code page http://www.rondebruin.nl/fso.htm For other code examples see my Dir page http://www.rondebruin.nl/copy3.htm Use ADO to copy from closed workbooks http://www.rondebruin.nl/ado.htm

http://www.rondebruin.nl/merge.htm[4/1/2009 10:28:04 PM]