 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
Briefly! RDBMerge is a user friendly way to Merge Data from Multiple
Excel Workbooks,
csv and xml files into a Summary Workbook.
1) Download the correct version and extract it to a
local directory.
2) Copy RDBMerge.xla(m) to a
unprotected directory on your system.
Tip: Use
one folder for all your add-ins (easy to backup your add-ins this way)
3) Start Excel and open a workbook.
Excel 97-2003
Click Tools, click Add-Ins, use "Browse" to go to
the add-in and then
click OK. Verify RDBMerge is checked in the add-in
list and then click OK.
Excel 2007-2016
2007:
Click the Microsoft Office Button, click Excel Options, click the Add-Ins
tab.
2010-2016: Click on File, click on Options, click the Add-ins tab.
In the Manage drop-down, choose Excel Add-ins, and click Go. Use “Browse” to
go to the add-in and then click on OK. Verify RDBMerge is checked in the
add-in list and then click OK.
In Excel 2007-2016 you find a button named 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.
Folder Location:
In the folder location section click
on the Browse button and select the folder with the files you want to merge.
After you do that you see the path returned in the UserForm.
Which Files:
In the section "Which Files" select the type of
files in the dropdown that you want to merge. If you use the first option
"XL?" it will merge all type of Excel files with an extension that start
with XL (xls, xlsx, xlsm, xlsb). But you can also only merge files with a
specific extension in the folder.By default the option to merge all files in
the folder is selected but you can also use the option to filter on the file
names, click on the Tips button for more information. In the Add-in for
Excel 2007-2013 there is also an option to select the files you want, easy
if you do not want to merge all the files in the folder.
Which worksheet(s):
In this section you have the option to
choose the worksheet by index or name. If you use the index 1 it will use
the first worksheet in each file, you not have to know the worksheet name
this way. You can also merge data from all worksheets or use the filter
option to filter on the worksheet names, click on the Tips button for more
information.
Which range:
You have the option to
merge a fixed range (can be more than one area). Click on the Tips button
for more information. Or use the First cell ? till last cell on worksheet
option, you can change the start cell (default = A1). Use A2 for example if
you not want to copy your header each time.
Different
options:
Before we press on the Merge button we have a few
options here that you can change.
1: The Add file
name checkbox will add the file name or file and sheet name before or above
your data.
You can use it for example to filter the Combine sheet for the
data from a certain file.
2: To avoid problems with
formulas I suggest you check Paste as values.
3: If
your workbooks have links to other workbooks use the UpdateLinks option to
update the values.
4: Use the Paste data next to
each other option if you want paste the data from each file next to each
other instead of below each other. If you set your fixed range for example
to A1:A100 and check this check box it will use one column for each file in
the combine sheet. If you range have two columns it will use two columns for
each file in the combine sheet.
5: If your workbooks
have an 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.
Click on the Merge button:
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.
You see it is very easy to work with this add-in, testing all options
with a few files in a test folder is the best way to get familiar with this
add-in. If you have problem or suggestions let me know and I try to help
you.
Note: If you merge XML files it is possible
that you get a warning that Excel will be creating its own schema for this
schema-free document, check the checkbox "In the futere, do not show this
message" to stop this.
Note: Download the add-in that is correct for your Excel version, If you want a Mac version of the add-in check out the Mac section on my website. Note: version 1.4 is working in Excel 2007-2016
Excel 2007-2016 version 1.4
File date(8-Dec-2010)
Excel 97-2003 version 1.3
File date(1 April-2010)
Note: If you can't find the menu item on the Data tab in the Ribbon and the add-in is checked in the Add-ins dialog see this website for more information : http://peltiertech.com/Utility30/Documentation30/RibbonDisappears.html