Please Help Me solve Excel with Visual Basic 2010

[vivibuy88]

I'm newbie..First time touching visual basic 2010 . I want to compare 2 files , If 2 files having same item then the item will be copy and place in new excel file. However I'm having diffult on the compare cells part.
I had been trying to solve the question but I cant get a solution by searching internet.
Please Help me and take note on my error ..Please
Below are my code


Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Imports Microsoft.Office.Interop
Class Form1

Dim i As Integer
Dim m As Integer

Dim excel As New Object
Dim workbook As New Object
Dim sheet As New Object


Dim excel1 As New Object
Dim workbook1 As New Object
Dim sheet1 As New Object

Dim excel2 As New Object
Dim workbook2 As New Object
Dim sheet2 As New Object

Dim excel3 As New Object
Dim workbook3 As New Object
Dim sheet3 As New Object

Dim csv_location_check As String
Dim outputdrive$
Dim unmount_output$
Dim ma_output$
Dim BOM_output$
Dim csv_location As String
Dim rmd_location$
Dim unmount_B$
Dim unmount As String
Dim count_csv$
Dim partNumber2$
Dim Location_2$
Dim Location_length$
Dim CT$
Dim Tech$
Dim Location_Renamed$
Dim partNumber$
Dim filename As String
Dim sFileName As String
Dim csv_file$
Dim rmd_file$
Dim eBOM_file$
Dim BOM_database$
Dim Location_rmd$

Dim count_rmd_3 As Integer
Dim count_rmd_2 As Integer
Dim Bot_exit As Integer
Dim rmd_comp As Integer
Dim check_Bom As Boolean
Dim Bot_comp As Integer
Dim total_comp As Integer
Dim Top_comp As Integer
Dim error_BOM_2 As Integer
Dim error_tech As Boolean
Dim csv_comp As Integer
Dim smd_bot As Integer
Dim smd_top As Integer
Dim ma As Integer
Dim col_1 As Integer
Dim col_2 As Integer
Dim col_count As Integer
Dim model As Integer
Dim error_nb As Integer
Dim error_ma As Integer
Dim error_BOM As Integer
Dim error_eBOM As Integer
Private Sub Browse_csv_Click(sender As System.Object, e As System.EventArgs) Handles Browse_csv.Click
On Error GoTo errorhandler
Dim sFileNames() As String
Dim sPath As String
Const max_size As Integer = 32767
csv_txt.Clear()
OpenCSV.Filter = "Miscorsoft Comma Seperated Value(*.csv)|*.csv"
OpenCSV.FilterIndex = 2
OpenCSV.Multiselect = True
OpenCSV.CheckFileExists = True
OpenCSV.CheckPathExists = True
OpenCSV.ShowDialog()
csv_txt.Text = OpenCSV.FileName
sFileNames = Split(OpenCSV.FileName, vbNullChar)
Exit Sub
errorhandler:
Exit Sub

End Sub

Private Sub Browse_rmd_Click(sender As System.Object, e As System.EventArgs) Handles Browse_rmd.Click
Dim filter_Renamed As String
On Error GoTo OpenError
filename$ = ""
rmd_txt.Clear()
OpenRMD.Filter = "Microsoft Excel Workbook (*.lst)|*.lst"
OpenRMD.FilterIndex = 2
OpenRMD.ShowDialog()
rmd_txt.Text = OpenRMD.FileName

GenerateBtn.Enabled = True
Exit Sub
OpenError:
Exit Sub
End Sub

Private Sub GenerateBtn_Click(sender As System.Object, e As System.EventArgs) Handles GenerateBtn.Click
Dim count_eBOM, count_rmd, count_csv, csv_comp, rmd_comp, eBOM_comp, BOM_comp As Integer
Dim count_BOM As Integer
Dim row_rmd_2, row_csv, row_rmd, row_eBOM As Integer
Dim row_BOM As Integer



outputdrive = "O:\ENG\Dept\04_Common\02_Development_Guidelines\02_PCB\Training_Folder\"
BOM_output = outputdrive & PCB_txt.Text & "_components.txt"
FileOpen(2, BOM_output, OpenMode.Output)

unmount_output = outputdrive & PCB_txt.Text & "_nb_components.txt"
FileOpen(1, unmount_output, OpenMode.Output)

ma_output = outputdrive & PCB_txt.Text & "_ma_component.txt"
FileOpen(3, ma_output, OpenMode.Output)

unmount_txt.Text = ""
PrintLine(1, "Generating EBOM.....")

'Bom_txt.Text = ""
'PrintLine(2, "Generating EBOM ")

ma_txt.Text = ""
PrintLine(3, "Generating EBOM...")

unmount_txt.Text = unmount_txt.Text & vbNewLine & vbNewLine & "Generating n.b.componentlist..."
unmount_txt.Text = unmount_txt.Text & vbNewLine & "Not inserted components are : "
PrintLine(1, "Generating n.b.component list...")
PrintLine(1, "not inserted component are :")

ma_txt.Text = ma_txt.Text & vbNewLine & vbNewLine & "Generating ma components "
PrintLine(3, "Generating ma components...")

csv_file = csv_txt.Text
rmd_file = rmd_txt.Text
eBOM_file = "O:\ENG\Dept\04_Common\02_Development_Guidelines\02_PCB\Training_Folder\EBOM.xls"

excel = CreateObject("Excel.Application")
workbook = excel.workbooks.Open(csv_file)
excel.visible = True
sheet = workbook.worksheets.Item(1)
For csv_comp = 3 To 1000
If sheet.Cells(csv_comp, 4).Value = "" Then
Exit For
Else
count_csv = count_csv + 1
End If
Next

excel1 = CreateObject("Excel.Application")
workbook1 = excel1.Workbooks.Open(rmd_file)
excel1.Visible = True
sheet1 = workbook1.worksheets.Item(1)
For rmd_comp = 13 To 1000
If sheet1.Cells(rmd_comp, 1).value = "" Then
Exit For
Else
count_rmd = count_rmd + 1
End If
Next

excel2 = CreateObject("Excel.Application")
workbook2 = excel2.Workbooks.Open(eBOM_file)
excel2.Visible = True
sheet2 = workbook2.worksheets.Item(1)
' sheet2.Cells(1, 3) = title_txt.text
'sheet2.Cells(2, 3) = "eBOM_" & PCB_txt.Text

error_nb = False
error_ma = False
error_BOM = False

smd_bot = 0
smd_top = 0
i = 0
count_rmd_3 = count_rmd + 14
Dim toLoop As Integer
toLoop = 3 + count_csv
For row_csv = 3 To toLoop


unmount = sheet.Range(sheet.cells(row_csv, 8)).Value Conversion from type Range to type String is not valid
csv_location = sheet.Range(sheet.Cells(row_csv, 4)).Value
csv_location_check = sheet.Range(sheet.Cells(row_csv, 7)).Value

If csv_location_check = "MP" Or csv_location_check = "TP" Then GoTo 408
For row_rmd = 13 To 13 + count_rmd
rng = DirectCast(sheet1.get_Range(sheet1.Cells(row_rmd, 1), sheet1.Cells(36, 4)), Excel.Range)

Tech = VB.Left(VB.Right(sheet1.Cells(row_rmd, 1), 36), 4) Conversion from type Range is not valid

CT = VB.Left(VB.Right(sheet1.Cells(row_rmd.ToString, 1), 45), 6)
Location_Renamed = VB.Right(VB.Left(sheet1.cells(row_rmd.ToString, 1), 7), 5)
Location_length = VB.Left(VB.Right(Location_Renamed.ToString, 3), 1)
partNumber = VB.Right(VB.Left(sheet1.Cells(row_rmd, 1), 27), 13)
partNumber2 = VB.Left(partNumber, 1) & VB.Right(VB.Left(partNumber, 5), 3) & VB.Right(VB.Left(partNumber, 9), 3) & VB.Right(partNumber, 3)
If VB.Left(VB.Right(Location_Renamed, 3), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "0000" & VB.Left(VB.Right(Location_Renamed, 4), 1)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 1)
ElseIf VB.Left(VB.Right(Location_Renamed, 2), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "000" & VB.Left(VB.Right(Location_Renamed, 4), 2)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 2)
ElseIf VB.Left(VB.Right(Location_Renamed, 1), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "00" & VB.Left(VB.Right(Location_Renamed, 4), 3)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 3)
Else
Location_2 = Location_Renamed
Location_rmd = Location_Renamed
End If

If unmount <> "n.b." And csv_location = Location_rmd Then
For row_eBOM = 14 + i To count_rmd_3
If CT = "TOP" And Tech = "smd" Then
Top_comp = Top_comp + 1
i = i + 1
sheet2.Cells(row_eBOM, 4) = partNumber2
sheet2.Cells(row_eBOM, 3) = Location_2
sheet2.Cells(row_eBOM, 6) = "CT"
Exit For
ElseIf CT = "TOP" And Tech = "cons" Then
i = i + 1
sheet2.Cells(row_eBOM, 4) = partNumber2
sheet2.Cells(row_eBOM, 3) = Location_2
sheet2.Cells(row_eBOM, 6) = "CT"
Exit For
ElseIf CT = "BOTTOM" And Tech = "smd" Then
smd_bot = smd_bot + 1
Exit For
ElseIf CT = "BOTTOM " And Tech = "cons" Then
smd_bot = smd_bot + 1
Exit For
ElseIf Tech <> "smd" And Tech <> "" And Tech <> "cons" Then
error_ma = error_ma + 1
ma_txt.Text = ma_txt.Text & vbNewLine & "Please define process insertion - " & Location_2
PrintLine(3, "Please define " & Location_2)
Exit For
End If
Next
End If
If unmount = "b.b." And csv_location = rmd_location Then
error_nb = error_nb + 1
unmount_txt.Text = unmount_txt.Text & vbNewLine & csv_location & "-" & sheet.cells(row_csv, 7)
PrintLine(1, csv_location & "-" & sheet.cells(row_csv, 7))
ElseIf csv_location = Location_rmd Then
Exit For
End If
Next
408:
Next

'checking for cb
m = 0
smd_top = 16 + i
Bot_exit = 0
Bot_comp = 1
If smd_bot <> 0 Then
For row_csv = 3 To 3 + count_csv
unmount = sheet.cells(row_csv, 8)
csv_location = sheet.cells(row_csv, 4)
csv_location_check = sheet.cells(row_csv, 7)
If csv_location_check = "MP" Or csv_location_check = "TP" Then GoTo 508
For row_rmd = 13 To 13 + count_rmd
Tech = VB.Left(VB.Right(sheet1.Cells(row_rmd, 1), 36), 4)
CT = VB.Left(VB.Right(sheet1.Cells(row_rmd, 1), 45), 6)
Location_Renamed = VB.Right(VB.Left(sheet1.cells(row_rmd, 1), 7), 5)
Location_length = VB.Left(VB.Right(Location_Renamed, 3), 1)
partNumber = VB.Right(VB.Left(sheet1.Cells(row_rmd, 1), 27), 13)
partNumber2 = VB.Left(partNumber, 1) & VB.Right(VB.Left(partNumber, 5), 3) & VB.Right(VB.Left(partNumber, 9), 3) & VB.Right(partNumber, 3)
If VB.Left(VB.Right(Location_Renamed, 3), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "0000" & VB.Left(VB.Right(Location_Renamed, 4), 1)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 1)
ElseIf VB.Left(VB.Right(Location_Renamed, 2), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "000" & VB.Left(VB.Right(Location_Renamed, 4), 2)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 2)
ElseIf VB.Left(VB.Right(Location_Renamed, 1), 1) = "" Then
Location_2 = VB.Left(Location_Renamed, 1) & "00" & VB.Left(VB.Right(Location_Renamed, 4), 3)
Location_rmd = VB.Left(Location_Renamed, 1) & VB.Left(VB.Right(Location_Renamed, 4), 3)
Else
Location_2 = Location_Renamed
Location_rmd = Location_Renamed
End If
count_rmd_2 = count_rmd_3 + 2
For row_eBOM = smd_top + m To count_rmd_2
If CT = "BOTTOM" And Tech = "smd" Then
Bot_comp = Bot_comp + 1
m = m + 1
Bot_exit = Bot_exit + 1
sheet2.cells(row_eBOM, 4) = partNumber2
sheet2.Cells(row_eBOM, 3) = Location_2
sheet2.Cells(row_eBOM, 6) = "CB"
smd_bot = smd_bot - 1
Exit For
ElseIf CT = "BOTTOM" And Tech = "cons" Then
m = m + 1
sheet2.cells(row_eBOM, 4) = partNumber2
sheet2.Cells(row_eBOM, 3) = Location_2
sheet2.Cells(row_eBOM, 6) = "CB"
smd_bot = smd_bot - 1
Exit For
End If
Next

If csv_location = rmd_location Then
Exit For
End If
Next
508:
Next
End If

excel = Nothing
workbook = Nothing
sheet = Nothing
excel1 = Nothing
workbook1 = Nothing
sheet1 = Nothing
excel2 = Nothing
workbook2 = Nothing
sheet2 = Nothing



End Sub


End Class

[Izandol]

You must use code tags please.

After you have done this, please confirm if you mean to use:

Please Login or Register  to view this content.

or truly:

Please Login or Register  to view this content.

You must also know that this is VBA programming forum and not for .Net languages like VB.