AlexF777
asked on
empty cells in Excel file give run-time errors, not sure how to handle properly
the following snippet works ok, as long as cells(3), cells(8) and cells(12) in Excel file are not empty
trying to find good way to handle situation when they are
Dim xlApp = New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
For Each objExcelFile In My.Computer.FileSystem.Get DirectoryI nfo(strPat h2ProcessE xcelMileag eFiles).Ge tFiles(str WildCard2P rocess)
Dim strExceptionMileagePath As String = strPath2ProcessExcelMileag eFiles & "\Exceptions\" & objExcelFile.Name & "_Exceptions.txt"
Dim strOkMileagePath As String = strPath2ProcessExcelMileag eFiles & "\Ok\" & objExcelFile.Name & "_Ok.txt"
My.Computer.FileSystem.Wri teAllText( strExcepti onMileageP ath, _
"Precision" & vbTab & "Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII )
My.Computer.FileSystem.Wri teAllText( strOkMilea gePath, _
"Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII )
If objExcelFile.Name.StartsWi th("~$") Then
Continue For
End If
xlWorkBook = xlApp.Workbooks.Open(objEx celFile.Fu llName)
xlWorkSheet = xlWorkBook.Worksheets("Inc idents With Mileage")
' For Each xlWorkSheet In xlWorkBook.Worksheets
For Each row In xlWorkSheet.UsedRange.Rows
If row.cells.count >= 12 Then
My.Computer.FileSystem.Wri teAllText( strMilesCr ossReferen cePath & "\" & _
row.cells(8).value.ToStrin g & ".txt", _
row.cells(12).value.ToStri ng & vbCrLf, False)
If row.cells(3).value Is Nothing Then
My.Computer.FileSystem.Wri teAllText( strExcepti onMileageP ath, _
" " & vbTab & row.cells(8).value.ToStrin g & vbTab & row.cells(12).value.ToStri ng & vbCrLf, True, System.Text.Encoding.ASCII )
Else
Select Case row.cells(3).value.ToStrin g
Case "Precision" ' just a header
Exit Select
Case "address"
My.Computer.FileSystem.Wri teAllText( strOkMilea gePath, _
row.cells(8).value.ToStrin g & vbTab & row.cells(12).value.ToStri ng & vbCrLf, True, System.Text.Encoding.ASCII )
Case Else
My.Computer.FileSystem.Wri teAllText( strExcepti onMileageP ath, _
row.cells(3).value.ToStrin g & vbTab & row.cells(8).value.ToStrin g & vbTab & row.cells(12).value.ToStri ng & vbCrLf, True, System.Text.Encoding.ASCII )
End Select
End If
End If
Next
xlWorkBook.Close()
Next
trying to find good way to handle situation when they are
Dim xlApp = New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
For Each objExcelFile In My.Computer.FileSystem.Get
Dim strExceptionMileagePath As String = strPath2ProcessExcelMileag
Dim strOkMileagePath As String = strPath2ProcessExcelMileag
My.Computer.FileSystem.Wri
"Precision" & vbTab & "Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII
My.Computer.FileSystem.Wri
"Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII
If objExcelFile.Name.StartsWi
Continue For
End If
xlWorkBook = xlApp.Workbooks.Open(objEx
xlWorkSheet = xlWorkBook.Worksheets("Inc
' For Each xlWorkSheet In xlWorkBook.Worksheets
For Each row In xlWorkSheet.UsedRange.Rows
If row.cells.count >= 12 Then
My.Computer.FileSystem.Wri
row.cells(8).value.ToStrin
row.cells(12).value.ToStri
If row.cells(3).value Is Nothing Then
My.Computer.FileSystem.Wri
" " & vbTab & row.cells(8).value.ToStrin
Else
Select Case row.cells(3).value.ToStrin
Case "Precision" ' just a header
Exit Select
Case "address"
My.Computer.FileSystem.Wri
row.cells(8).value.ToStrin
Case Else
My.Computer.FileSystem.Wri
row.cells(3).value.ToStrin
End Select
End If
End If
Next
xlWorkBook.Close()
Next
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
checking for nothing still produced an error
Did you define variables after this line "If row.cells.count >= 12 Then"?
ASKER
mas_oz2003,
i apologize:
although
if row.cells(8) is nothing
still did not work for me,
if row.cells(8).value is nothing worked just find
i apologize:
although
if row.cells(8) is nothing
still did not work for me,
if row.cells(8).value is nothing worked just find
ASKER
the reason i was getting an error is because i was trying to use
row.cells(3).value.tostrin g
using just row.cells(3).value did not give me an error but did not validate comparison with nothing
either
row.cells(3).value.tostrin
using just row.cells(3).value did not give me an error but did not validate comparison with nothing
either
Dim rowCell8 As String
if (row.cells(8) Is Nothing)
rowCell8 = ""
else
rowCell8 = row.cells(8).value.ToStrin
and then replace any call to with row.cells(8) local variable rowCell8. Do the same construct for the other cells (3 and 12)