Solved

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

Posted on 2011-03-09
6
365 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 500 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 35

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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 35

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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