[RESOLVED] Get Contents of Cell in Excel Problem

[stimbo]

This has me stuck. I've tried everything I can and still no joy. Why wont the following work. I'm getting no error messages and the console is writing out the 'Not Found' message so it's doing all the code.

For this example I've stripped the code right down to the most basic. I write text into a column of excel cells with the phrase "FIND ME". I then want to say if the content of the cell (pick one from the populated range of cells) = "FIND ME" then Do stuff. It wont work. I've declared variables etc... here's the relevant code:

moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)  'Create Excel Object
 
        moApp.Visible = False                                   'Make it invisible
 
        Dim myFileName As String = "D:\My Documents\helpMe2.xls" 'This is the file to open
        Dim oWB As Excel.Workbook = moApp.Workbooks.Open(myFileName)    'Open the workbook
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Time"), Excel.Worksheet)  'Open Worksheet Name
 
        Console.WriteLine("Got here")
 
        For i As Integer = 1 To 40
            oSht.Cells(i, 1) = "FIND ME"  'Write Phrase in Cells as a test - it works fine
        Next
 
        If oSht.Cells(10, 1).ToString = "FIND ME" Then  'Example: select a cell from populated range to check
            Console.WriteLine("Found it")
        Else : Console.WriteLine("Didn't Find it")   'This is all I ever get
        End If
 
        oWB.Save()
        oWB.Close(True)
        oSht = Nothing
        oWB = Nothing

I tried assigning the value that was in one of the populated cells to a String variable and then output it:

Dim myValue As String = oSht.Cells(10, 1).ToString   'put value from cell into String
 
oSht.Cells(2, 2) = myValue          'Now output it into different cell to see what it is

All I got was this in the cell: System.__ComObject

What does that mean???

I tried different formats such as these but to no avail:

If oSht.Cells.Item(10, 1).ToString = "FIND ME" Then   'etc
 
 If oSht.Cells(10, 1) Is "FIND ME" Then                      'etc

I'm totally beaten and have gone past the angry stage to just curious as to what the answer is. What have I done wrong? Anyone got the answer please?

[jmcilhinney]

I've not done much Office Automation so this is an intuitive answer and I can't guarantee that it is correct.

This code:

If oSht.Cells(10, 1).ToString = "FIND ME" Then

is getting an Excel cell, converting it to a string and then comparing that to "FIND ME". What you need to do is get the CONTENTS of the cell, cast that as String and then compare that to the target. You get the contents of a cell via its Value property.

[stimbo]

It was intuititive to me as well but didn't work. I have tried every combination I could think of (value, etc...).

I think it's a casting problem of sorts. Found this link on MSDN about that strange message I got when i put the value into a string and outputted it again but can't really figure out if the answer is in front of me. Casting

I tried casting an excel range without any joy and a few others (Strings etc... but it aint working).

I really thought this was going to be the easy bit!

[stimbo]

It only took about 20 hours but hey.

For anyone who may be interested:

'Need to specify cell as an Excel Range - cast it
  Dim myRange As Excel.Range = CType(oSht.Cells(10, 1), Range)  'Will replace cell values with array i to loop
 
'Then to compare
   If myRange.Value.ToString = "FIND ME" Then  'check from populated range to 
            Console.WriteLine("Found it")
        Else : Console.WriteLine("Didn't Find it")   'This is all I get
   End If

EDIT: Or simply something like -

If Not CType(oSht.Cells(10, 1), Range).Value.ToString = "FIND ME" Then  
            Console.WriteLine("Not Found")
        Else : Console.WriteLine("Blimey, Found it")   
  End If

It has already become a better day.