Searching Excel with iLogic

This is an interesting iLogic solution to a problem where part inventories may dictate the values of a component in an assembly.
Perhaps the inventory changes somehow on a regular enough basis and modifying the actual code would not be practical; or the person who determines the value of that database is not an Inventor user.

This particular client had an ever expanding list of customers with varying requirements for the end product including machining tolerances, available fastener specifications and approved material lists to name a few.

As new customers were added it seemed to be a constant struggle to add the new values into the iLogic code and, believe it or not, sometimes the existing customers would change their mind - almost NEVER happens right? ;-)


Having a database in Excel format located in a network share and available for edit by someone not familiar with iLogic (or even Inventor for that matter) worked out very nicely in this case. Perhaps you can use the technique to solve some of your challenges as well. Of course, the actual solution for our client was more involved than this example, but we’ll get you started and offer a high level overview on searching Microsoft Excel to find a needed value.


First let’s start with the spreadsheet itself:

On “Sheet1” I have a row that lists all of the available diameters for a pin that we will place in our assembly as well as a row for all of the available lengths for that pin. This spreadsheet should be saved somewhere within the Project Path or on a network share where we can call out the specific location within the iLogic code.


Next I have an “assembly” that has a block with a hole in it and a pin constrained within the hole. The pin must change, of course, with the diameter of the hole as well as the length of the block but only within the values of our database of sizes.



Remember, we are assuming the database to be dynamic with values being added, modified and subtracted all the time.
Within the assembly I’ve placed the following lines of code in an iLogic rule:

i = GoExcel.FindRow("SearchExcelForBestFit.xlsx", "Sheet1", "Dia", "<=", Hole_Dia, "Length", ">=", thickness)
MsgBox("Best fit found in row #" & i)
Pin_Dia = GoExcel.CurrentRowValue("Dia")
Pin_Length = GoExcel.CurrentRowValue("Length")


The first line initiates the search in the spreadsheet with the syntax “GoExcel.FindRow("filename", "sheetname", "first column title", "operator", value to look for, "second column title", "operator", value to look for, "third column title",...)” , searching the Dia column for a value that is less than or equal to the diameter of the hole in the block and a Length that is greater than or equal to the block thickness.

A little message box will affirm a result and apply the values to the parameters of the pin with the GoExcel.CurrentRowValue code.

Use this methodology to effectively apply all sorts of values to your parts and assemblies to control parameters, iProperties, and standards information from an Excel database. Try the example here to get started.