Merge two excel files with primary key in vb.net

Question

I have two Excel files I need to merge into one Excel file based on a primary key. I need to do this in vb.net and have no idea where to start. One file is a data list and the other a matrix. I need the matrix fields added to the data list and depending on the primary key the data rows in the data list will be populated by the corresponding matrix row. I have the following but I am unsure if I'm going in the right direction. If so, then how do i save it as a new Excel file?

Dim DT1 As DataTable
DT1.Rows.Add(DtSet)

Dim DT2 As DataTable
DT2.Rows.Add(DtSet2)

DT1.PrimaryKey = New DataColumn() {DT1.Columns(ComboBox1.SelectedItem)}
DT2.PrimaryKey = New DataColumn() {DT1.Columns(ComboBox2.SelectedItem)}

DT1.Merge(DT2)

The reason your last question was deleted was that you didn't do much investigation yourself. I just searched-engined *save datatable into excel* and the *first* result is http://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel . This is the exact same question you have, and it has a very well received answer. — djv, Sep 08 '16 at 21:24
My two tables represent two excel files, Matrix and DataList. I'm trying to populate my DataList with the Matrix via primary key, do you know a way of doing this? That is what I am using for DT1 and DT2 above.... — Chrisetiquette, Sep 08 '16 at 21:33
Also, I did the code conversion and I still get this error "Public member 'Add' on type 'Worksheet' not found." — Chrisetiquette, Sep 08 '16 at 21:44
`Imports Microsoft.Office.Interop.Excel` | `Dim wb = New Workbook()` | `wb.Worksheets.Add(dt, "WorksheetName")` — djv, Sep 08 '16 at 21:54
Currently you are asking 2 questions. Break your question to 2 Posts: *1)* How to merge two DataTables *2)* How to save a DataTable into a Excel file. — Reza Aghaei, Sep 08 '16 at 22:21
@RezaAghaei I did but no one is answering it http://stackoverflow.com/questions/39397514/code-data-list-with-a-matrix-in-excel-using-vb-net — Chrisetiquette, Sep 08 '16 at 22:24
@Verdolino im getting this error "'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll " from Dim wb = New Excel.Workbook() — Chrisetiquette, Sep 08 '16 at 22:27
I read your other question. Also about the other question: you should break the problem to two parts. First how to read data from excel file into a DataTable. Second how to merge two DataTables to one. — Reza Aghaei, Sep 08 '16 at 22:28
@RezaAghaei I can get the data from the excel files into two separate DataTable's but I can't join them together. Kind of like an inner join in sql. — Chrisetiquette, Sep 08 '16 at 22:29
Good job. If your current question is about merging two data tables, you can remove the parts that you are talking about excel. Just keep parts about merging two data tables and also include columns which those data tables have and say what would be the result. — Reza Aghaei, Sep 08 '16 at 22:32
Then is seems what you are looking for: [Merging 2 data tables in vb.net](http://stackoverflow.com/questions/1262383/merging-2-data-tables-in-vb-net) — Reza Aghaei, Sep 08 '16 at 22:35
@RezaAghaei ok I updated the question to be more relevant to my needs, care to answer it now? lol — Chrisetiquette, Sep 08 '16 at 22:37
@RezaAghaei your link wouldn't work because the code requires you to list out each column, i need this to be variable. — Chrisetiquette, Sep 08 '16 at 22:39
@RezaAghaei Basically I need something like this but in vb.net SELECT [ALL].Field1, [ALL].Field2, [ALL].Field3, [ALL].Field4, [ALL].Field5, [ALL].Field6, States.Statelong, States.Stateshort FROM States INNER JOIN [ALL] ON States.Stateshort = [ALL].Field2; — Chrisetiquette, Sep 08 '16 at 22:41
@Chrisetiquette The solution in the link is really good. It doesn't depend to column count and types. It just ask to for your tables and the key column. Go with it. — Reza Aghaei, Sep 08 '16 at 22:49
@RezaAghaei I'm having trouble wrapping my head around the code, can't seem to fit it to my needs.... — Chrisetiquette, Sep 08 '16 at 23:37

score 0 · Answer 1 · answered Dec 07 '16 at 19:07

If I were you, i would use the Excel consolidate feature for this.

https://www.ablebits.com/office-addins-blog/2015/09/01/consolidate-excel-merge-sheets/#consolidate-data-excel

If you really have to use VB for this, you can try the script below.

Sub Merge2Workbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop

    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

You may find this interesting too.

https://siddharthrout.wordpress.com/2012/06/05/vb-netvba-copy-rows-from-multiple-tabs-into-one-sheet-in-excel/

Merge two excel files with primary key in vb.net

1 Answers1