Update iPart/iAssembly table through Excel AND have table update model

[erichter]

I found a snippet of iLogic code that allows me to update the Excel table of an iPart or iAssembly, and I've linked to the source below. This bit of code works great, except that iLogic is unable to update the model based on the active row of the table. This is a problem, as since I am replacing all values in a column, that would include the active row as well.

For i = 2 to oFactory.TableRows.Count + 1
oCells.Item(i, 3) = "Text to replace"
Next

If I run the code, the active row will not change, but if I open the Excel table, I will get the message:

"The values in the Active Row of the table do not match the document's current values. Do you wish to update the table before continuing?"

To which I must answer "no". Then the table will open. If I then save and close the table, I will get the message:

"The values in the document do not match the Active Row of the Table. Do you wish to update the document before continuing?"

To which I must answer "yes" to finally update the model.

This is far more tedious than I would like. To my knowledge, these prompts cannot be bypassed even though I ALWAYS want to update the model from the table. This wouldn't be too much of a problem, except I must run this for multiple documents at once, and these tables are enormous and take a long time for each change to take affect. Therefore, this table change must be entirely automated.

So my question is if anyone knows how to force the model to be updated from the iPart/iAssembly table or a workaround that would achieve the same effect. For example, I know I can change all rows excluding the active row, then change active row to a different row, then edit the table for what was previously the active row, but I have not been able to achieve this in an iLogic script, and the function "iPart.ChangeRow" does not work for multiple open documents.

https://forums.autodesk.com/t5/inventor-ilogic-api-vba-forum/edit-filename-of-iassembly-table-member...

[theo.bot]

is it required to do the update in the excel workbook? If the values are not based on formula's you can edit the table directly. in that case you can simple edit the entire table including the default row. If you use excel you could pass the values from the changed cells to the parent object to update the model. So passing the value to the corresponding parameter for example will avoid that message.

here's a small sample to edit cells in the 4th column of a table:

Dim oDoc As PartDocument
oDoc = ThisDoc.Document 

Dim oComp As PartComponentDefinition
oComdef = oDoc.ComponentDefinition

Dim oRow As iPartTableRow
oValue = 10

'change cell values in the fourth column a length parameter
For Each oRow In oComdef.iPartFactory.TableRows
	oRow.Item(4).Value = oValue * oRow.Index & " mm"
Next

Here is a small sample using excel and write the value back

Dim oDoc As PartDocument
oDoc = ThisDoc.Document 

Dim oComp As PartComponentDefinition
oComdef = oDoc.ComponentDefinition

Dim oWorkSheet
 oWorkSheet = oComdef.iPartFactory.ExcelWorkSheet

Dim oCells
 oCells = oWorkSheet.Cells

Dim oRow As iPartTableRow
oValue = 10

oCells.Item(2,4) = oValue * 10 & " mm"
oCells.Item(3,4) = oValue * 20 & " mm"
oCells.Item(4,4) = oValue * 50 & " mm"
oCells.Item(5,4) = oValue * 80 & " mm"

Dim oWB
oWB = oWorkSheet.Parent

oWB.Save
oWB.Close

Dim oPara As Inventor.Parameter
oPara = oComdef.iPartFactory.TableColumns.Item(4).ReferencedObject

Dim UOM As UnitsOfMeasure
UOM = oDoc.UnitsOfMeasure

oPara.Value = UOM.ConvertUnits(Val(oComdef.iPartFactory.DefaultRow.Item(4).Value),UnitsTypeEnum.kMillimeterLengthUnits,UnitsTypeEnum.kDatabaseLengthUnits)
InventorVb.DocumentUpdate()

[erichter]

Unfortunately, I do need to use Excel formulas. The iAssemblies I'm working on are too complicated to function without them, as I am changing out table replaces, not dimensional parameters. The iPart table method works great if I don't need to use formulas, but I'm looking for a solution that can keep the formulas intact.

[A.Acheson]

The only solution I have found is to programically select the browser name of each key in question and force that error message to appear then accept it manually and move on. Would this help? The disadvantage is that you cannot skip the error message but at least it will appear consistently instead of leaving the update pending in the background. I believe changing an iPart’s iproperties through ilogic has also this affect and is a limitation. Here is the sample post for changing iproperties. Below is the code. The selection of the browser node and activation of the member is the business end of the code to trigger the error message. 

'https://forums.autodesk.com/t5/inventor-customization/automate-idw-drawing-file-creation-for-all-ipart-members/td-p/9420094
'http://inventortrenches.blogspot.com/2013/03/determine-file-type-for-ilogic-rule.html

'Instructions***************************************************
'Method A:Rule run when iProperties columns are added to the iPart factory table:
'Add iProperties columns to the iPart factory table before running this rule.
'1.This rule will loop through each member applying the iProperties then activate each member.
'2.This activation will force the System generated message box to appear reading
'"The values in the Active Row of the table do not match the document's current values. 
'Do you wish To update the table before continuing?"
'3. Manually accept this change and select yes.
'4. Cycle repeats.

'Method B:Rule run without iProperties columns added to the iPart factory table:

'1.This rule will loop through each member applying the iProperties then activate each member.
'2.iPart members are created/updated and  receive the iProperties but the factory iProperties will show only the last member the iProperties were added Too. 
'3.Cycle repeats.
'
'This method can be  confusing as you cannot check the results of the rules, Solution add any column that changes to the Table.
'Instructions End ***************************************************

	'start of iLogic rule - - - - - - - - - - - - - - - - - -

Check = MessageBox.Show("Add required iProperties to first member then add columns to iPart factory table" _
& vbNewLine & "" _
& vbNewLine & "If iProperty columns are not present only one member will have iProperties updated correctly" _
& vbNewLine & "in the factory file, member properties will be updated" _
& vbNewLine & "" _
& vbNewLine & "This rule will work on all iPart members,if there are many it will take a long time!" _
& vbNewLine , "Ilogic Instructions", MessageBoxButtons.YesNo, MessageBoxIcon.Warning)


If  Check = vbYes Then 
 	
	Dim oDoc As Document
	oDoc = ThisDoc.Document
	
    'Check if ODoc is a Part Document
	If oDoc.DocumentType = Inventor.DocumentTypeEnum.kPartDocumentObject  Then
	            'MessageBox.Show("This is a part file.", "iLogic")
	      

	
	' Set a reference to the component definition
	oDef = ThisApplication.ActiveEditDocument.ComponentDefinition

	 ' Make sure we have an iPart factory.
	    If oDef.IsiPartFactory  = False Then
	        MsgBox ("Chosen document is not a factory.", vbExclamation)
	        Exit Sub
	    End If
		
	' Set a reference to the factory.
	Dim oFactory As iPartFactory
	oFactory = oDef.iPartFactory

	'iPartF = oDef.iPartMember.ParentFactory
	  ' Get the number of rows in the factory.
	Dim iNumRows As Integer
	    iNumRows = oFactory.TableRows.Count 
	 
	Dim iRow As Integer
	'Set iRow = First Member


		Try
			For iRow = 1 To iNumRows
			'Change ipart Row
			iPart.ChangeRow("", iRow)
			
			'Use if memberfile name needed
			'MemberFileName = oFactory.FileNameColumn(iRow).Value
			'MessageBox.Show(MemberFileName, "Title")

			'[---Call external "function" or rule or do something---
			Dim Length As Decimal = 0.0

			Dim Height As Decimal = 0.0

			Dim Width As Decimal = 0.0

			 

			Length = Measure.ExtentsLength

			Height = Measure.ExtentsHeight

			Width = Measure.ExtentsWidth

			 

			Dim SLength As String = Length.ToString()

			Dim SHeight As String = Width.ToString()

			Dim SWidth As String = Height.ToString()

			 

			iProperties.Value("Custom", "LUNGHEZZA_EXT") = SLength

			iProperties.Value("Custom", "LARGHEZZA_EXT") = SWidth

			iProperties.Value("Custom", "ALTEZZA_EXT") = SHeight
			'


			'[Select by UI the node of current member in order to bring up the message ipart has change contents,
			'If this is not used iproperties will only apply to last member active
			'https://forums.autodesk.com/t5/inventor-customization/possible-to-check-all-members-of-factory-for-errors-using-ilogic/td-p/3863094


			Dim oErrorManager As ErrorManager
			   oErrorManager = ThisApplication.ErrorManager
			 
			Dim oTop As BrowserNode
			     oTop = oDoc.BrowserPanes("Model").TopNode
			   
			Dim bHasErrorOrWarning As Boolean
			'ThisApplication.SilentOperation = True
			   
		    ' Highlight the iPart table row 
		    oTop.BrowserNodes("Table").BrowserNodes.Item(iRow).DoSelect
		   
		    ' Activate the iPart table row this will trigger the do you want to change table contents accept yes to allow iproperties to stick to the member row. 
		    Dim oCommand As ControlDefinition
		    oCommand = ThisApplication.CommandManager.ControlDefinitions("PartComputeiPartRowCtxCmd")
		    oCommand.Execute
		     'ThisApplication.SilentOperation = False
			  
				 If oErrorManager.HasErrors Or oErrorManager.HasWarnings Then
				        MsgBox (oErrorManager.LastMessage, vbOKCancel)
				 End If
			']	
			
			
			'Rebuild as create member can sometimes fail
			
			ThisDoc.Document.Rebuild() 
			
			Try
				'Create each member
			 Call oFactory.CreateMember
			 Catch
				MessageBox.Show("Error creating Member", "iLogic") 
			 End Try
			Next
			
			
			
		Catch
		MessageBox.Show("Error Exiting", "iLogic")	
		End Try
		
		iLogicVb.UpdateWhenDone = True
		
			'open folder	
		oFolder = ThisDoc.Path &"\" & ThisDoc.FileName(False)
		Call Shell("explorer.exe" & " " & oFolder, vbNormalFocus)
	End If

 Else
 MessageBox.Show("Rule Canceled Exiting", "iLogic")
 End If

[erichter]

I figured out how to get the desired outcome by combining the Excel method with the direct table method you described in your first post.

The gist of this script is:

1. Use Excel to write a formula to all members of a column except the first member row (row 2).
2. Convert the formula in the first row to a value so it can be changed without Excel.
3. Save the desired formula as value in a blank cell so it can be referenced later.
4. Exit and save the Excel table.
5. Edit the iPart table with the non-Excel method by referencing the saved value from step 3 and update the model.
6. Make a second Excel edit to convert that value back into a formula.
7. Delete the temporary value that was generated in step 3.
8. Exit and save the Excel table again.

Inventor doesn't a see a conflict between the model and table and does not prompt to update.

The only downsides of this script are that it requires opening the Excel table twice, and I also have to define the formula twice. In the future I would like to make this work with only having to define the formula once to simplify the code. Also, I made the code to work with an iAssembly, but in the future I would expand it to be able to work with either an iPart or iAssembly. The problem with either of these changes is that I cannot define an object within an if enclosed block. But either way, I'm happy with this script.

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim odef As AssemblyComponentDefinition
odef = oDoc.ComponentDefinition

Dim oFactory As iAssemblyFactory
oFactory = odef.CreateFactory

Dim oWorkSheet
oWorkSheet = oFactory.ExcelWorkSheet

Dim oWB
oWB = oWorkSheet.Parent

Dim oCells
oCells = oWorkSheet.Cells

oCol = 27

For i = 3 to oFactory.TableRows.Count + 1
oCells.Item(i, oCol) = "=""SPA-""&IF(ISERR(SEARCH(""DP"",F" & i & ")),IF(LEFT(RIGHT(F2,3))=""M"",""E"",""C""),IF(LEFT(RIGHT(F" & i & ",3))=""M"",""D"",""B""))&IF(LEFT(RIGHT(F" & i & ",3))=""C"",""C"",)&""-""&MID(F" & i & ",7,3)&""_""&LEFT(D" & i & ",2)&""-""&RIGHT(F" & i & ")"
Next ' Change all rows except the first row

oRow = 2
x = oFactory.TableRows.Count + 3
oCells.Item(oRow, oCol) = oCells.Item(oRow,oCol).Value ' Convert existing formula to value

oFormula = "=""SPA-""&IF(ISERR(SEARCH(""DP"",F" & oRow & ")),IF(LEFT(RIGHT(F2,3))=""M"",""E"",""C""),IF(LEFT(RIGHT(F" & oRow & ",3))=""M"",""D"",""B""))&IF(LEFT(RIGHT(F" & oRow & ",3))=""C"",""C"",)&""-""&MID(F" & oRow & ",7,3)&""_""&LEFT(D" & oRow & ",2)&""-""&RIGHT(F" & oRow & ")"

oCells.Item(x, oCol) = oFormula
oCells.Item(x, oCol) = oCells.Item(x, oCol).Value ' Save formula under the table and convert to value

oWB.Save
oWB.Close

Dim oWorkSheet2
oWorkSheet2 = oFactory.ExcelWorkSheet

Dim oWB2
oWB2 = oWorkSheet2.Parent

Dim oCells2
oCells2 = oWorkSheet2.Cells

oFactory.TableRows.Item(oRow-1).Item(oCol).Value = oCells2.Item(x, oCol).Value ' Changes model state to desired value
oDoc.Update

oCells2.Item(oRow, oCol) = oFormula ' Reverts value back to formula
oCells2.Item(x, oCol).Delete ' Removes temporary value from under table

oWB2.Save
oWB2.Close

oDoc.Save

[erichter · Accepted solution]

I was able to refine the code to make it work more efficiently. The Excel formula only needs to be defined once, and the code now works with any row being the active row. The biggest change I made to get this to work is to define the formula as an ArrayList, so each row iteration can be referred to independently. It will also work on either iParts or iAssemblies. It should be pretty easy to modify this script to modify multiple table columns at once.

It now works as follows:

1. Use Excel to write a formula to all members of a column except the active row.
2. Convert the formula in the first row to a value so it can be changed without Excel.
3. Save the desired formula as value in a blank cell, save it as a variable, then delete the cell.
4. Exit and save the Excel table.
5. Edit the iPart table with the non-Excel method by referencing the saved value from step 3 and update the model.
6. Make a second Excel edit to convert that value back into a formula.
7. Exit and save the Excel table again.

oDoc = ThisApplication.ActiveDocument

oFactory = oDoc.ComponentDefinition.CreateFactory

Dim oWorkSheet
oWorkSheet = oFactory.ExcelWorkSheet

Dim oWB
oWB = oWorkSheet.Parent

Dim oCells
oCells = oWorkSheet.Cells

iEnd = oFactory.TableRows.Count + 3

iCol = 27

Dim oFormula as New ArrayList
oFormula.Add("")
oFormula.Add("")

For i = 2 to oFactory.TableRows.Count + 1

oFormula.Add("=""SPA-""&IF(ISERR(SEARCH(""DP"",F" & i & ")),IF(LEFT(RIGHT(F2,3))=""M"",""E"",""C""),IF(LEFT(RIGHT(F" & i & ",3))=""M"",""D"",""B""))&IF(LEFT(RIGHT(F" & i & ",3))=""C"",""C"",)&""-""&MID(F" & i & ",7,3)&""_""&LEFT(D" & i & ",2)&""-""&RIGHT(F" & i & ")")

If oFactory.TableRows.Item(i-1).MemberName = oFactory.DefaultRow.MemberName
iRow = i
oCells.Item(iRow, iCol) = oCells.Item(iRow, iCol).Value ' Convert existing formula to value in active row
Else
oCells.Item(i, iCol) = oFormula(i) ' Writes formula to all rows except active row
End If

Next

oCells.Item(iEnd, iCol) = oFormula(iRow) ' Save new formula under the table
oValue = oCells.Item(iEnd, iCol).Value ' Convert new formula to value
oCells.Item(iEnd, iCol).Delete ' Removes new formula from under table

oWB.Save
oWB.Close

Dim oWorkSheet2
oWorkSheet2 = oFactory.ExcelWorkSheet

Dim oWB2
oWB2 = oWorkSheet2.Parent

Dim oCells2
oCells2 = oWorkSheet2.Cells

oFactory.TableRows.Item(iRow-1).Item(iCol).Value = oValue ' Changes model state to desired value
oDoc.Update

oCells2.Item(iRow, iCol) = oFormula(iRow) ' Reverts active row value back to formula

oWB2.Save
oWB2.Close

oDoc.Save