[RESOLVED] Newbie question

[stimbo]

Quite simply - why wont this work?
I'm not getting any errors. I've checked and it's definitely opening/referencing the correct excel file. I've kept it this simple just to see if I can get the basics working... (I can't )

The console is writing out the Not Found message. It's definitely there! I even copied and pasted a load of other 1's around just in case.

Why, oh why???

Imports Excel
Imports Microsoft.Office.Core
 
Private moApp As Excel.Application
 
        moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        moApp.Visible = True
        Dim myFileName As String = "D:\My Documents\helpMe2.xls"
        Dim oWB As Excel.Workbook = moApp.Workbooks.Open(myFileName)
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Time"), Excel.Worksheet)
 
        Console.Write("Got here")
        If oSht.Cells(3, 1) Is "1" Then 'found, take items from this row and next row
            Console.Write("Found Number")
            oSht.Cells(5, 6) = "Found"
        Else
            Console.Write("Nothing Found")
        End If
 
        oWB.Close(True, myFileName)
        oSht = Nothing
        oWB = Nothing

I also tried

Cells.Items

.ToString = "1"

Nothing works. Please put me out of my misery.

[Xizeta]

why not just use 1 as an integer instead of a string ? If autocalculation is on "1" will be converted to the integer 1

[stimbo]

It doesn't matter if it's a String or an Integer.

If I put text in there, which is what will actually be in the cells, it still doesn't work.

Something I've done is just deeply flawed I think. Just don't know what.

[DKenny]

Try this

If oSht.Cells(3, 1).Value = 1

[stimbo]

The intellisense isn't allowing me the .Value option after the cell reference. Only .ToString and a few other things. I can do osht.Cells.Value

I put this code in to try and check for things. It writes to the file without a problem, saves it, closes it. No errors at all. It just wont find the damn word that has to be in the cell in this example because it has just written it itself!!
Stil writes the didn't find message. What is going on??? I've tried every combination I can think of.

Console.WriteLine("Got here")
 
        For i As Integer = 1 To 40
            oSht.Cells(i, 1) = "FIND ME"  'Write Phrase in Cells 
        Next
 
        If oSht.Cells(10, 1).ToString = "FIND ME" Then  'Example: selected a cell from populated range to check
            Console.WriteLine("Found it")
        Else : Console.WriteLine("Didn't Find it")
        End If

[DKenny]

OK, try this - you will need to Dim rngCheckCell as an excel range.

Set rngCheckCell = oSht.Cells(3, 1)
        
        If rngCheckCell.Value = 1 Then
            Console.Write ("Found Number")
            Set rngCheckCell = oSht.Cells(5, 6)
            rngCheckCell.Value = "Found"
        Else
            Console.Write ("Nothing Found")
        End If

[stimbo]

Nope. Still no joy. Thank you for the input though.

I'm going for a ciggy before I put the computer through the window.
I declared it as follows. Hope it's right. Because I've got Option Strict and Explicit On I also had to also cast it like so...


Dim rngCheckCell As Excel.Range
rngCheckCell = CType(oSht.Cells(3, 1), Range)

etc....

[stimbo]

I tried something different to see what would happen. After filling in cells with a string value I then assigned one of the cell's values to a String variable. The outputted it into a clear cell to see what it would say:

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

Here is what was in the Excel cell as the value:
System.__ComObject


What is that about??

[stimbo]

Hi Declan,
I finally got it working. It was the strange COM value being written to the excel file allowed me to narrow down that it was a casting issue in particular and hence the excel range was probably the most likely answer.

The answer was a mix of both your answers. I finally got it working. Maybe this is exactly what you meant in the first place. If so, my apologies for not picking up on it. So 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