CODEWITHSUNDEEP
vb.netexcelexcel-interop
Ebassador
Ebassador

Reputation: 349

Get Cell Value in Excel from Range

How can I extract the value of a cell from a range object? It seems like it should be simple. This Stackoverflow question and answers did not really help. Here is what I want to do but it fails every time with an exception on row.columns(0,0).

Dim rdr = oFindings.Range
For Each row As Excel.Range In rdr.Rows
   Dim Account = row.Columns(0,0).value2.tostring
   ' Do other stuff
Next

To make it work I had to implement this code:

For Each row As Excel.Range In rdr.Rows
    ndx = 0
    For Each cell As Excel.Range In row.Columns
        If ndx = 0 Then Account = NS(cell.Value2)
        ' Do other stuff
        ndx += 1
    next
Next

That seems like such a kludge. What is the secret?

Upvotes: 0

Views: 5900

Answers (1)

D_Bester
D_Bester

Reputation: 5931

Most of the problems have already been alluded to, but here is the answer with code.

    Dim rdr As Excel.Range = oFindings.Range
    For Each row As Excel.Range In rdr.Rows

        'explicitly get the first cell as a range 
        Dim aCell As Excel.Range = row.Cells(1, 1)

        'explicity convert the value to String but don't use .String
        Dim Account as string = CStr(aCell.Value2)

        If Not String.IsNullOrEmpty(Account) Then

            ' Do other stuff

        End If

    Next

Using aCell.Value2.toString will fail if the cell is empty because Value2 will be Nothing. Instead use CStr(aCell.Value2) which won't fail. But then you need to test if the string is null or empty before using the value.

Upvotes: 1

Related Questions