Inventor populate iProperties from Excel using iLogic problem

[luke.davenport]

Hi, 

I’ve been researching a solution to this but have had no luck so far…

I am trying to use an iLogic rule to populate iProperties (some generic, some custom) from a part number spreadsheet (containing columns such as PART NUMBER, DESCRIPTION, FINISH etc) and the rule keeps throwing up the following error:

'Conversion from string "" to type 'Double' is not valid.'

Here’s part of the code (would be extended for the required custom iproperties but I’m assuming the same principle is ok)

StartRule:

'read excel file

GoExcel.Open("C:\DESIGN\Documents\EXCEL FILES\PART NUMBERS", "PARTS")

'index row 3 through 10000

For rowPN = 3 To 10000

'find first empty cell in PART NUMBER column (Column A)

 If (GoExcel.CellValue("A" & rowPN) = "") Then

'create a variable for the value of the cell that is one row up from the empty cell row

    myPN = GoExcel.CellValue("A" & rowPN - 1)

     myDesc = GoExcel.CellValue("B" & rowPN- 1)

         Exit For

 End If

Next

'check to see if Part Number is the same as the file name or blank

If iProperties.Value("Project", "Part Number") = ThisDoc.FileName(False) _  

Or iProperties.Value("Project", "Part Number") = "" Then

'set iProperty to read value in from excel

iProperties.Value("Project", "Part Number")  =  myPN

'set iProperty to read value in from excel

iProperties.Value("Project", "Description")  =  myDesc

Changing the format of the excel data (number or text) has no effect. Is manipulation of the format required so that Inventor can read text and numbers to iProperties in this way? Or have I missed something obvious?  

I am using Inventor 2010 and Excel 2007.

Many thanks for your help

Luke

[thomaskennedy · Accepted solution]

Your error comes from the data in excel being formatted wrong for the check you are doing. 

Two ways to solve this one:

Change

If GoExcel.CellValue("A"&rowPN)= "" Then

To
If GoExcel.CellValue("A"&rowPN)= 0 Then....

OR 

Ensure your cells are formatted as text. If your numbers are stored as text you should see the green triangle in the top left corner of each cell.

One thing I've found though, If you enter your numbers in excel as normal, then change the format to 'Text' you need to go through and 'refresh' the cells (either re-enter the number or hit F2 then enter on every cell to change).

Hope this helps.

Tom

[luke.davenport]

Hi Tom,

So using "" is suitable for checking a blank text cell, and 0 is suitable for checking a blank number (double format) cell? Thank you for your quick reply, very much appreciated. Will check if this fixes it tonight.

Luke

[thomaskennedy]

Exactly. The default value for a double value is '0'.

No problem!

Tom

[luke.davenport]

Nice one. It works fine now. Thanks Tom for saving me a lot of time!

Luke

[LSA-skan]

Hi

Sorry to intrude on this solved case, but i would like to know how  this code would look, when using it in VBA?

/LSA