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
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.
I 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.
Ron -
You're not charging enough!
Hi Mike
Glad that you can use it.
Feedback is welcome
Hi Jon
>You're not charging enough!
How many beer can I charge Jon ?
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.
To 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.
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
http://www.rondebruin.nl/mail/folder2/saveatt.htm
Thanks,
I will definitely find some use for that,
(wish I had it two weeks ago)
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!
Hi Ron,
Great 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
Maybe this code example is a start
http://www.rondebruin.nl/copy4.htm
There is a worksheet copy macro also
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
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!!
Thanks Ron,
But How to merge Microsoft Office all file types? Like a txt & xml?
btw thanks again Ron
Hi adprim
For txt files see
http://www.rondebruin.nl/csv.htm
Or
http://www.rondebruin.nl/txtcsv.htm
You can adapt the code on my site for XML files
Maybe the next version I will add it to the add-in
http://www.rondebruin.nl/copy3.htm
XML support in RDBMerge add-in will be great. I spent a lot of time on merging xml files in the past. Or I lost a lot of time because of different schemes and pain to understand how to handle them in Excel.
Hello. Sorry for a question which may be outdated with introducti of RDBMerge add-in but maybe you could still help me.
While using Ron's "Merge cells from all or some worksheets into one Master sheet" method I would like to copy aslo data which were filtered (I mean actually those which dont appear any more after filter use)in worksheets to appear in the Master sheet.
thnanks for any suggestion
Hi Tomas
This is not a easy macro.
Can't you change the filter in the code so that
we can copy the visible data ?.
Send me a test workbook private and I look at it this week.
You find my mail address on my site
Thanks Ron,
I solved it by first removing the filter, merging the sheets and applying the filter again. Maybe bit breakneck but it's working.
Thanks so much for this - I was about to write a macro to do this myself, but thankfully I found your (far more feature-laden) add-in. It cuts hours out of my week!
It is almost like magic!! Will save me tons of time!!
Thank you! I'm a psychology graduate student compiling data and this saved me hours of time.