Solved

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

Posted on 2011-03-09
6
361 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now