|
-
July 1st, 2014, 07:48 AM
#1
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 11:21 AM.
Reason: added code tags
-
July 1st, 2014, 11:24 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
