Merge data from different workbooks with the RDBMerge add-in
Hi all
For some reason I can’t add comments to my old post, that’s why this new post.
A few months back I create a small new Add-in named RDBMerge to merge data from
all workbooks in a folder. I update the add-in last week.
http://www.rondebruin.nl/merge.htm
Changes are:
1) You can filter the file names with wildcards
2) You can filter the worksheet names with wildcards
In the next version I will add a option to copy the data next to each other and maybe a autofilter option
to filter the records you want in each workbook.
Do you have other suggestions ?
On my site you also find VBA code examples to do the same and more.
Ron de Bruin
http://www.rondebruin.nl/tips.htm
Mike Alexander:
Third times a charm. DK feel free to clean up the test posts. Sorry.
Any way Ron,
Fantastic tool. The environment I work is deluged with processes where the merging of multiple workbooks are a weekly occurance. We often use a generic procedure to merge the file such as
Dim MyFiles As String
' Specify a target directory
MyFiles = Dir$("D:\Documents and Settings\gzgkpp\Desktop\*.xls")
Do While MyFiles ""
'Open Workbooks one by one
Workbooks.Open "D:\Documents and Settings\gzgkpp\Desktop \" & MyFiles
'Copy the Source Data
Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Open the destination file
Workbooks.Open Filename:="C:\Temp.xls"
'Select next available row and paste copied data
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Close and Save the destination file
ActiveWorkbook.Save
ActiveWindow.Close
'Close the source file without saving
Workbooks(ActiveWorkbook.Name).Close SaveChanges:=False
'Next File in the Directory
MyFile = Dir()
Loop
End Sub
Now RDBMerge gives my team more options that the generic code.
4 October 2007, 1:26 pmI have some suggestions for additional features that I'll post later. Just wanted you to know that we will definately be using it here in our analytical services group.
Jon Peltier:
Ron -
You're not charging enough!
4 October 2007, 2:28 pmRon de Bruin:
Hi Mike
Glad that you can use it.
Feedback is welcome
Hi Jon
>You're not charging enough!
5 October 2007, 9:01 amHow many beer can I charge Jon ?
am8421:
As a regular user of the add-in and some tips from your site I would suggest:
1 - option to open files that are write-password protected, but with no password to open. Otherwise you need to confirm many times that you want to open file in read-only mode.
2 - if they are password protected to be opened, option to put the password only once (certainly, if it's the same for all).
3 - option to merge named ranges.
4 - merge only the data or data with original formatting.
5 - option to choose merging in one worksheet or in many worksheets in a output workbook.
It's going to become swiss army knife... Anyway, I'm interested how other users get the data to be merged. It's no problem when you have all files on a local disk or LAN, but when you need to collect the data from many people in different locations, you are in trouble. When I used mail, I received lot of them with exactly the same name, wrong mail subject or something else. I tried Webform (it was only in Excel 97) but with no success. It crashes in newer vesions. Perhaps it's an idea for a separate post.
8 October 2007, 7:35 amTo make thing more difficult - for some documents I can't use VBA. They are used also by those who work with other spreadsheets software (Open Office is most popular). I try to program the workbook only with formulas but not all of them work in other spreadsheets.
Ron de Bruin:
Hi am8421
Thanks for your suggestions
Passwords is already working in my tester
For mail attachments I use something like this to get them in a folder
8 October 2007, 2:35 pmhttp://www.rondebruin.nl/mail/folder2/saveatt.htm
alderaic:
Thanks,
I will definitely find some use for that,
(wish I had it two weeks ago)
9 October 2007, 2:08 pmFrank Szalai:
Thank you for this great tool, I was looking through a lot of sites to find a solution.
Your solution was custom tailored and it works great.
I will use your site to learn new things!
11 October 2007, 7:14 amtammyl:
Hi Ron,
17 December 2007, 6:54 pmGreat tool. In addition I need to copy/move sheets across multiple excel instances.
Looking to select workbook/worksheet to move, then select workbook for insert.
Search Hi/Low can't find a solution. Perhaps your add-in could handle this option.
Any Help appreciated.
Cheers
Tammy
Ron de Bruin:
Maybe this code example is a start
http://www.rondebruin.nl/copy4.htm
There is a worksheet copy macro also
19 December 2007, 10:13 amRon de Bruin:
Update: Version 1.2 is ready
Hi all
I update my merge add-in today
http://www.rondebruin.nl/merge.htm
New are:
If your workbooks have a Open password you can fill in the open/modify password in the
two text boxes on the userform
If your workbooks have links to other workbooks you can use the UpdateLinks option to update the values.
There is option to copy a range with more areas now
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).
And I fixed a few bugs
Have fun and if you have problems let me know
29 April 2008, 1:01 pmDJ:
Hi Ron,
I used your Add-in on a PDF file that I scanned, OCRed and saved as an Excel spreadsheet. It took the 200 page PDF file and divided it over 200 tabs! No way was I going to waste time copying and pasting 200 worksheets. So I searched the web for a macro/vba or formula to combine all tabs in the workbook onto one sheet. After an hour of searching discussion boards (and trying various macros) I finally found your addin. Your RDBmerge worked perfectly!!
Thanks for creating such a great add-in! Kudos!!
21 June 2008, 3:55 am