No announcement yet.

Help needed with VBA Code to merge two separate excel documents onto one document

  • Filter
  • Time
  • Show
Clear All
new posts

  • Help needed with VBA Code to merge two separate excel documents onto one document

    I attached a copy of a sample of how I want the system to work. The first sheet "Doc1" represents the main document, "Doc2" represents the second document that will be merged onto "Doc1". Please keep in mind that these are separate document but for explanation purposes, I put them in the same document. The "MergedDoc" sheet represents what I want the document to look like after the merge has occurred. For this merge to occur;
    • The click of a button on "Doc1" should trigger that action. Both documents will be placed in the same folder on my computer.
    • As seen in the merged doc, the merge should occur in an alphabetical order using the "Option" column.
    • If a part shares the same "Option" and "Part name" on both documents but different "Part number", then the one on "Doc2" replaces the one on "Doc1"
    • If a part shares all the same info on both document, then that part doesn't have to be merged.
    All these conditions listed have been illustrated in the excel document attached to this.

    Please let me know if this helps and if you need more information. Thanks in advance.
    Attached Files

  • #2
    Based on the way you've described it and the sample documents provided, that requires a number of discreet steps to accomplish the desired result.
    The complication is mainly due to the section "sub-headers" which appear in the samples (eg JD0853 ARRANGEMENT, TURBOCHARGER) as these need to end up back at the top of their respective section when the merged data is added and then sorted.

    I believe the attached sample file does what you have requested by placing the macro into a sample "Doc1" style file. Also attached is the example "Doc2" file which needs to be open when the macro runs. The name of the Doc2 file can be specified on the front sheet of the Doc1 file.
    This macro does a reasonable amount of error checking but is not foolproof ! The main code for merging is in the module called MERGE.

    In your example file, merged data was moved back into the correct sub-section but there was no obvious sorting. However, a final sort to get the merged data back into some semblance of order will sort in Option and then Part number order (smallest to largest).

    Open both the Merge Doc 1.xlsm file and the Merge Doc 2.xlsx file. Then click on the button in the Merge Doc 1.xlsm file and it will create a new merged workbook. At present this new merged workbook is not copied back to Doc1 as this was not specified however this is just a proof of concept on how to tackle such a task.
    Attached Files


    • #3

      Thanks a lot for helping and putting this together. The code works as wanted but there is just a little tweak I would like to make and that's on me because I didn't clarify in my description of what I wanted.

      When the merge happens,
      • I don't want a new document to open with the merged file. Instead I want the merge to happen on "Doc1" which is the document that has the code in it. So the info from "Doc2" is merged and joined unto "Doc1".
      • Also, the real document that this will go on will have multiple sheets within that one document. For example, "Doc1" will have different sheets within it but the "BOM" sheet will contain that list that needs to be merged. Can this code work in such a way that it only merges that sheet with the other document?
      • Lastly, can you type out some instructions on how you were able to use the hyperlink to run the code?

      Once again thanks for helping with this. Please let me know if you have any questions.


      • #4
        OK, so the revised version is attached which will merge data back to the BOM sheet. I've also re-attached the sample Doc2 file for completeness (it has not been changed).
        To make this suitable for testing, there is a checkbox on the "Run" sheet which will determine if the merged output replaces the original BOM sheet or writes this to a new workbook as it was doing previously.
        The "BOM" sheet is referenced by name in the code so be aware of this.

        I've also made a number of bug fixes that became evident when I did some further testing but I think this version is now merging correctly.

        The main sheet called "Run" has the button on it to run the macro. This sheet also now uses a named range to reference the name of the 2nd doc to merge with - the named range is called MergeName and is referenced in the code.
        The "Run" sheet itself is also referenced in the code in order to determine the value of the checkbox (which is called cbResult). Be aware of this as the sheet name is important to the code.

        I think your last question was about how to create the button and assign it to a macro. There are a number of ways to do this.
        The simple way in this example is to :
        • insert a suitable shape on the worksheet (Insert menu, then Shapes option : pick a shape)
        • put some text into the shape
        • size and move the shape
        • right click on the shape and select the Assign Macro option
        • select the MergeDoc macro
        Now that a macro is assigned, it will run that macro when you click on the shape.
        Attached Files


        • #5
          This is wonderful! You really covered all the details with this new code.

          The only thing I noticed was that a new document opened when I clicked the merge button with the merged file. I don't want a separate document to open with the merged file. Can this merge occur in the page shown in the picture attached?

          As you can see in the picture I attached, that is the document that hosts the code for the merge. I want the merge to occur on the "BOM" sheet so this is where the new and merged list will be. So the new and refined list will be on the "BOM" sheet instead of automatically opening a new sheet with the new list. So it will be like pasting the "Doc2" onto "Doc1" and arranging it accordingly to the different sub-headers.

          Please let me know if this helps. Besides that every other thing was pin point. Thanks for helping.
          Attached Files


          • #6
            I'm not following your question. As per my previous post, there is a checkbox on the "Run" sheet which allow you to determine where to place the merged document.

            The checkbox has a description of "Write merge result back to this workbook"
            If leave this option unchecked, it will create a new workbook with the merged results (handy for testing)
            If you check this option, the merged results will be placed back into the BOM sheet, replacing the existing contents of that sheet.

            I've created it this way just to make it easy when I was testing.

            Let me know if that clarifies.


            • #7

              Thanks that's exactly what I was asking for.

              Now I can work with this and complete what I am trying to do.

              Thanks once again