Update parameters with Excel

manu.marjanen · ‎10-19-2021

How to make iLogic code that updates model parameters from excel?
I don't want link between Excel and Inventor.

iLogic code:
Open Excel, update model parameters and close Excel.

A.Acheson · ‎10-20-2021

In the ilogic editor under excel tab use go excel snippets to give the path and cell reference and you can let it be equal to user/model parameter. The excel sheet can be embedded in the document or used externally through file path.

https://knowledge.autodesk.com/support/inventor-products/learn-explore/caas/CloudHelp/cloudhelp/2014...

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan

WCrihfield · ‎10-20-2021

Here is a working example iLogic rule that uses those built-in iLogic GoExcel tools that @A.Acheson mentioned. It should work for the example image you posted, because it worked in my tests. You may just have to edit a few things to fit your situation. The file name, sheet name, title row, find row start row, and how many rows you want it to loop through can all be modified to suit your specific needs. I just modified it to match the data shown in the example in your image.

Dim oDocType As DocumentTypeEnum = ThisApplication.ActiveDocumentType
Dim oUParams As UserParameters
If oDocType = DocumentTypeEnum.kPartDocumentObject Or _
	oDocType = DocumentTypeEnum.kAssemblyDocumentObject Then
	oUParams = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
ElseIf oDocType = DocumentTypeEnum.kDrawingDocumentObject Then
	oUParams = ThisDrawing.Document.Parameters.UserParameters
End If
Dim oUParam As UserParameter


'specify Excel source file & sheet name
oFile = "C:\Temp\MyParams.xlsx"
oSheet = "Sheet1"
GoExcel.Open(oFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oRow, oCol As Integer
For oRow = 3 To 5
	Dim oName As String = GoExcel.CellValue("A" & oRow)
	If String.IsNullOrEmpty(oName) Then Exit For
	Dim oEquation As String = GoExcel.CellValue("B" & oRow)
	If String.IsNullOrEmpty(oEquation) Then Exit For
	Dim oUnits As String = GoExcel.CellValue("C" & oRow)
	If String.IsNullOrEmpty(oUnits) Then Exit For
	Dim oComment As String = GoExcel.CellValue("D" & oRow)
	Dim oExists As Boolean = False
	For Each oUParam In oUParams
		If oUParam.Name = oName Then
			oExists = True
			oAnswer = MsgBox("A parameter named '" & oName & "' already exists." & vbCrLf & _
			"Do you want to overwrite its value?", vbYesNo + vbQuestion + vbDefaultButton2, "PARAM EXISTS")
			If oAnswer = vbYes Then
				oUParam.Expression = oEquation
				If Not String.IsNullOrEmpty(oComment) Then
					oUParam.Comment = oComment
				End If
			Else
				Exit For 'exits the parameters loop, not the Excel rows loop
			End If
		End If
	Next
	If oExists = False Then
		oUParam = oUParams.AddByExpression(oName, oEquation, oUnits)
		If Not String.IsNullOrEmpty(oComment) Then
			oUParam.Comment = oComment
		End If
	End If
Next
GoExcel.Close

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡 or you can Explore My CONTRIBUTIONS

Wesley Crihfield

(Not an Autodesk Employee)

manu.marjanen · ‎10-20-2021

Thanks, this seems to work fine.

If I don't know how many rows will always come from Excel.
Is it wise to change this value so large that it is not exceeded?

For example,
For oRow = 3 to 1000

One more thing, can you change the code a bit?
I tried it myself but did not succeed.

The message box only indicates that the data transfer is complete, no other message box.

WCrihfield · ‎10-21-2021

Yes. It would be OK to set that last row count in the loop to a higher number than you expect to need, because the code already includes several lines which make sure there are values in the 3 critical cells in each row (Name, Equation, & Units). Since Comment is not critical, I deal with that differently. If the Comment cell was empty, I simply set its value as an empty string. Because if that cell was empty were empty, it might not have given our oComment variable a value, and that would cause it to throw an error when setting this variable as the value of the parameters comment. But when we set its value to an empty string, that avoids that potential error.

I'm not sure what other messages you are wanting, but I did add one more, where I thought it would be most useful in this situation. Since your initial post suggests your main goal is to update existing parameters, instead of creating new ones, I added a notification question in there for when a parameter by that name is not found, and asks you if you want to let the rule create that parameter for you. If you click No, it will skip to the next row in Excel. If you click Yes, it will create that parameter. I hope that is what you wanted.

Here is the updated code:

Dim oDocType As DocumentTypeEnum = ThisApplication.ActiveDocumentType
Dim oUParams As UserParameters
If oDocType = DocumentTypeEnum.kPartDocumentObject Or _
	oDocType = DocumentTypeEnum.kAssemblyDocumentObject Then
	oUParams = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
ElseIf oDocType = DocumentTypeEnum.kDrawingDocumentObject Then
	oUParams = ThisDrawing.Document.Parameters.UserParameters
End If
Dim oUParam As UserParameter

'specify Excel source file & sheet name
oFile = "C:\Temp\MyParams.xlsx"
oSheet = "Sheet1"
GoExcel.Open(oFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oRow, oCol As Integer
For oRow = 3 To 1000
	Dim oName As String = GoExcel.CellValue("A" & oRow)
	'if the Name cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oName) Then Exit For
	Dim oEquation As String = GoExcel.CellValue("B" & oRow)
	'if the Equation cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oEquation) Then Exit For
	Dim oUnits As String = GoExcel.CellValue("C" & oRow)
	'if the Units cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oUnits) Then Exit For
	Dim oComment As String = GoExcel.CellValue("D" & oRow)
	If String.IsNullOrEmpty(oComment) Then oComment = ""
	'if the Comment cell of this row is empty, it will NOT exit this main loop of rows
	Dim oExists As Boolean = False
	For Each oUParam In oUParams
		If oUParam.Name = oName Then
			oExists = True
			oAnswer = MsgBox("A parameter named '" & oName & "' already exists." & vbCrLf & _
			"Do you want to overwrite its value?", vbYesNo + vbQuestion + vbDefaultButton2, "PARAM EXISTS")
			If oAnswer = vbNo Then
				Exit For 'exits the parameters loop, not the Excel rows loop
			ElseIf oAnswer = vbYes Then
				oUParam.Expression = oEquation
				oUParam.Comment = oComment
			End If
		End If
	Next
	If oExists = False Then
		oAns = MsgBox("No parameter was found named " & oName & "." & vbCrLf & _
		"Do you want it to be created?", vbYesNo + vbQuestion + vbDefaultButton2, "iLogic")
		If oAns = vbNo Then
			Exit For  'exits the parameters loop, not the Excel rows loop
		ElseIf oAns = vbYes Then
			oUParam = oUParams.AddByExpression(oName, oEquation, oUnits)
			If Not String.IsNullOrEmpty(oComment) Then
				oUParam.Comment = oComment
			End If
		End If
	End If
Next
GoExcel.Close

Wesley Crihfield

(Not an Autodesk Employee)

manu.marjanen · ‎10-21-2021

My proplem is:

Can existing parameters be updated so that there is no need to select messagebox yes or no? ("Do you want to overwrite its value?", vbYesNo)
If the existing parameter is found then update it (No messagebox)

This messagebox, if parameter by that name is not found select yes or no is good. ("Do you want it to be created?", vbYesNo)

I tried to change this myself but didn't really succeed.

WCrihfield · ‎10-21-2021

OK. I understand that. I have modified the code to get rid of that message, and just update existing parameters without notification.

Dim oDocType As DocumentTypeEnum = ThisApplication.ActiveDocumentType
Dim oUParams As UserParameters
If oDocType = DocumentTypeEnum.kPartDocumentObject Or _
	oDocType = DocumentTypeEnum.kAssemblyDocumentObject Then
	oUParams = ThisApplication.ActiveDocument.ComponentDefinition.Parameters.UserParameters
ElseIf oDocType = DocumentTypeEnum.kDrawingDocumentObject Then
	oUParams = ThisDrawing.Document.Parameters.UserParameters
End If
Dim oUParam As UserParameter

'specify Excel source file & sheet name
oFile = "C:\Temp\MyParams.xlsx"
oSheet = "Sheet1"
GoExcel.Open(oFile, oSheet)
GoExcel.DisplayAlerts = False
GoExcel.TitleRow = 2
GoExcel.FindRowStart = 3
Dim oRow, oCol As Integer
For oRow = 3 To 1000
	Dim oName As String = GoExcel.CellValue("A" & oRow)
	'if the Name cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oName) Then Exit For
	Dim oEquation As String = GoExcel.CellValue("B" & oRow)
	'if the Equation cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oEquation) Then Exit For
	Dim oUnits As String = GoExcel.CellValue("C" & oRow)
	'if the Units cell of this row is empty, it will exit this main loop of rows
	If String.IsNullOrEmpty(oUnits) Then Exit For
	Dim oComment As String = GoExcel.CellValue("D" & oRow)
	If String.IsNullOrEmpty(oComment) Then oComment = ""
	'if the Comment cell of this row is empty, it will NOT exit this main loop of rows
	Dim oExists As Boolean = False
	For Each oUParam In oUParams
		If oUParam.Name = oName Then
			oExists = True
			oUParam.Expression = oEquation
			oUParam.Comment = oComment
		End If
	Next
	If oExists = False Then
		oAns = MsgBox("No parameter was found named " & oName & "." & vbCrLf & _
		"Do you want it to be created?", vbYesNo + vbQuestion + vbDefaultButton2, "iLogic")
		If oAns = vbNo Then
			Exit For  'exits the parameters loop, not the Excel rows loop
		ElseIf oAns = vbYes Then
			oUParam = oUParams.AddByExpression(oName, oEquation, oUnits)
			If Not String.IsNullOrEmpty(oComment) Then
				oUParam.Comment = oComment
			End If
		End If
	End If
Next
GoExcel.Close

Wesley Crihfield

(Not an Autodesk Employee)

manu.marjanen · ‎10-21-2021

Thanks for the help, now this is working the way i want.

Community

Update parameters with Excel

Update parameters with Excel

Update parameters with Excel