iLogic Rule for Selecting Excel Cell

[Anonymous]

Hello,

I'm a long time Inventor user.  However I have just started exploring the power of iLogic.  I have written some recent rules to help automate some of our routine task.  I would like to create a rule that interfaces with an Excel file we use often that stores material information that we use to populate some of our iProperty parameters.  Mainly I'm looking to pull data from two cells in the workbook.  I have code to open the Excel file.  I would then like for the user to select a "cell" in Column B and have that value assigned to the "Stock Number"  iPropety parameter and based on that selection, find the value in the corresponding "G" column and assign that to the "Description" iProperty parameter.   I can't seem to find the iLogic code for prompting the user to select the desired Excel cell (with a mouse click) and assigning that to a variable to be used to fill out the iProperty.  Is there a method for accomplishing this or will a VBA snippet be required to accomplish it?

Any tips are greatly appreciated.  I have search the forums for some sample code but have come up empty.

[TONELLAL]

Hello,

I think the solution is more Inventor-side than Excel-side. I don't know if you can select a cell in Excel to send it to Inventor, but you can fill a multi-value Inventor parameter from Excel, so you can try :

-create a multi-value parameter in your Inventor template, in order to have it in all your files (or a rule that create the param)

-fill its values from Excel, with "MultiValue.List("d0")=GoExcel.CellValues("filename.xls", "Sheet1", "A2", "A10")

-put it on an external iLogic form

Hope this helps !

Alain

[marcin_otręba]

why you want to do it this way? why user must select cell ?

take a look at this:

https://forums.autodesk.com/t5/inventor-customization/ilogic-iproperty-value-according-to-keyword-in...

[Anonymous]

I guess I should explain a bit more about the Excel file.  This is a multi-sheet file with a lot of data in it.  It is a stock material database sort of speak.  Since we do not have a link to SAP directly through Inventor, we refer to this Excel file to search for our stock materials (plate, pipe, structural steel).  Each item has a unique stock number in column B (W2T number).  When we find the correct stock number, we copy clip and paste it into the Stock Number iProperty field and then go to the description (column G) of that W2T and copy clip it as well into the description iProperty field.  Each material type has its on sheet in the Excel file and the sheet name matches the material type (Plate, Pipe, ect..).  This Excel file is updated regularly and shared among different groups within our company.   The only method I found would be to enter the cell ID name for the W2T (Stock Number) and then have it place that in the variable for Stock Number and use its location to find the corresponding G column.  I was hoping to simply allow the user to select the W2T cell once they find it.   My plan was to have a user prompt that ask for the material type so that it would open the Excel file to that sheet.  That will make it easier to navigate.   Other methods or searching could be done but there are lots of variables that would need to be entered to find the correct material for each different type.  So I was trying to keep it simple for now.

[Anonymous]

That search method would likely work but given we have several different stock material types, it would require a lot of code to accomplish it.   The search criteria for each material would be different of course.  I will see about testing it on one of the sheets to see if I can get it to work.  Thanks for the link.

[WCrihfield]

I have learned to use Named Ranges within most of my important frequently referenced Excel files, that I want to link to from Inventor.  It is simple to use when the named range only includes a single cell, but when your named range includes more than one cell, it has to be treated as a 2 dimensional array, which requires a little bit more code.  One advantage I've noticed is that is it a lot more stable.  But if the named range only contains one cell, it works just like calling out the cells address, only smarter.  When someone edits the Excel file, and adds or deletes rows or columns, the named range maintains its cells & contents, and doesn't mess my Inventor process up.  There is a GoExcel.NamedRangeValue("RangeName") option available in the Snippets / System / Excel Data Links section, but it's not very informative, and states that it won't work on Embeded spreadsheets.  Also when using a Named Range, you don't have to specify what sheet it's on (if when you created the named range in Excel, you specified it that way).

[Anonymous]

Just wanted to let the group know that I was able to develop a method to search our Excel and make the selection automatically.  I'm still working on the guts of the code.  Thanks for every ones tips.