can someone please confirm why i am getting this error? Thanks in advance Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCompare.Click
' On Error Resume Next
On Error GoTo Error_Handler
oExcel = New Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim iRows As Object
Dim iCols As Short
Dim iRow As Object
Dim iCol As Short
Dim sFileName As String
Dim sComponent As String
Dim iFound As Short
Dim bBorder As Boolean
sComponent = Trim(cmbComponent.Text)
If sComponent = "" Then
MsgBox("Please select a component name from the list.")
Exit Sub
End If
sFileName = Trim(Text2.Text)
' Verify that file name is entered
If Mid(sFileName, Len(sFileName), 1) = "/" Or Mid(sFileName, Len(sFileName), 1) = "\" Then
MsgBox("Please enter valid file name in File Location text field.")
Exit Sub
Else
'append .TXT to file name if not provided
If UCase(Mid(sFileName, Len(sFileName) - 3)) <> ".TXT" Then
sFileName = sFileName & ".TXT"
End If
End If
bBorder = False
iRows = 0
iCols = 0
' Verify if component name is entered
iFound = GetFileAttributes(sFileName) ' Check if the file is found.
If iFound <> -1 Then
' Open Text file in Excel.
'Call oExcel.Workbooks.OpenText(Filename:=sFileName, DataType:=Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Tab:=True)
oExcel.Workbooks.Open("C:\DDSTest\A2.xls")
oBook = oExcel.ActiveWorkbook
oSheet = oBook.Worksheets(1)
oExcel.Visible = True
'' format cell some
'Count # of rows and columns that display data.
iRows = oSheet.UsedRange.Rows.Count
iCols = oSheet.UsedRange.Columns.Count
Else
'Display message if file is not found.
MsgBox("The file '" & sFileName & "' is not found.")
Exit Sub
End If
Select Case UCase(sComponent)
Case "QUOTELIST"
If iFound <> -1 Then
'Loop through rows to compare data and mark differences.
For iRow = 1 To iRows
' Bold the layout row.
If InStr(1, oSheet.Cells._Default(iRow, 4).Value, "Template Layout:") > 0 Then
oSheet.Rows._Default(iRow).Font.Bold = True
End If
'Search for lines that contain symbol comparison to compare and mark differences.
If InStr(1, oSheet.Cells._Default(iRow, 5).Value, "Error: Symbol on") > 0 Then
For iCol = 6 To iCols
'change values to string
oSheet.Cells._Default(iRow + 1, iCol).Value = CStr((oSheet.Cells._Default(iRow + 1, iCol).Value))
oSheet.Cells._Default(iRow + 2, iCol).Value = CStr((oSheet.Cells._Default(iRow + 2, iCol).Value))
If InStr(1, oSheet.Cells._Default(iRow + 1, iCol).Value, ".") > 0 And InStr(1, oSheet.Cells._Default(iRow + 1, iCol).Value, "E") = 0 And IsNumeric(oSheet.Cells._Default(iRow + 1, iCol).Value()) Then
oSheet.Cells._Default(iRow + 1, iCol).Value = Decimal.Round(CDec(Double.Parse(oSheet.Cells._Default(iRow + 1, iCol).Value)), 2)
End If
If InStr(1, oSheet.Cells._Default(iRow + 2, iCol).Value, ".") > 0 And InStr(1, oSheet.Cells._Default(iRow + 2, iCol).Value, "E") = 0 And IsNumeric(oSheet.Cells._Default(iRow + 2, iCol).Value) Then
oSheet.Cells._Default(iRow + 2, iCol).Value = Decimal.Round(CDec(Double.Parse(oSheet.Cells._Default(iRow + 2, iCol).Value
)), 2)
End If
If oSheet.Cells._Default(iRow + 1, iCol).Value <> oSheet.Cells._Default(iRow + 2, iCol).Value Then
'MsgBox(oSheet.Cells._Default(iRow + 1, iCol).Value)
'MsgBox(oSheet.Cells._Default(iRow + 2, iCol).Value)
oSheet.Cells._Default(iRow + 1, iCol).Interior.ColorIndex = 3
oSheet.Cells._Default(iRow + 2, iCol).Interior.ColorIndex = 3
End If
Next
End If
Next
oBook.Save()
'Display message box when all comparison highlights are completed.
MsgBox("All errors have been successfully highlighted.")
' Else
'Display message if file is not found.
' MsgBox("The file '" & sFileName & "' is not found.")
End If
'--------------------------------------------------------------------------
Case Else
'Display message if component is not supported.
MsgBox("The component '" & sComponent & "' is not currently supported.")
End Select
Me.Close()
Exit Sub
Error_Handler:
MsgBox("Error: " & Err.Number & "-" & Err.Description, MsgBoxStyle.Critical, "UNEXPECTED ERROR")
oBook.Close()
End Sub
End Class
