?
Solved

empty cells in Excel file give run-time errors, not sure how to handle properly

Posted on 2011-03-09
6
Medium Priority
?
375 Views
Last Modified: 2012-06-27
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
Comment
Question by:AlexF777
  • 3
  • 2
6 Comments
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 2000 total points
ID: 35088467
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
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 35088578
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
 

Author Comment

by:AlexF777
ID: 35100375
checking for nothing still produced an error
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 36

Expert Comment

by:Miguel Oz
ID: 35100477
Did you define variables after this line "If row.cells.count >= 12 Then"?
0
 

Author Comment

by:AlexF777
ID: 35100639
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
 

Author Comment

by:AlexF777
ID: 35100691
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question