RDBMerge Add-in
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
-OR-
local_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.
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) Use the Browse button to select the folder with the files(sub folders is optional)
2) In the File type combo select the file extension (XL? in the 2007 version = all Excel files)
3) Choose if you want to merge all files in the folder or want to filter (click on the Tips button)
4) 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).
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
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
-OR-
local_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.
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) Use the Browse button to select the folder with the files(sub folders is optional)
2) In the File type combo select the file extension (XL? in the 2007 version = all Excel files)
3) Choose if you want to merge all files in the folder or want to filter (click on the Tips button)
4) 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).
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