Please Help me Compare two excel files (cells) using Visual Basic 2010
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Jul 2014
    Posts
    2

    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
    Last edited by DataMiser; July 1st, 2014 at 12:21 PM. Reason: added code tags

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,860

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

    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)
    Always use [code][/code] tags when posting code.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center