Export BOM Model Data to Excel

[b.mccarthy]

Hello.

I have several use cases where I need to export the information contained in the BOM Model Data tab. I have seen other posts here, but the rules do not give me what I need:

• Export the "Model Data" tab only (not "Structured" or "Parts Only") to *.xls or *.xlsx (not *xml) using the assembly name
• Leave all settings as is, i.e. do not modify BOM Structure
• Save the excel file in the same directory as the assembly

I possess minimal coding skills and I have no idea where to start with this, so any help is appreciated.

Thank you.

[Andrii_Humeniuk]

Hi @b.mccarthy . Unfortunately you cannot export BOM "Model Data " (link). Explain why you don't want to use Structured, or Parts Only. Perhaps we will help you better understand these BOMs.

Structured BOM export example:

Sub main
	Dim oDoc As Document = ThisDoc.Document
	If Not TypeOf oDoc Is AssemblyDocument Then Exit Sub
	Dim oAsmDoc As AssemblyDocument = oDoc
	Dim oModelBOM As BOMView = oAsmDoc.ComponentDefinition.BOM.BOMViews(2) 'Structured
	Dim sPath As String = IO.Path.GetDirectoryName(oDoc.FullDocumentName) & "\"
	Dim sName As String = IO.Path.GetFileNameWithoutExtension(oDoc.FullDocumentName) & ".xls"
	oModelBOM.Export(sPath & sName, FileFormatEnum.kMicrosoftExcelFormat)
	System.Diagnostics.Process.Start("explorer.exe", "/select,""" & sPath & sName & """")
End Sub

[Michael.Navara]

Directly it is not possible as @Andrii_Humeniuk mentioned above. But you can handle Export to Excel yourself. Here is code sample where you can start your implementation.

Sub main
	'Setup
	Dim asm As AssemblyDocument = ThisDoc.Document
	Dim modelBomView As BOMView = asm.ComponentDefinition.BOM.BOMViews(1)
	Dim excelFileName As String = System.IO.Path.ChangeExtension(asm.FullFileName, ".xlsx")

	'Exported data
	rows = New List(Of List(Of Object))
	rows.Add(GetBomHeader())
	IterateBomRows(modelBomView.BOMRows, 0)
	
	'Save data to Excel
	WriteDataToExcel(excelFileName)

End Sub

Private rows As List(Of List(Of Object))

Sub IterateBomRows(oBomRows As BOMRowsEnumerator, level As Integer)
	For Each oBomRow As BOMRow In oBomRows
		rows.Add(GetBomRowData(oBomRow, level))

		'Recursive call
		If Not oBomRow.ChildRows Is Nothing Then IterateBomRows(oBomRow.ChildRows, level + 1)
	Next
End Sub

Function GetBomHeader() As List(Of Object)
	Dim header As New List(Of Object)
	header.Add("Level")
	header.Add("ItemNumber")
	header.Add("ItemQuantity")
	header.Add("TotalQuantity")
	header.Add("BOMStructure")
	header.Add("Part Number")
	header.Add("Title")

	Return header
End Function

Function GetBomRowData(oBomRow As BOMRow, level As Integer) As List(Of Object)
	Dim xlsRow As New List(Of Object)
	Dim compDef As ComponentDefinition = oBomRow.ComponentDefinitions(1)
	Dim propSets As PropertySets = compDef.Document.PropertySets

	xlsRow.Add(level)
	
	xlsRow.Add(oBomRow.ItemNumber)
	xlsRow.Add(oBomRow.ItemQuantity)
	xlsRow.Add(oBomRow.TotalQuantity)

	xlsRow.Add(System.Enum.GetName(GetType(BOMStructureEnum), compDef.BOMStructure))

	xlsRow.Add(propSets("{32853F0F-3444-11D1-9E93-0060B03C1CA6}")("Part Number").Value)
	xlsRow.Add(propSets("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}")("Title").Value)

	Return xlsRow
End Function

Sub WriteDataToExcel(xlsFileName As String)
	StartExcel()
	
	Dim workBook = GoExcel.Application.Workbooks.Add()

	Dim rowsCount As Integer = rows.Count
	Dim columnsCount As Integer = rows(0).Count

	Dim data(rowsCount, columnsCount) As Object
	For c As Integer = 0 To columnsCount - 1
		For r As Integer = 0 To rowsCount - 1
			data(r, c) = rows(r)(c)
		Next
	Next

	Dim columnName As String = Char.ConvertFromUtf32(columnsCount + 64)

	workBook.Worksheets(1).Range("A1:" & columnName & rowsCount).Value2 = data

	workBook.SaveAs(xlsFileName)
	workBook.Close()
	
	GoExcel.QuitApplication
End Sub

Sub StartExcel
	'You need to enable COM engine for excel in iLogic settings
	Try
		GoExcel.Open("")
	Catch
	End Try
End Sub

[b.mccarthy]

Thank you both for your help.

@Andrii_Humeniuk 

Your code works great to generate the Structured excel sheet exactly as the export tool does, and I will definitely use it. I just have to remember to sort the list before running the rule (if you can add some code the will sort by Part Number and then renumber the list, then all will be right with the world!) It does not, however, generate a sheet based upon the Model Data tab. I thought by changing the "2" in this line:

Dim oModelBOM As BOMView = oAsmDoc.ComponentDefinition.BOM.BOMViews(2) 

to a "0" would trick the code, but Inventor was not happy with that...

@Michael.Navara 

Your code is comprehensive and publishes all levels of the assembly, especially the "Level 0" data, which is exactly what I need to access. However, the exported sheet needs to be formatted like the "Structured" or "Parts Only" versions, but with the Model Data entries and columns only. I have no clue how to modify your code to accomplish this.

The issue I am trying to solve resolves around a client's request. We have developed numerous configurations of his product in one assembly as asked for. He needs to see an Excel BOM (A) that includes all of the configurations (22 and rising). He also wants a BOM (B) that shows only the sub-assemblies, and finally, he wants a BOM (C) for parts only. I can generate A & C or B & C, but not A, B & C from the assembly. My workaround so far is:

• For BOM A, set the BOM Structure on the Bill Of Materials to "Normal" and export the "Structured" tab
• For BOM B, set the BOM Structure on the Bill Of Materials to "Phantom" and export the "Structured" tab
• For BOM C, the BOM Structure setting is irrelevant. Export the "Parts Only" tab

One other possible option I see is to have line(s) in the code that will set the BOM Structure as needed for the export, and then reset it.

Thank you again.