parameters to be linked within Excel

[RStancescu]

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 have an Inv file called U21.ipt (see attached)
• I have two user parameters m1 and m2
• I have an Excel file with multiple columns and multiple rows. (see attached)

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

[chandra.shekar.g]

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.

https://forums.autodesk.com/t5/inventor-customization/save-pdf-or-dwf-to-my-documents-ilogic/m-p/702...

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,

[RStancescu]

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

[RStancescu]

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

[chandra.shekar.g]

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,

[RStancescu]

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

[chandra.shekar.g · Accepted solution]

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,

[RStancescu]

That works, appreciate your help!