How to check if an excel file cell has a certain value on load from datagridview vb.net

Question

I'm hoping to find a way to check if a certain cell in my excel file contains certain values.
Right now I'm pulling excel data into a datagridview in vb.net.
For instance, if only numbers are allowed in my cell but a letter is in place, I want the program to automatically change invalid entries to default values. Also I'm hoping to have an integer variable that increments by one if invalid entries are found.

Pseudocode:

First I need to define some type of integer variable named invalidcell,  
and initialize to 0.  
For each worksheet  
    For each cell in each worksheet 
        If an invalid cell is read then:
             the invalid cell value is replaced by the default value,  
             and the invalidcell is incremented by 1.
        End If
    End loop
End loop

Then when all the cells have been imported, display an error message if the invalidcell is > 0, that says how many invalidcells were found.

I greatly appreciate any help or suggestions anyone can give.

Do you want to open the workbook via Interop or just query it via Oledb? The latter is certainly faster — rwisch45
Then you can still use my answer below because the basic principle is the same. Use IsNumeric to check if the value is a number or not. — rwisch45

rwisch45 rwisch45 · Accepted Answer · 2013-07-17T00:54:44

Well you can use IsNumeric to determine if it is a number or not. Then just declare a variable to count the invalid cell values.

Private Sub FillGridView()

'..Declare Excel application, workbook, range etc
Dim myValue as Integer = 0
Dim iCounter as Integer = 0
If IsNumeric(cell.Value2) Then
     'this is a number
     myValue = cell.Value2
Else
     'this is not a number - increment counter and set default value
     iCounter += 1
     myValue = 0
End If

If iCounter > 0 Then MsgBox(iCounter & " invalid cells were found.")

End Sub

and just add myValue to your grid view with whatever procedure you are using.

How to check if an excel file cell has a certain value on load from datagridview vb.net

1 Answers