ilogic vlookup excel parameters

[Anonymous]

our finished product will be shipped out in carton tubes.  There is a huge number of these carton tubes and we currently have an excel spreadsheet to list the part number and it's dimensions from left to right.

Could someone help me write an ilogic code that can use the iproperties, "Part Number" to look up inside an excel spreadsheet for that part number and return several numbers that can then be used as user parameters?

Please and thank you.

[Curtis_Waguespack]

Hi @Anonymous ,

Just for clarification are you looking to still use the Excel file, or are you looking to contain the table information in an external ilogic rule... both can be done, it's largly a matter of preference, and how data will be added/modified/updated.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

[Curtis_Waguespack]

Attached is quick example file of doing this in the rule ....note this is done in an internal rule for this example but you would want this to be done as an external rule to ensure that the table data is in one location and is not getting copied over and over, etc.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

'[ table data
Dim oTABLE_LIST As New ArrayList
    ''     PN | Length | Width | Diameter
oTABLE_LIST.add("123|1|2|0.5|")
oTABLE_LIST.add("124|2|3|0.75|")
oTABLE_LIST.add("125|3|4.5|1.0|")

']


Dim oPN_LIST As New ArrayList

'Add Part number to list
For Each oItem In oTABLE_LIST     
            
        '[ split using vertical bar char
        Dim oTable As String() = oItem.Split(New Char() {"|"c})
        
        oPN_LIST.add (oTable(0) )
        
        ']
        

Next

'set list to parameter
MultiValue.List("Part_Number") = oPN_LIST


'look up part number in table list
For Each oItem In oTABLE_LIST     
            
        '[ split using vertical bar char
        Dim oTable As String() = oItem.Split(New Char() {"|"c})
        
        oTable_PN = oTable(0) 
        oTable_Length = oTable(1) 
        oTable_Width = oTable(2) 
        oTable_Diameter = oTable(3) 
        ']
        
        '[ set parameters based on Part Number
        If  oTable_PN = Parameter("Part_Number")  Then        
            Parameter("Width") = oTable(1) 
            Parameter("Length") = oTable(2) 
            Parameter("Diameter") = oTable(3) 
        End If
        ']
Next


InventorVb.DocumentUpdate()

[Curtis_Waguespack · Accepted solution]

To do it with the data in Excel you would use something like this:

oPath = "C:\Users\cwaguespack\Downloads\"
oFileName = "PN Lookup.xlsx"

oPN = Parameter("Part_Number")

'get row using part number 
oRow = GoExcel.FindRow(oPath & oFileName , "Sheet1", "PN", "=", oPN)

Parameter("Width") = GoExcel.CellValue(oPath & oFileName, "Sheet1",  "B" & oRow ) 'get value in 2nd column
Parameter("Length") = GoExcel.CellValue(oPath & oFileName, "Sheet1", "C" & oRow) 'get value in 3rd column
Parameter("Diameter") = GoExcel.CellValue(oPath & oFileName, "Sheet1", "D" & oRow ) 'get value in 4th column

InventorVb.DocumentUpdate()

[Anonymous]

Thank you, love this forum.  🙂

this is working for me and I can take it from here.  Much appreciation!