Question Conversion from String Error - but I don't understand why it's happening?

[thebatfink]

Hi,

I have a small program which is opening and scraping Excel work books and loading the data into a local SqlCe database.

One of the cells I am trying to scrape is causing me no end of problems and I don't understand why it's the case. It doesn't make any sense to me.
The cell.value can contain any of the following types of data:
- Integer
- Decimal
- String (specifically 'ERR')

What I need to do is convert whatever is there, into an integer. I have been doing this by using conditional arguements to test the contents, if its a string, my integer is 0. If its not a string, I perform an integer.tryparse and then finally set the integer to 0.. but for some reason the following fails..

Dim PlanWorkBook As Excel.Workbook
Dim PlanWorksheet As Excel.Worksheet
....

PlanFileName = HMFileList(i)
PlanWorkBook = ExcelApp.Workbooks.Open(PlanFileName, [ReadOnly]:=True, UpdateLinks:=False)
For Each Sheet As Excel.Worksheet In PlanWorkBook.Worksheets
If Sheet.CodeName = "Sheet2" Then
WorkSheetindex = Sheet.Index
End If
Next
PlanWorksheet = PlanWorkBook.Sheets(WorkSheetIndex)

Now if I use the following to test for the cell containing the string "ERR", I get an error saying
'Conversion from string "ERR" to type "Double" is not valid'

If PlanWorksheet.Range("A" & j & "").Value = "ERR" Then

But if I use the following to test, it works ok and captures the presence of the string?

If Right(PlanWorksheet.Range("A" & j & "").Value, 3) = "ERR" Then

So I have found a working solution to my problem, but I would really appreciate if someone could give me an understanding as to why the first test is trying to convert the cell contents to type double before making the comparison!
Thanks!

 

[JohnH]

why the first test is trying to convert the cell contents to type double before making the comparison!

It is trying to convert your "ERR" string to Double to compare it with the cell.Value that is already a Double.

You should not convert all values to string for comparison, you can check the what type the value is with TypeOf Operator (Visual Basic)

 

[thebatfink]

It is trying to convert your "ERR" string to Double to compare it with the cell.Value that is already a Double.

You should not convert all values to string for comparison, you can check the what type the value is with TypeOf Operator (Visual Basic)

Hi, thanks for the reply.

I gather 'typeof' will return what type Cell.Value object is, and not what Cell.Value's value is?

I just realised as soon as I read your reply that to take text from the cell, I should use the Cell.Text object. As I am always trying to convert these to integers with integer.tryparse, I guess I could get all the cell contents via Cell.Text and tryparse the ones I need and the others will be strings anyway.

A throw back to working with Excel VBA.

Or am I missing your point with typeof usage?

Thanks!

 

[JohnH]

I just realised as soon as I read your reply that to take text from the cell, I should use the Cell.Text object. As I am always trying to convert these to integers with integer.tryparse, I guess I could get all the cell contents via Cell.Text and tryparse the ones I need and the others will be strings anyway.

I would say that is a bad idea. Most of the values you're after is numeric already, so why convert them to strings only to convert them back to numeric values again?

If you want to check if cell value is Double: If TypeOf cell.Value Is Double Then...