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.
I have installed your add-in, but I have a question. Previously when my company was on Microsoft 97/2003 we had a macro that would combine multiple workbooks within a folder into 1 workbook so that all of the data could be reviewed at once. Can your add-in do the same thing?
Hi Angel
Yes it can do that
Hi Ron,
I am trying to use your RDBMerge add-in with Excel 2007 but when merging files/worksheets it is only returning the worksheet name in the results. All the data is missing. Is there a way to fix this?
Thank you,
Atalanta
Hi Atalanta
What do you see in the log sheet ?
Hi Ron,
you have done a great job….dear can you please tell me how can we import workbooks along with all worksheets in one new workbook. I used your add-in but it imports only one sheet from workbooks. I am in need to import all worksheets at a time and where worksheet name is same in different workbooks it should be merged under one worksheet else it should be placed on another worksheet. My English is not good hope you will understand my issue.
Thanking you in anticipation
GOD bless you.
SUA
Hi SUA
I will add this option in a new version
I hope I have time in Jan 2010 to make a new version
Dear Ron
Thanks for prompt reply, i will be waiting for your new version. One more suggestion / query, is it possible to import selected data (based on some conditions) from different text files to excel worksheets?. I am searching for this issue on google but didnt find any suitable answer. Actually i want to pick specific error strings from a log file and want the data in excel file for further working.
I am sure your reply will solve my issue.
Many Thank & Regards,
SUA
Check out the filter example in this code example
See example 4
http://www.rondebruin.nl/copy3.htm
Filter example does not contain any information regarding text files. I basically get to fetch data from different text files to make its database, this can be done in simply importing text file in to any software like MS access of Sql Server but issue is this that data it not in proper format and thats why i cant perform text to column option on text file. so can you guide me how to pick selected data based on some conditions from different text files.
Regards,
Ron de Bruin,
I came across your wonderful creation of Merge Add-in. Its an awesome tool and Thank You for sharing. I was so curious to know about Log sheet which is a great check while using. Can you please share how is it been created.
Merry Christmas and have a happy holidays,
Duke.
Hi Duke
The code will add a new log sheet to the workbook and when it loop through the workbooks
For FileNum = LBound(myFiles) To UBound(myFiles)
You can use FileNum for the row number to fill in the info you want
Something like this :
.Cells(FileNum + 3, 1).Value = FileNum
.Cells(FileNum + 3, 2).Value = myFiles(FileNum)
.Cells(FileNum + 3, 3).Value = FileDateTime(myFiles(FileNum))
End With
Ron,
Happy New Year and hope you had a warm and safe celebration too.Thank You SO MUCH for your response.
Regards,
Duke
Ron,
For the creating a “Log Sheet” the above code, do I need to place it in “Get_File_Names” or “Get_Data”.
Sorry for my lack of knowledge.
Thanks,
Duke
You are THE MAN!
Thanks Ron.
I had the same design (different data) in over 100 worksheets across 36 workbooks, this Add-In was EXACTLY what I needed to compile the data into ONE Worksheet so I could continue to massage it.
Thank you again.
hi Duke
I try to make a example for you this weekend
You are using the code from my FSO page, Am I correct ?
Hi Travis
Glad you like it
Hi Duke
Send me a private mail
I will send you a example workbook if I have your address
Ron,
You guesses it right. That’s correct.
Thanks,
Duke
Ron,
I tried to implement you code for “Merging Data from Multiple Workbooks into a Summary Workbook in Excel” and it worked perfectly. Thank you every much.
But is there anyway to save everything in a predefined worksheet instead of new one?
If there is a way …my life is saved.
Thanks again,
Ardic
Hi Ardic
You see this code line in the macro
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
You can change it to this to add the data in a sheet in the Activeworkbook
Set BaseWks = Worksheets(”YoursheetName)
Or
Set BaseWks = ActiveSheet
Thank you Ron!
but this time when I debug the code I have a error in with the line
destrange.Value = sourceRange.Value
Hi Ron,
I am facing challenge in writing the code to Copy data from worksheets in multiple workbooks only if their name conains “SIPOC”. The same is definitely working thru the ADD-IN but can you help me with the code for the same. The sheet name can have SIPOC left, right center anywhere.
Thanks in advance.
Abhilasha
It looks like this, you must loop through the sheets after mybook is open and test the name
SheetFilterString = “*” & “SIPOC” & “*”
For Each sh In mybook.Worksheets
If LCase(sh.Name) Like LCase(SheetFilterString) Then
Your copy code here
When I have time I will add a example to the page.
If you can’t get it to work mail me private and I will help you.
Hi Ardic
Are you sure the sheet name exist ?
Yes, I am sure it exists…Actually code is working for sheets with 5 coloumns but when you have 10 coluomns with gives that error..It start to paste sheets side by side and after a while vertically.
Hi Ardic
Send me one or two of the files and I check it out for you