Ilogic rule to add instance property to Template excel from BOM

[sachin_kumar6CZM2]

Hi Experts,

I am trying to export my BOM to an template excel and I am successfully doing this. but now i have added a instance property to all the subassemblies since that particular property changes depending on the parent assembly to which the subassemblies are placed. Please find the snippet below(instance property is named "phase")

But I couldn't find a way to export this instance property also to the excel. 

My current code is working fine for iproperties and custom property. I am not posting the full code here. Just a glimpse of the code

Dim docPropertySet As PropertySet
	
		docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
		customprop = rDoc.PropertySets.Item(“Inventor User Defined Properties”)
		SummaryPropertySet = rDoc.PropertySets.Item("Summary Information")
		
	
		
        xlWorksheet.Range("B" & row).Value = bRow.ItemNumber
		xlWorksheet.Range("C" & row).Value = docPropertySet.Item("Part Number").Value
		xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value

this code is working fine and its exporting all the BOM items like part number, description etc. to my own template excel.

Could anyone please help me to figure out a way to add that instance property also.

your help is very much appreciated. 

[WCrihfield]

Hi @sachin_kumar6CZM2.  That is clearly not your whole code for this task, because it does not include a lot of other necessary stuff.  Instance properties are not accessed within the 'Document.PropertySets' collection.  They are stored within the ComponentOccurrence.OccurrencePropertySets (which represents a different PropertySets collection), after the ComponentOccurrence.OccurrencePropertySetsEnabled property has been set to True.  Since this special PropertySets collection is on the assembly component occurrence, and not the actual document that the component occurrence references, its data is stored in the assembly that the component occurrence is directly located within.  If approaching this from the point-of-view of the assembly's BOM, you would have to first get the ComponentOccurrencesEnumerator from the BOMRow.ComponentOccurrences property, so you can iterate through them, because each individual ComponentOccurrence can possibly have different properties, or different values for those properties, since these are 'instance' based.  Because the BOMRow.OccurrencePropertySets property represents the entire BOMRow, not any specific single instance, among multiple instances being represented by this one row.

[sachin_kumar6CZM2]

Hi @WCrihfield ,

Thanku for your reply..

yes that is not full code.. I didn't post the full code since I thought it would make the post longer.

Below is my full code..

Sub main()
	
	If ThisApplication.ActiveDocument.DocumentType <> kAssemblyDocumentObject Then
    MessageBox.Show("Please run this rule from the assembly file.", "iLogic")
    Exit Sub
End If

Dim TemplateFolder As String = ThisApplication.DesignProjectManager.ActiveDesignProject.TemplatesPath
Dim ExcelTemplate As String = TemplateFolder &"\EDT_T-PA25-DT200_BoM.xltx"

If System.IO.File.Exists(ExcelTemplate) = False Then
MessageBox.Show("Cannot locate the BoM Excel Template. Make sure it is saved under the "  _
& "name 'EDT_T-PA25-DT200_BoM' in the current project template folder" & vbNewLine _
& vbNewLine & vbNewLine & vbNewLine &"Click OK to open the Template folder", "BoM Excel template unavailable")
Shell("explorer.exe " & TemplateFolder, vbNormalFocus) 
Exit Sub
End If
Dim MissingList As Collection = New Collection
Choice = InputRadioBox("Select the BoM type", "Phase BoM", "KIT BoM", True, Title := "Export BoM")

oPath = ThisDoc.Path
oName = ThisDoc.FileName(False) 'without extension
Dim oDate As String = Now().ToString("yyMMdd-HHmm_")
oDate = oDate.Replace(":", "")  ' & " - " & TypeString

Dim oDoc As AssemblyDocument

oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = False
Dim oBOMView As BOMView
xlApp = CreateObject("Excel.Application")

'comment out or change to false 
'in order to not show Excel
xlApp.Visible = False

workspace = ThisDoc.WorkspacePath()
xlWorkbook = xlApp.Workbooks.Open(ExcelTemplate)
Dim row As Integer
row = 4



If Choice Then 
	
'get target folder path
oFolder = oPath & "\" & oDate & oName & " Phase BOM"
	
oBOMView = oBOM.BOMViews.Item("Structured")
ThisApplication.CommandManager.ControlDefinitions.Item("AppUpdateMassPropertiesCmd").Execute
xlWorksheet = xlWorkbook.Worksheets("KITs List")
xlWorksheet.Activate






  Call QueryBOMRowProperties(oBOMView.BOMRows, xlApp, xlWorkbook, xlWorksheet, row)
  
  
  
 Else	
	xlApp.DisplayAlerts = False
	xlWorkbook.Worksheets("KITs List").Delete 
	xlApp.DisplayAlerts = True
	
	'get target folder path
	oFolder = oPath & "\" & oDate & oName & " KIT BOM"
	

End If

row = 4
Dim iBOM As BOM
iBOM = oDoc.ComponentDefinition.BOM

iBOM.PartsOnlyViewEnabled = True

Dim iBOMView As BOMView
iBOMView = iBOM.BOMViews.Item("Parts Only")
ThisApplication.CommandManager.ControlDefinitions.Item("AppUpdateMassPropertiesCmd").Execute

xlWorksheet = xlWorkbook.Worksheets("Parts List")
xlWorksheet.Activate

iBOMView.Sort("Part Number", True)
iBOMView.Renumber(1,1)
Dim ibRow As BOMRow
ibRows = iBOMView.BOMRows
For Each ibRow In ibRows
	
		
		
	If ibRow.ComponentDefinitions.Item(1).Type = "100675072" Then
		Dim VirtualDoc As ComponentDefinition = ibRow.ComponentDefinitions.Item(1)
		Dim docPropertySet As PropertySet
		docPropertySet = VirtualDoc.PropertySets.Item("Design Tracking Properties")
		customprop = VirtualDoc.PropertySets.Item(“Inventor User Defined Properties”)

		xlWorksheet.Range("A" & row).Value = docPropertySet.Item("Part Number").Value
		xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Description").Value
		xlWorksheet.Range("C" & row).Value = docPropertySet.Item("Material").Value
		Try
		xlWorksheet.Range("D" & row).Value = customprop.Item("Finish Spec Code").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Finish")
		End Try
		Try
		xlWorksheet.Range("E" & row).Value = customprop.Item("MaxL").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-MaxL")
		End Try
		Try
		xlWorksheet.Range("F" & row).Value = customprop.Item("Width").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Width")
		End Try
		Try
		xlWorksheet.Range("G" & row).Value = customprop.Item("Thickness").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Thickness")
		End Try
		xlWorksheet.Range("H" & row).Value = ibRow.ItemQuantity
	Else
		Dim rDoc As Document
		rDoc = ibRow.ComponentDefinitions.Item(1).Document
		Dim docPropertySet As PropertySet
		docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
		customprop = rDoc.PropertySets.Item(“Inventor User Defined Properties”)

		xlWorksheet.Range("A" & row).Value = docPropertySet.Item("Part Number").Value
		xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Description").Value
		xlWorksheet.Range("C" & row).Value = docPropertySet.Item("Material").Value
		Try
		xlWorksheet.Range("D" & row).Value = customprop.Item("Finish Spec Code").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Finish")
		End Try
		Try
		xlWorksheet.Range("E" & row).Value = customprop.Item("MaxL").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-MaxL")
		End Try
		Try
		xlWorksheet.Range("F" & row).Value = customprop.Item("Width").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Width")
		End Try
		Try
		xlWorksheet.Range("G" & row).Value = customprop.Item("Thickness").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Thickness")
		End Try
		xlWorksheet.Range("H" & row).Value = ibRow.TotalQuantity
		xlWorksheet.Range("I" & row).Value = ibRow.ComponentDefinitions.Item(1).Document.ComponentDefinition.MassProperties.Mass
		
	End If
	
	row = row + 1
	
Next
Dim Message As String = ""

'For Each iList As String In MissingList
'	Message = Message & iList & vbNewLine
'Next
'If MissingList.Count = 0 Then
'Else
'MessageBox.Show(Message & vbNewLine & "Click OK to copy the List to clipboard", _
'"The rule didn't run on these " & MissingList.Count & " Read Only Parts")
'Clipboard.SetText(Message)
'End If
	'get target folder path
	
xlWorksheet1 = xlWorkbook.Worksheets("Cover")
xlWorksheet1.Activate
xlWorksheet1.Range("BD21").Value = iProperties.Value("Project", "Part Number")
xlWorksheet2 = xlWorkbook.Worksheets("Verification")
xlWorksheet2.Activate
xlWorksheet2.Range("B4").Value = Now().ToString("dd/MM/yy")

'Check for the folder and create it if it does not exist
If Not System.IO.Directory.Exists(oFolder) Then
System.IO.Directory.CreateDirectory(oFolder)
End If

Dim Filename As String = oFolder & "\" & oName & ".xlsx"
'If you want to save the workbook in a specified name
xlWorkbook.SaveAs(Filename)
xlWorkbook.Close
xlApp.Quit

'Show the folder
MessageBox.Show("New Files Created in: " & vbLf & oFolder, "iLogic")
'Open the folder containing the new files
Shell("explorer.exe " & oFolder, vbNormalFocus) 

End Sub

Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator,ByVal xlApp As Object, ByVal xlWorkbook As Object, ByVal xlWorksheet As Object, ByRef row As Integer)

Dim i As Long
For i = 1 To oBOMRows.Count
Dim bRow As BOMRow
    bRow = oBOMRows.Item(i)
Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document

Dim MissingList As Collection = New Collection

		Dim docPropertySet As PropertySet
	
		docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
		customprop = rDoc.PropertySets.Item(“Inventor User Defined Properties”)
		SummaryPropertySet = rDoc.PropertySets.Item("Summary Information")
		
	
		
        xlWorksheet.Range("B" & row).Value = bRow.ItemNumber
		xlWorksheet.Range("C" & row).Value = docPropertySet.Item("Part Number").Value
		xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
		Try
			xlWorksheet.Range("E" & row).Value = customprop.Item("Frame Height").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Frame Height")
		End Try
		Try
			xlWorksheet.Range("F" & row).Value = customprop.Item("Frame Width").Value
		Catch
		MissingList.Add(docPropertySet.Item("Part Number").Value & "-Frame Width")
		End Try
		Try
			xlWorksheet.Range("G" & row).Value = customprop.Item("MaxL").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-MaxL")
		End Try
		xlWorksheet.Range("H" & row).Value = bRow.ItemQuantity
		'xlWorksheet.Range("H" & row).Value = docPropertySet.Item("Material").Value
		Try
		xlWorksheet.Range("I" & row).Value = customprop.Item("Finish Spec Code").Value
		Catch
			MissingList.Add(docPropertySet.Item("Part Number").Value & "-Finish")
		End Try
		'Try
		'xlWorksheet.Range("A" & row).Value = customprop.Item("Phase Number").Value
		'Catch
			'MissingList.Add(docPropertySet.Item("Part Number").Value & "-Phase Number")
		'End Try
		xlWorksheet.Range("J" & row).Value = bRow.ComponentDefinitions.Item(1).Document.ComponentDefinition.MassProperties.Mass
		
          
		row = row + 1
	
		If Not bRow.ChildRows Is Nothing Then
                    Call QueryBOMRowProperties(bRow.ChildRows, xlApp, xlWorkbook, xlWorksheet, row)
                End If
			

Next


End Sub

I went through your reply but i am not very well versed with iLogic programming, is there any chance you could help me through a code representation ? 

[WCrihfield]

You are right...that is a really long code.  In cases like this, where the code is really long, I generally recommend that you copy your rule code over into a new text document (using Notepad.exe or similar), then save it to C:\Temp\ folder, then attach that text file to the forum reply, as an attachment.  Much neater, takes up less space in the forum discussion screen, and is convenient.  When some folks see a longer than expected code example, they can get discourage from wanting to get involved with it, because it can seem like it it would require investing more time into it than they are willing to.  We all have our own projects going on where we work, so we have to be wise about how we spend our time helping others.  With that in mind, I will consider reviewing all that code at, some point, to see if I can figure out how to incorporate the added functionality, but I offer no guarantees.  Not being familiar with your assembly, or your included set of files, or the layout of your Excel file, or its formatting, it will be more difficult for someone else to deal with.  But I also can not download or open ZIP files from forums, due to corporate security policies where I work, so attaching a Pack-n-go of your assembly and excel file for testing would not help.