[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

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.GetDirectoryInfo(strPath2ProcessExcelMileageFiles).GetFiles(strWildCard2Process)
         Dim strExceptionMileagePath As String = strPath2ProcessExcelMileageFiles & "\Exceptions\" & objExcelFile.Name & "_Exceptions.txt"
         Dim strOkMileagePath As String = strPath2ProcessExcelMileageFiles & "\Ok\" & objExcelFile.Name & "_Ok.txt"
         My.Computer.FileSystem.WriteAllText(strExceptionMileagePath, _
            "Precision" & vbTab & "Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII)
         My.Computer.FileSystem.WriteAllText(strOkMileagePath, _
            "Incident" & vbTab & "Distance" & vbCrLf, False, System.Text.Encoding.ASCII)

         If objExcelFile.Name.StartsWith("~$") Then
            Continue For
         End If
         xlWorkBook = xlApp.Workbooks.Open(objExcelFile.FullName)
         xlWorkSheet = xlWorkBook.Worksheets("Incidents With Mileage")
         '        For Each xlWorkSheet In xlWorkBook.Worksheets
         For Each row In xlWorkSheet.UsedRange.Rows
            If row.cells.count >= 12 Then
               My.Computer.FileSystem.WriteAllText(strMilesCrossReferencePath & "\" & _
                                                   row.cells(8).value.ToString & ".txt", _
                                                   row.cells(12).value.ToString & vbCrLf, False)
               If row.cells(3).value Is Nothing Then
                  My.Computer.FileSystem.WriteAllText(strExceptionMileagePath, _
                  " " & vbTab & row.cells(8).value.ToString & vbTab & row.cells(12).value.ToString & vbCrLf, True, System.Text.Encoding.ASCII)
               Else
                  Select Case row.cells(3).value.ToString
                     Case "Precision" ' just a header
                        Exit Select
                     Case "address"
                        My.Computer.FileSystem.WriteAllText(strOkMileagePath, _
                           row.cells(8).value.ToString & vbTab & row.cells(12).value.ToString & vbCrLf, True, System.Text.Encoding.ASCII)
                     Case Else
                        My.Computer.FileSystem.WriteAllText(strExceptionMileagePath, _
                        row.cells(3).value.ToString & vbTab & row.cells(8).value.ToString & vbTab & row.cells(12).value.ToString & vbCrLf, True, System.Text.Encoding.ASCII)
                  End Select
               End If
            End If
         Next
         xlWorkBook.Close()
      Next
0
AlexF777
Asked:
AlexF777
  • 3
  • 2
1 Solution
 
gamarrojgqCommented:
Hi,

If you do not care about knowing if the cells are blank or not (you just want to prevent the errors) try to add ""  to the cells when you use them instead of use value.ToString, like this

("" & ow.cells(12).value)

So, this line

My.Computer.FileSystem.WriteAllText(strExceptionMileagePath, _
                  " " & vbTab & row.cells(8).value.ToString & vbTab & row.cells(12).value.ToString & vbCrLf, True, System.Text.Encoding.ASCII)

Will becemo like this

My.Computer.FileSystem.WriteAllText(strExceptionMileagePath, _
                  " " & vbTab & ("" & row.cells(8).value) & vbTab & ("" & row.cells(12).value) & vbCrLf, True, System.Text.Encoding.ASCII)
0
 
Miguel OzSoftware EngineerCommented:
To avoid run time error, just check that row.cells(8) is not null before using it.
Dim rowCell8 As String
if (row.cells(8) Is Nothing)
  rowCell8 = ""
else
  rowCell8 = row.cells(8).value.ToString()

and then replace any call to with row.cells(8) local variable rowCell8. Do the same construct for the other cells (3 and 12)
0
 
AlexF777Author Commented:
checking for nothing still produced an error
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Miguel OzSoftware EngineerCommented:
Did you define variables after this line "If row.cells.count >= 12 Then"?
0
 
AlexF777Author Commented:
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

 
0
 
AlexF777Author Commented:
the reason i was getting an error is because i was trying to use

row.cells(3).value.tostring

using just row.cells(3).value did not give me an error but did not validate comparison with nothing
either
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now