Export BOM with all levels into custom excel

[sachin_kumar6CZM2]

Hi Experts,

i want to export my BOM with all levels into a custom excel template. i also wants to add custom i properties to the excel. i have used the following 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

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

oBOMView.Sort("Part Number", True)
oBOMView.Renumber(1, 1)

  Call QueryBOMRowProperties(oBOMView.BOMRows, xlApp, xlWorkbook, xlWorksheet, row)
End If
	'get target folder path
	oFolder = oPath & "\" & oDate & oName & " KIT BOM"
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, ByVal row As Integer)
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 oPartNumProperty As Inventor.Property
	If rDoc.DocumentType = kAssemblyDocumentObject And _
		rDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value.Contains("K") Then
	
		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
		Try
			xlWorksheet.Range("C" & row).Value = customprop.Item("Frame Height").Value
		Catch
			
		End Try
		Try
			xlWorksheet.Range("D" & row).Value = customprop.Item("Frame Width").Value
		Catch
		
		End Try
		xlWorksheet.Range("E" & row).Value = bRow.ItemQuantity
		xlWorksheet.Range("F" & 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
			End If

Next


End Sub


but whenever i run this, i am still getting only the top level assembly in the excel. childrens are not exporting to the BOM

But whenever i run this, i am still getting only the top level assembly in the excel. Corresponding children's are not exporting to the BOM

Could anyone please help me with this. i don't know whether it is due to any error in ilogic rule. 

[daltonNYAW9]

Is the "row" value keeping up when you find 'ChildRows'?
try changing how the row variable is passed from byVal to byRef

ByRef row As Integer

[bradeneuropeArthur · Accepted solution]

You need to loop also for the childrows!