Merge data from different workbooks with the RDBMerge add-in

Posted on by Ron de Bruin

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

Posted in Uncategorized

87 thoughts on “Merge data from different workbooks with the RDBMerge add-in”

  1. 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

    Sub CombineFiles()
    Dim MyFiles As String

    ‘ Specify a target directory
        MyFiles = Dir$(“D:Documents and SettingsgzgkppDesktop*.xls”)
        Do While MyFiles  “”

    ‘Open Workbooks one by one
        Workbooks.Open “D:Documents and SettingsgzgkppDesktop “ & 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.

  4. 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.

  7. 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!

  8. 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

  10. 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

  11. 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!!

  12. Thanks Ron,
    But How to merge Microsoft Office all file types? Like a txt & xml?
    btw thanks again Ron

  14. 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.

  15. 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

  16. 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

  17. 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.

  18. 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!

  20. Thank you! I’m a psychology graduate student compiling data and this saved me hours of time.

  21. 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?

  23. 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

  25. 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

  27. 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

  29. 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,

  30. 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.

  31. 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 :

                With LogWks
                    .Cells(FileNum + 3, 1).Value = FileNum
                    .Cells(FileNum + 3, 2).Value = myFiles(FileNum)
                    .Cells(FileNum + 3, 3).Value = FileDateTime(myFiles(FileNum))
                End With

  32. Ron,
    Happy New Year and hope you had a warm and safe celebration too.Thank You SO MUCH for your response.

    Regards,
    Duke

  33. 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

  34. 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.

  38. 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

  39. 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

  40. Thank you Ron!
    but this time when I debug the code I have a error in with the line
    destrange.Value = sourceRange.Value

  41. 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

  42. 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.

  44. 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.

  46. Hi Ron,

    You are a genious in Excel. This tool is very much helpful to me in consolidatin the associates production in my team. One request from my side.

    Is it possible to update the tool for Filter option. so that i can filter on a column based on date?

    Thanks,
    Mohan

  48. Hi Ron,

    My head touches ground when I bow my head. Hats off for your tool. I installed your RDBMerge latest version 1.3 and I am able to complete my work with in minutes which it took hours earlier (Merging of many excel files into one) .

    One suggestion –

    Do you have any plans to include an option in the userform asking according to which file/ sheet column headings the data should be aligned?. The reason why I am asking is because there are certain excel files where the headings are in different columns.

    You are my inspirator
    Thanks
    Murugan

  49. Hi Murugan

    Glad you like it

    About your suggestion :
    Normal you create a macro for this special stuff.

    I will think about it for adding it to the next version

    Thanks for your reply

  50. Awesome tool!

    Was looking for a solution to a problem for past week or so overthe internet… This is the perfect solution, as I don’t need to get into thehassle of VBA

    Thanks a lot!

  52. Hi Ron,

    Super tool, makes our task of merging our client masters into a consolidated master so easy!! Needed help though – when we try including the running of the add-in as a step in another macro, it doesn’t record the steps. We couldn’t figure how to get this add-in to run from within another macro.

    Taruna

  54. Hi Ron !!!

    Thanks a ton for your RDBMerge Add in , but we would like to have a predefine criteria for selecting the files , specific sheet in the macro rather then giving option for the user to select .Pls help

    Aarti

  57. Hi-

    This is potentially a very useful tool, but I’m having trouble getting it to work in my environment: excel 2010 (64-bit), Windows 7. Browsing always returns an empty “No items match your search” regardless of the options I pick. I ran a repair on my Office 2010 in case some registry entry had been corrupted, to no avail. Any suggestions for troubleshooting?

    Thanks
    Ken

  59. Ron,

    Thanks for all of your help. I have a similar question from SUA earlier.
    I have approximately 50 workbooks all with 10 tabs.

    Tab 1
    Tab 2
    Tab 3, etc.

    What I am trying to do is get Tab 1 for each workbook into a new workbook so that all 50 Tab 1’s are in one summary workbook, all 50 tab 2’s are in a summary workbook, etc. Do you know if there is something out there that can help me do this. I tried the RDB Merge Add-in but it was putting everything on one worksheet. I am looking for separate tabs in a summary workbook. Thanks again for all of yourr help.

    Ryan

  61. Ron –

    I installed RDAMerge on my Toshiba laptop running Windows 7. I am unable to use the add in because the dialog box is bigger than my screen and I can’t get to the merge button. What can I do to re-size the box?

  65. Has anyone adapted the ‘Merge data from all workbooks in a folder’ macro to include multiple worksheets? I need to merge not just every workbook in a single folder, but if there is more than one instance of a worksheet with the first 2 letters of the worksheet name starting with “DF” then I need each of those worksheets included in the merge.

  67. Hi Ron,

    Does this add-in work with Excel 2011 for Mac? I downloaded/unzipped the add-in, then added it to my add-ins in excel, but it doesn’t pop up under the data tab in the ribbon.

  68. Not tested but I will soon

    My main machine is also a Mac now so I am trying all Windows code to see if I can get it to work in 2011

    I start this problem page
    http://www.rondebruin.nl/mac.htm

    And I think it will be a very big page after a few months

    I will make a Mac version also of the add-in when I have found fixes for all the bugs I find

  69. Hi Ron

    I have many files (csv or excel with a single sheet) that I need to merge into a single workbook, but each file should go into a separate worksheet.
    Could you please help me?

    (I think this was already suggested as an enhacement option for RDBMerge)

    Thanks in advance

  73. Awesome utility!
    Is there a way to merge each file into it’s own worksheet in the workbook instead of all on the same sheet?
    I’m tired of copy and pasting!!!

  74. hi ron

    as my company policy says i cannot download any .zip or .exe files in the office but i need the application for merging multiple worksheets/files into 1 worksheet

    can u please help me out in order to use the code in your macro or how can i solve the problem

    the only solution i could find is through RDBMerge but that cannot be downloaded

    so suggest the alternate ways please

  75. Hi Ron,

    Thanks for a great tool. Is there way you can recommend to de-duplicate the contacts automatically?

    Angela

  76. I am trying to use the Merge multiple worksheets into a single worksheet, using Excel-Mac-2011. I have copied the code as you wrote it into a macro, and saved it as xls file. I changed the source column reference line to A1:H1, as per your instruction…

    “And you can change the range A1:C1 to your range.

    With mybook.Worksheets(1)
    Set SourceRange = .Range(“A1:C1″)
    End With”

    …but it still only copies columns A and B into the new combined file. Is there something else I need to change to make this work?
    Sure hope I can get this to work–it is a gem of a macro.

  78. I have used your merge addin for a few years. It saved me a lot of time as I have to merge a lot of files.

    But suddenly now I can not see any files to merge. The addin does not recognise any xls files (“no items match your search”). I didn’t change anything.

    No clue why it suddenly doesn’t work.

    Thank you,
    Mark Cresswell

  79. Hello Ron,
    I want to call your addin in one of my macro. Found the below command in ur forum, but its not working for me.

    You can call the add-in like this

    Sub test()
    Dim obj As Object
    Application.Run “‘RDBMerge.xlam’!RunRDBMergeForm”, obj
    End Sub

    This is not working for me. It says run time error ‘424’: object required

    Pls help.
    Thanks

  80. Hi

    Excel crashing continously when I am trying to use merge through RDBMERGE in ubuntu 14.04 lts. This is the command line output:

    fixme:advapi:RegisterTraceGuidsA (0x2e0448c0, 0x2e0d8a88, {8736922d-e8b2-47eb-8564-23e77e728cf3}, 1, 0x33fba0, (null), (null), 0x2e0d8a88,): stub
    fixme:imm:ImmDisableIME (-1): stub
    fixme:dbghelp:elf_search_auxv can’t find symbol in module
    fixme:dbghelp:MiniDumpWriteDump NIY MiniDumpWithDataSegs
    fixme:advapi:RegisterEventSourceW ((null),L”Microsoft Office 14″): stub
    fixme:advapi:ReportEventW (0xcafe4242,0x0001,0x0000,0x000003e8,(nil),0x0008,0x000000e2,0x2e0c2b7c,0x6ac58c): stub
    err:eventlog:ReportEventW L”excel.exe”
    err:eventlog:ReportEventW L”14.0.4734.1000″
    err:eventlog:ReportEventW L”4b58fbb3″
    err:eventlog:ReportEventW L”vbe7.dll”
    err:eventlog:ReportEventW L”0.0.0.0″
    err:eventlog:ReportEventW L”4b229a9f”
    err:eventlog:ReportEventW L”0″
    err:eventlog:ReportEventW L”00003642″
    fixme:advapi:DeregisterEventSource (0xcafe4242) stub
    sajawed@sajawed:~/wine32/drive_c/Program Files/Microsoft Office/Office14$ fixme:netapi32:NetGetJoinInformation Semi-stub (null) 0x33fbd0 0x33fbc4
    fixme:advapi:UnregisterTraceGuids 0: stub
    fixme:advapi:RegisterEventSourceW ((null),L”Office Software Protection Platform Service”): stub
    fixme:advapi:ReportEventW (0xcafe4242,0x0000,0x0000,0x40000387,(nil),0x0000,0x00000000,0x83e894,(nil)): stub
    fixme:advapi:DeregisterEventSource (0xcafe4242) stub

  81. hi,
    i tried using the rdbmerge in 2011 mac.
    The resulting file does not have merged content, but just a text “Ready” on top left and seconf log sheet.
    What am i missing.

    Also I need to merge all my worksheets in their specific tabs.
    Also I need to merge into a single file data from all tabs into one sheet with matching column content.
    Thanks

  82. Hello.

    I have been using rdbmerge in Excel 2011 (Mac) for several years. Recently it didn’t show up in the tools menu so I attempted to re link via add-ins. “Excel could not ope RDBMergeMac.xlam because some content is unreadable. Do you want to open and repair this workbook?”

    The repair does not work. I am using Mac OS 10.10.5 and Excel 2011 vs 14.5.4 (150722).

    Thanks in advance for any advice!

  83. Hi sir Ron, I bump to this awesome merging file and I’m using it for a while now, can I request to have an option that it can copy also data that were hidden by DATA-Filter option. having huge files and merging single sheet(problem sheet has different filters applied, e.g. shorted by date, only contains this, greater than this value etc.) taking years to load 1 sheet disable filter and save.

    thanks.

  84. Hi Ron,

    RDB Merge is a great tool. Are you still developing it?
    I was wondering if it is possible to include an option to ignore empty cells when merging.
    The Files I have to merge contain a large number of empty cells within the range I need to merge and being able to not merging empty cells would be a great help for me.

    Greetings from Ireland

    Matthias

  85. Matthias…you might want to check out PowerQuery aka “Get and Transform”, if you’ve got Excel 2013 or later. It radically simplifies mashing together data from different tabs/workbooks/sources.

  86. Hi Ron,

    The RDBMerge.xlam plugin is getting crashed frequently due to large data is piled up in memory. If that starts with saving the workbook and save on every merge, it will not crash and if autofilter removed on every sheet it open, it will be helpful. I could not modify your code as it is protected with password.

  87. Hi Ron,

    I am using RDB add-in just a week ago to consolidate different excel file sheet to one sheet.

    However, I am getting error in log sheet for particular sheet and tried in all the ways,,but my unluck only that particular sheet is not getting picked on the merge action.

    Pls help

    Thanks

    Sangamesh

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.