VB App: “Comb Merge” Excel Workbooks

[Human2.0]

Greeting fellow Humans,

I'm trying to "comb merge" (if any thing as such) bunch of Excel files. There are 2 type of Excel workbook: A & B

Both has a column called : Location ID

These are the step I think the prog should do :

Promp user to select location of 2 file >> Read data from the user selected file >> Open pre-set Excel template paste item accordingly >> Save it!

Final Output: Same Location ID from both workbook in 1 sheet beside each other..

Possible? please guide me..newbie here..done my research Smile | :) **i can post picture of the files but i don't think you can on here

**Update: there are many pair of A&B files.Each pair of A & B files has different number of rows

**Here is how the interface looks like, just to gv an idea: http://www.imgplace.com/viewimg849/8800/45mergeinterface.jpg

[adam_k]

Yes it's possible.
I might be easier to do and possibly use it with VBA from Excel, but it is possible to do it with VB.Net.

[Human2.0]

Yes it's possible. I might be easier to do and possibly use it with VBA from Excel, but it is possible to do it with VB.Net.

I used this code for the buttons..now only the merge button left..could some one suggest how

Imports Microsoft.Office.Interop.Excel
Imports System.Data.OleDb
Public Class Form1
Private Sub btnHT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHT.Click 
OpenFileDialog1.ShowDialog()
If OpenFileDialog1.FileName = OpenFileDialog2.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog1.FileName = OpenFileDialog3.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog1.FileName = OpenFileDialog4.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
    lblHT.Text = OpenFileDialog1.FileName End If
End Sub
 
Private Sub btnHB_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHB.Click
OpenFileDialog2.ShowDialog()
If OpenFileDialog2.FileName = OpenFileDialog1.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog2.FileName = OpenFileDialog3.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog2.FileName = OpenFileDialog4.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
    lblHb.Text = OpenFileDialog2.FileName
End If
End Sub
 
Private Sub btnRT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRT.Click
OpenFileDialog3.ShowDialog()
If OpenFileDialog3.FileName = OpenFileDialog1.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog3.FileName = OpenFileDialog2.FileName Then
     MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog3.FileName = OpenFileDialog4.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
    lblRT.Text = OpenFileDialog3.FileName
End If
End Sub
 
Private Sub btnRB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRB.Click
OpenFileDialog4.ShowDialog()
If OpenFileDialog4.FileName = OpenFileDialog1.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog4.FileName = OpenFileDialog2.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
ElseIf OpenFileDialog4.FileName = OpenFileDialog3.FileName Then
    MessageBox.Show("File already uploaded!", "Try again", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
    lblRB.Text = OpenFileDialog4.FileName
End If
End Sub
 
Private Sub btnMerge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMerge.Click Dim oExcel As Object = CreateObject("Excel.Application")

[Human2.0]

Yes it's possible. I might be easier to do and possibly use it with VBA from Excel, but it is possible to do it with VB.Net.

total 5 button : Header Top , Header Bottom , Report Top , Report Bottom & Merge.
I have created a template for the final output.

Report top & Report bottom will be paste in a specified cell = copy & paste concept.
Quantity: Header files has standard set of rows each time.

Below that

the Report top & Report bottom will be "comb merge. The primary key would be "Location"

So same "Location" id from Report top & report bottom will be paste beside each other..process goes on depending how many quantity it has.

Quantity: report files has random quantity of rows.

Simplified steps:
1-user selects all 4 files.
2-user click merge
3-data are read and paste in the template file
4-user prompt to save.
-HAPPY ENDING-

[Human2.0]

DEAD