Looking for an iLogic rule to retrieve a part number from excel

[Anonymous]

Hi, looking for help with an iLogic rule that will retrieve a part number(s) from an excel sheet. I work for a company that builts vacuum trucks and I'm trying to make an .iam template where the user can select different lengths of shells to achive the desire overall length. I have been able to achive this using iLogic rules and forms and making the shell length parameter multivalue. But now I need to show predetermined SAP part numbers for the shells in BOM or part list. I all ready have an excel sheet with shell sizes with corresponding part numbers on file. I also thought about incorporating our custom CC parts/shells that already have the SAP part numbers set up as a custom iProperty but might be to complicated for a newbie iLogic user. Any help would be appricated Gordon Inventor 2012

[jletcher]

http://inventortrenches.blogspot.com/2011/06/ilogic-use-part-iproperty-to-get-file.html

Check this out...

[jletcher]

This is not what you want but it also may help..

http://inventortrenches.blogspot.com/2011/04/using-excel-and-ilogic-to-retrieve-part.html

[Anonymous]

Ok, Itried second option... no errors but didn't work.

[jletcher]

I don't see where you define or assign Front_Shell, what makes it find different part numbers Front_Shell it a parameter not a value so how does inventor know it is a different size and find in excel? The parameter does not change so what defines the look up in the excel?

[jletcher]

I have a question is there a reason you are using excel to do this and not just ilogic?

[Anonymous]

Doesn't a parameter equal a value? Front_Shell is a multi-VALUE parameter. Like I said I'm a newbie at iLogic. If (GoExcel.CellValue("C" & rowSL) = "Front_Shell") Then What value would I replace Front_Shell with?

[jletcher]

Front_Shell is the name of the parameter it does not change I think you need to have the value to be able to look it up in the spreadsheet. Not sure I just link you there.

I will go look at it and see But I am learning ilogic myself so not sure how much help I would be...

But you are asking it to look up the value of the Front_Shell but I think your code has it looking at the named parameter not the value. But let me look at the blog and see..

[Curtis_Waguespack]

Hi  jletcher and gordon.rogers,

Here's a quick example that might help.

Where C:\Temp\Test Part Number.xlsx looks like this:

You can use a rule such as this. Here I've added an array list to select from but that information will come from your size parameter in the real world. The size is then used to set the part number iproperty based off of the corresponding cell value.

'define the XLS to look at
myXLS = "C:\Temp\Test Part Number.xlsx"
GoExcel.Open(myXLS, "Sheet1")

'set the list of sizes to choose from
Dim myArrayList As New ArrayList
i = 1
Do until i = 10+1
myArrayList.add(i) 
i = i + 1
Loop

'present the list to the user
mySize= InputListBox("Select a size", myArrayList, "", "iLogic", "Sizes")

'find the row
myRow = GoExcel.FindRow(myXLS, "Sheet1", "Size", "=",mySize)

'find the cell
myCell = GoExcel.CellValue(myXLS, "Sheet1", "B" & myRow)

'set the part number
iProperties.Value("Project", "Part Number") = myCell 

'present the result
MessageBox.Show("The Part Number is: " & iProperties.Value("Project", "Part Number"), "iLogic")

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

[jletcher]

Sweet

[Anonymous]

Hi Curtis, I tried this and got this error message. See attachments

[jletcher]

 it is because you have empty rows his code does not have it where if there is an empty row bypass.

[Curtis_Waguespack]

Hi gordon.rogers,

Here's an example that will deal with the blank rows. Also this version builds the arraylist from the XLS file each time (rather than building the list internally in the iLogic code).

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

'define the XLS to look at
myXLS = "U:\iLogic examples\Shells.xls"
GoExcel.Open(myXLS, "Sheet1")

'set the list of sizes reading from the XLS file
Dim myArrayList As New ArrayList
i = GoExcel.FindRowStart 
Do until i = 22 + GoExcel.FindRowStart  ' 22 rows plus the start row position
myArrayList.add(GoExcel.CellValue(myXLS, "Sheet1", "C" & i)) 
i = i + 1
Loop

'present the list to the user
mySize= InputListBox("Select a size", myArrayList, "", "iLogic", "Available sizes")

'find the row
'looks for a row in the "Sizes" column that has a  value greater than zero (blank cells return -1)
'and looks for a row in the "Sizes" column equal to mySize
myRow = GoExcel.FindRow(myXLS, "Sheet1", "Sizes", ">" ,0, "Sizes", "=",mySize) 

'find the cell value
myCell = GoExcel.CellValue(myXLS, "Sheet1", "A" & myRow)

'set the part number
iProperties.Value("Project", "Part Number") = myCell 

'present the result
MessageBox.Show("The Part Number is: " & iProperties.Value("Project", "Part Number"), "iLogic")