Hello all
I’m learning a lot about connecting Excel with Inventor 2016. I know that Inventor can be linked from Parameters dialog box to an excel file. (Fx and then Link)
I also learnt how to connect one parameter with a cell from excel.
I need your help with this scenario:
I need m1 parameter to be linked with the number of excel of U21 row and column A Length. (and m2 with B Length)
U21 is the file name.
In my example, m1 to be connected with cell C5; C5 is at the intersection of U21 row (which is my Inventor file name) and A Length column.
Later, when people create a new part from template, by naming the file accordingly, both parameters to be automatically populated with values from this Master excel file.
I really appreciate your help.
radu
Solved! Go to Solution.
Solved by chandra.shekar.g. Go to Solution.
Hi Radu,
Currently, parameters can be linked to only single value of excel cell.
In order to link multiple values from excel, through iLogic code can be achieved. Please find the same iLogic code in the following.
Dim docFolder As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments MultiValue.List("m1") = GoExcel.CellValues(docFolder & "\Master.xlsx", "Sheet1", "C5", "C7") MultiValue.List("m2") = GoExcel.CellValues(docFolder & "\Master.xlsx", "Sheet1", "F5", "F7")
Dim docFolder As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments
For more information about the above iLogic code, please go through the following link.
Prepared a template with iLogic rule called "MultiVlaue List" and attached with this post.
Please download attachment ipt file and locate the file at "C:\Users\Public\Documents\Autodesk\Inventor 2017\Templates". Make sure that Master.xlsx file should available at "C:\Users\%user name%\Documents\". If excel file is not available at "C:\Users\%user name%\Documents\", then iLogic code will throw an error.
Please feel free to contact if there is any doubt.
If solves your problem, click on "Accept as solution" / give a "Kudo".
Thanks and regards,
I'm using Inv 2016, cannot open the file.
Better to post the entire code and will create a rule for it. I will update it for Templates 2016 location.
Are m1 and m2 updating their values based on the file name?
If I rename the file U23 then m1 and m2 parameters must read the values 700 and 500.
I really appreciate your help.
radu
you can also email me at
rstancescu@bensonglobal.com
I was able to install Inv 2018, moved the files as requested and see what's happening.
What actually I need to achieve is to have single user parameters. When I rename the file to U22 then m1 and m2 to read from Master.xlsx the values for row U22 (m1 = 500 and m2 = 250)
If I name the file U23, m1 and m2 to read from excel 700 and 500.
Hopefully this is possible.
Thank you again
radu
Hi Radu,
Please find the following iLogic code to update parameters on saving to respective file name.
Dim docFolder As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments If ThisDoc.FileName(False) = "U21" Then Parameter("m1") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "C5") Parameter("m2") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "F5") Else If ThisDoc.FileName(False) = "U22" Then Parameter("m1") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "C6") Parameter("m2") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "F6") Else If ThisDoc.FileName(False) = "U23" Then Parameter("m1") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "C7") Parameter("m2") = GoExcel.CellValue(docFolder & "\Master.xlsx", "Sheet1", "F7") End If InventorVb.DocumentUpdate() ThisApplication.ActiveView.Fit()
Prepared a template in Inventor 2016 attached as "Sample.ipt". copy and paste the template at "C:\Users\Public\Documents\Autodesk\Inventor 2016\Templates". Later, it can be easily upgrade to Inventor 2017 and 2018.
An iLogic rule called "Parameter change on saving" is created using above iLogic code as shown below.
In template file, go to "Manage" tab -> "iLogic" panel -> iLogic rule -> click on "Event Triggers" button. On clicking, dialog called "Rules triggered by Events" will appear to trigger the iLogic rule. "Parameter change on saving" will be triggered on "after save document" event as shown in the following image.
Make sure that, Master.xlsx file is available at "C:\Users\%username%\Documents\"
Please feel free to contact if there is any doubt.
If solves your problem, click on "Accept as solution" / give a "Kudo".
Thanks and regards,
Thank you for update. I do have one more question,
Could m1 parameter find the excel value by looking at the intersection of row starting with the file name and column A Length?
Right now, m1 is looking to a named/defined cell; if rows are re-ordered, C5 cell changes the value and m1 gets messed up.
So if I change the file name from U21 to U22 then m1 to update automatically and read 500 instead of 300.
Appreciate all your help.
radu
Hi Radu,
Please find sample iLogic code to finding row of file name and respective length values.
Sub Main() Dim docFolder As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments Dim m1Value As String Dim m2Value As String If ThisDoc.FileName(False) = "U21" Then FindRowValue(docFolder, "U21", "C", m1Value) FindRowValue(docFolder, "U21", "F", m2Value) If m1Value = "" OrElse m2Value = "" Then MessageBox.Show("File name or parameters not found", "Inventor") Else Parameter("m1") = m1Value Parameter("m2") = m2Value End If Else If ThisDoc.FileName(False) = "U22" Then FindRowValue(docFolder, "U22", "C", m1Value) FindRowValue(docFolder, "U22", "F", m2Value) If m1Value = "" OrElse m2Value = "" Then MessageBox.Show("File name or parameters not found", "Inventor") Else Parameter("m1") = m1Value Parameter("m2") = m2Value End If Else If ThisDoc.FileName(False) = "U23" Then FindRowValue(docFolder, "U23", "C", m1Value) FindRowValue(docFolder, "U23", "F", m2Value) If m1Value = "" OrElse m2Value = "" Then MessageBox.Show("File name or parameters not found", "Inventor") Else Parameter("m1") = m1Value Parameter("m2") = m2Value End If End If InventorVb.DocumentUpdate() ThisApplication.ActiveView.Fit() End Sub Sub FindRowValue(ByVal path As String, ByVal cellName As String, ByVal cell as String, ByRef value As String) value = "" For rowcell = 2 To 50 If GoExcel.CellValue(path & "\Master.xlsx", "Sheet1", "A" & rowcell) = cellName Then value = GoExcel.CellValue(cell & rowcell) End If Next End Sub
Prepared a template and attached with this post.
Please make sure that column should not change. Number of rows are limited to 50. If any extensions need to modify iLogic code.
Please feel free to contact if there is any doubt.
If solves your problem, click on "Accept as solution" / give a "Kudo".
Thanks and regards,