Please Help me Compare two excel files (cells) using Visual Basic 2010

Please Help me Solve my code please ....Excel with Visual Basic 2010
By soulbro in .NET Newbies




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

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)
Invalid ExceptionCast Exception was unhandled
                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

---

Moved to VB.Net forum

You really should be using the .SubString method rather than all those VB.Right and VB.Left statements

Code:

---

Partnumber.Substring(Position,NumberOfCharacters)

---