Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-09
6
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

618 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