BOM Export to Template including Instance Properties

[Thilo.Haedicke]

Hi, 
with the help of a nice Tutorial and Forum posts I've been able to Export the BOM to an excel Template at a defined row.
Now I also want to use the new Instance Properties, where we want to overwrite some Custom Iproperties (expecially because of Content Center files).

Is there any way to read out if an Custom Iproperty has been overwritten by an Instance Property? Then this Instance Property should be used instead of the Custom IProp.
Thanks in Advance!
In the following you can find the Ilogic Code:


Sub Main()

		Dim oAssydoc As AssemblyDocument
		oAssydoc = ThisApplication.ActiveDocument
		
		Dim oAssyCompDef As AssemblyComponentDefinition
		oAssyCompDef = oAssydoc.ComponentDefinition
				
		Dim oBOM As BOM
		oBOM = oAssyCompDef.BOM
		
		Dim oBOMView As BOMView
		
		'BOM View "Strukturiert"(GERMAN) = "Structured)
		oBOMView = oBOM.BOMViews.Item("Strukturiert")
		oBOM.StructuredViewEnabled = True
		oBOM.StructuredViewFirstLevelOnly = True
		
'BOM Export

	'Copy Job Information Spreadsheet from workspace
		Dim sJobBOMTempName As String = String.Concat("D:\Template-BOM.xlsx")
		Dim sJobBOMName As String = String.Concat("D:\BOM.xlsx")
		
		IO.File.Copy(sJobBOMTempName, sJobBOMName, True)

		Dim sMySheet As String = "BOM"
		Static iCurrRow As Integer = 9
		
		GoExcel.Open(sJobBOMName, sMySheet)
	
	'Write BOM values to Excel
		Call QueryBOMRowProperties(oBOMView.BOMRows, sJobBOMName, iCurrRow)
		oAssydoc.Save
	
	'Save Excel
		GoExcel.Save
		GoExcel.Close
	
End Sub

Public Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, JobSpreadsheetName As String, iCurrRow As Integer)
	' Iterate through the contents of the BOM Rows.
	Static CurrentRow As Integer = iCurrRow
	Dim i As Long
	Dim sWriteCell As String
	
	For i = 1 To oBOMRows.Count
		
		'Get the current row
		Dim oRow As BOMRow
		oRow = oBOMRows.Item(i)
		
		'Set a referance to the primary ComponentDefinition of the row
		Dim oCompDef As ComponentDefinition
		oCompDef = oRow.ComponentDefinitions.Item(1)
		
		Dim oPropSets As PropertySets
		oPropSets = oCompDef.Document.PropertySets
		
		oDesignTrackingPropertySet = oPropSets.Item("Design Tracking Properties")
		oInventorSummaryPropertySet = oPropSets.Item("Inventor Summary Information")
		oInventorDocSummaryPropertySet = oPropSets.Item("Inventor Document Summary Information")
		oCustomPropertySet = oPropSets.Item("Inventor User Defined Properties")
		
		'Get the file properties that are required
		oItemNumberProperty = oRow.ItemNumber
		oDescriptionProperty = oDesignTrackingPropertySet.Item("Description")

		sWriteCell = "A" & CurrentRow
		GoExcel.CellValue(sWriteCell.AsEnumerable) = oItemNumberProperty
		
		sWriteCell = "B" & CurrentRow
		GoExcel.CellValue(sWriteCell.AsEnumerable) = oRow.ItemQuantity
				
		sWriteCell = "C" & CurrentRow
		GoExcel.CellValue(sWriteCell) = oDescriptionProperty.Value
		
		'Write Custom Ipropterties to Excel "Werkstoffnorm"
		'HOW To Write INSTANCE PROPERTIES To EXCEL, WHERE SOME OF THESE CUSTOM iProperties HAVE BEEN OVERWRITTEN? 
		Try
      	oMatNormProperty = oCustomPropertySet.Item("Werkstoffnorm")
		sWriteCell = "E" & CurrentRow
		GoExcel.CellValue(sWriteCell) = oMatNormProperty.Value
		Catch
    	End Try
		
		Try
      	oBeschProperty = oCustomPropertySet.Item("Bescheinigung")
		sWriteCell = "F" & CurrentRow
		GoExcel.CellValue(sWriteCell) = oBeschProperty.Value
		Catch
		End Try

		CurrentRow = CurrentRow + 1
		
		'Recursively iterate child rows if present.
		If Not oRow.ChildRows Is Nothing Then
			Call QueryBOMRowProperties(oRow.ChildRows, JobSpreadsheetName, CurrentRow)
		Else	
		End If
		
	Next i
End Sub

[bradeneuropeArthur]

Does this help you further?

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/assembly-instance-property-api-refer...

[Thilo.Haedicke]

Thank you for this idea. I tried to work with this solution before. But due to my low experiances in programming I'm not able to get this VBA Code into my Ilogic.

So I tried the VBA Code instead of Ilogic from the inventor API Help "Using the BOM APIs API Sample" with the help of these Forum Posts:

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/vba-bom-export-with-excel-template/t...

..and integrated some code lines for writing the Instance properties and this works.

But there is a problem with the custom Iproperty of the same name "ABC": If the next item does not have this Custom Iproperty defined, then this item gets the Value of "ABC" from the item before/above.

There is the BOM Export excel file attached with some comments in Column J

Code:

Public Sub BOMExp()
' Set a reference to the assembly document.
' This assumes an assembly document is active.
Dim oDoc As AssemblyDocument
Set oDoc = ThisApplication.ActiveDocument

' Set a reference to the BOM
Dim oBOM As BOM
Set oBOM = oDoc.ComponentDefinition.BOM

' Set whether first level only or all levels.
oBOM.StructuredViewFirstLevelOnly = True

' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True

'Set a reference to the "Structured" BOMView
Dim oBOMView As BOMView
Set oBOMView = oBOM.BOMViews.Item("Strukturiert")

'Define Excel Template
Dim oTemplate As String: oTemplate = "C:\temp\Template.xlsx"

'set excel app and add worksheet
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open(oTemplate)
'Set xlwb = xlApp.workbooks.Add
Set xlws = xlwb.Worksheets(1)
xlApp.Visible = True

xlws.Name = "Stückliste " & oPartNumProperty

'Start Row for Property Export to Excel
Dim oStartRow As Integer: oStartRow = 9

Debug.Print "Item"; Tab(15); "Quantity"; Tab(30); "Part Number"; Tab(70); "ABC"
Debug.Print "----------------------------------------------------------------------------------"

'Initialize the tab for ItemNumber
Dim ItemTab As Long
ItemTab = -3

Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End Sub

Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long, ByVal xlApp As Object, ByVal xlwb As Object, ByVal xlws As Object, oStartRow As Integer)
ItemTab = ItemTab + 3
' Iterate through the contents of the BOM Rows.
Dim i As Long
For i = 1 To oBOMRows.Count
' Get the current row.
Dim oRow As BOMRow
Set oRow = oBOMRows.Item(i)

'Set a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
Set oCompDef = oRow.ComponentDefinitions.Item(1)

Dim oPartNumProperty As Property
Dim oABCProperty As Property

If TypeOf oCompDef Is VirtualComponentDefinition Then
'Get the file property that contains the "Part Number"
'The file property is obtained from the virtual component definition
Set oPartNumProperty = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Part Number")

'if the Custom Iproperty doesn't exist, go to next
On Error Resume Next
'Get the Custom property that contains the "ABC"
Set oABCProperty = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("ABC")

'write ItemNumber to Column 2 (=B)
xlws.Cells(oStartRow, 2) = oRow.ItemNumber

'write Custom Property of ABC to Column 9 (=I)
'Problem:Custom IProperty is written to excel, but if a ipt or iam does not contain this custom iproperty "ABC" the a row gehts the same Value like the last one (with custom Iproperty)
xlws.Cells(oStartRow, 9) = oABCProperty.Value
'write Instance Property of ABC to Column 9 (=I)
xlws.Cells(oStartRow, 9) = oRow.OccurrencePropertySets.Item(1).Item("ABC").Value

'Define next BOM Row
oStartRow = oStartRow + 1

Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oDescripProperty.Value
Else
'Get the file property that contains the "Part Number"
'The file property is obtained from the parent
'document of the associated ComponentDefinition.
Set oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")

'if the Custom Iproperty doesn't exist, go to next
On Error Resume Next
'Get the Custom property that contains the "ABC"
Set oABCProperty = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("ABC")

'write ItemNumber to Column 2 (=B)
xlws.Cells(oStartRow, 2) = oRow.ItemNumber

'write Custom Property of ABC to Column 9 (=I)
'Problem:Custom IProperty is written to excel, but if a ipt or iam does not contain this custom iproperty "ABC" the a row gehts the same Value like the last one (with custom Iproperty)
xlws.Cells(oStartRow, 9) = oABCProperty.Value
'write Instance Property of ABC to Column 9 (=I)
xlws.Cells(oStartRow, 9) = oRow.OccurrencePropertySets.Item(1).Item("ABC").Value

'Define next BOM Row
oStartRow = oStartRow + 1

Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
oPartNumProperty.Value; Tab(70); oABCProperty.Value

'Recursively iterate child rows if present.
If Not oRow.ChildRows Is Nothing Then
Call QueryBOMRowProperties(oRow.ChildRows, ItemTab, xlApp, xlwb, xlws, oStartRow)
End If
End If
Next
ItemTab = ItemTab - 3
End Sub