iLogic to export BOM to Excel

[tgregory3G7FA]

My code does everything properly except it doesn't format the Unit QTY and QTY in Excel as it appears in the BOM.  What am I missing?

' iLogic Rule: Export Structured BOM to Excel
' For Autodesk Inventor 2025

Dim oDoc As Document = ThisDoc.Document
If oDoc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule must be run from an assembly document.")
Return
End If

' Setup
Dim oAsmDoc As AssemblyDocument = oDoc
Dim oBOM As BOM = oAsmDoc.ComponentDefinition.BOM
oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = False
Dim oStructuredBOMView As BOMView = oBOM.BOMViews.Item("Structured")

' Create output directory
Dim exportFolder As String = "C:\_VaultWIP\BOM_Exports"
If Not System.IO.Directory.Exists(exportFolder) Then
System.IO.Directory.CreateDirectory(exportFolder)
End If

Dim exportFileName As String = System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName) & ".xlsx"
Dim exportFullPath As String = System.IO.Path.Combine(exportFolder, exportFileName)

' Start Excel
Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
Dim oWorkbook As Object = oExcel.Workbooks.Add()
Dim oSheet As Object = oWorkbook.Sheets(1)

' Column headers
Dim headers As String() = {"Item", "Part Number", "Description", "Item QTY", "Unit QTY", "QTY"}
For i = 0 To headers.Length - 1
oSheet.Cells(1, i + 1).Value = headers(i)
Next

' Write BOM rows
Dim row As Integer = 2
For Each oRow As BOMRow In oStructuredBOMView.BOMRows
Dim oCompDef As ComponentDefinition = oRow.ComponentDefinitions(1)
Dim oPartDoc As Document = oCompDef.Document
Dim oProps = oPartDoc.PropertySets.Item("Design Tracking Properties")

Dim partNumber As String = ""
Dim description As String = ""

Try
partNumber = oProps.Item("Part Number").Value
description = oProps.Item("Description").Value
Catch
' Ignore missing iProperties
End Try

Dim itemQty As Double = oRow.ItemQuantity
Dim unitQty As Double = 1 ' Default value
Dim totalQty As Double = itemQty * unitQty

oSheet.Cells(row, 1).Value = oRow.ItemNumber
oSheet.Cells(row, 2).Value = partNumber
oSheet.Cells(row, 3).Value = description
oSheet.Cells(row, 4).Value = itemQty
oSheet.Cells(row, 5).Value = unitQty
oSheet.Cells(row, 6).Value = totalQty

row += 1
Next

' Center-align specific columns
Dim centerCols As Integer() = {1, 4, 5, 6} ' Corresponds to: Item, Item QTY, Unit QTY, QTY
For Each colIndex As Integer In centerCols
oSheet.Columns(colIndex).HorizontalAlignment = -4108 ' xlCenter
Next

' Autofit column widths
oSheet.Columns.AutoFit()

' Save and clean up
oWorkbook.SaveAs(exportFullPath)
oWorkbook.Close(False)
oExcel.Quit()

MsgBox("BOM exported successfully to:" & vbCrLf & exportFullPath)

[Andrii_Humeniuk]

Hi @tgregory3G7FA .

Unit QTY is of two types: Each and parameter. In order to find out what the current Unit QTY is, you need to use the GetBaseQuantity() method in the BOMQuantity object. Depending on the result, you need to write the corresponding data into the Excel cell, either "Each" or parameter (lines 57-64).

To get the QTY, you will just need to get the TotalQuantity property in the BOMRow object.

' iLogic Rule: Export Structured BOM to Excel
' For Autodesk Inventor 2025

Dim oDoc As Document = ThisDoc.Document
If oDoc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
	MsgBox("This rule must be run from an assembly document.")
	Return
End If

' Setup
Dim oAsmDoc As AssemblyDocument = oDoc
Dim oBOM As BOM = oAsmDoc.ComponentDefinition.BOM
oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = False
Dim oStructuredBOMView As BOMView = oBOM.BOMViews.Item("Structured")

' Create output directory
Dim exportFolder As String = "D:\BOM_Exports"
If Not System.IO.Directory.Exists(exportFolder) Then
	System.IO.Directory.CreateDirectory(exportFolder)
End If

Dim exportFileName As String = System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName) & ".xlsx"
Dim exportFullPath As String = System.IO.Path.Combine(exportFolder, exportFileName)

' Start Excel
Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
Dim oWorkbook As Object = oExcel.Workbooks.Add()
Dim oSheet As Object = oWorkbook.Sheets(1)

' Column headers
Dim headers As String() = {"Item", "Part Number", "Description", "Item QTY", "Unit QTY", "QTY"}
For i = 0 To headers.Length - 1
	oSheet.Cells(1, i + 1).Value = headers(i)
Next

' Write BOM rows
Dim row As Integer = 2
For Each oRow As BOMRow In oStructuredBOMView.BOMRows
	
	Dim oCompDef As ComponentDefinition = oRow.ComponentDefinitions(1)
	Dim oPartDoc As Document = oCompDef.Document
	Dim oProps = oPartDoc.PropertySets.Item("Design Tracking Properties")
	
	Dim partNumber As String = ""
	Dim description As String = ""
	
	Try
		partNumber = oProps.Item("Part Number").Value
		description = oProps.Item("Description").Value
	Catch
		' Ignore missing iProperties
	End Try
	
	Dim itemQty As Double = oRow.ItemQuantity
	Dim eBOMQty As BOMQuantityTypeEnum
	Dim unitQty As Double
	Call oCompDef.BOMQuantity.GetBaseQuantity(eBOMQty)
	If eBOMQty = BOMQuantityTypeEnum.kEachBOMQuantity Then
		unitQty = "Each"
	Else
		unitQty = oCompDef.BOMQuantity.UnitQuantity
	End If
	Dim totalQty As Double = oRow.TotalQuantity
	
	oSheet.Cells(row, 1).Value = oRow.ItemNumber
	oSheet.Cells(row, 2).Value = partNumber
	oSheet.Cells(row, 3).Value = description
	oSheet.Cells(row, 4).Value = itemQty
	oSheet.Cells(row, 5).Value = unitQty
	oSheet.Cells(row, 6).Value = totalQty
	
	row += 1
Next

' Center-align specific columns
Dim centerCols As Integer() = {1, 4, 5, 6} ' Corresponds to: Item, Item QTY, Unit QTY, QTY
For Each colIndex As Integer In centerCols
	oSheet.Columns(colIndex).HorizontalAlignment = -4108 ' xlCenter
Next

' Autofit column widths
oSheet.Columns.AutoFit()

' Save and clean up
oWorkbook.SaveAs(exportFullPath)
oWorkbook.Close(False)
oExcel.Quit()

MsgBox("BOM exported successfully to:" & vbCrLf & exportFullPath)

[tgregory3G7FA]

Thanks for your help.

It's throwing an error 

Error on line 63 in rule: Rule0, in document

Conversion from string "60.000 in" to type 'Double' is not valid.

[tgregory3G7FA]

I got it working

' iLogic Rule: Export Structured BOM to Excel with formatted Unit QTY showing 3 decimal places
' For Autodesk Inventor 2025

Dim oDoc As Document = ThisDoc.Document
If oDoc.DocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
MsgBox("This rule must be run from an assembly document.")
Return
End If

' Setup
Dim oAsmDoc As AssemblyDocument = oDoc
Dim oBOM As BOM = oAsmDoc.ComponentDefinition.BOM
oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = False
Dim oStructuredBOMView As BOMView = oBOM.BOMViews.Item("Structured")

' Create output directory
Dim exportFolder As String = "D:\BOM_Exports"
If Not System.IO.Directory.Exists(exportFolder) Then
System.IO.Directory.CreateDirectory(exportFolder)
End If

Dim exportFileName As String = System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName) & ".xlsx"
Dim exportFullPath As String = System.IO.Path.Combine(exportFolder, exportFileName)

' Start Excel
Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
Dim oWorkbook As Object = oExcel.Workbooks.Add()
Dim oSheet As Object = oWorkbook.Sheets(1)

' Column headers
Dim headers As String() = {"Item", "Part Number", "Description", "Item QTY", "Unit QTY", "QTY"}
For i = 0 To headers.Length - 1
oSheet.Cells(1, i + 1).Value = headers(i)
Next

' Write BOM rows
Dim row As Integer = 2
For Each oRow As BOMRow In oStructuredBOMView.BOMRows
Dim oCompDef As ComponentDefinition = oRow.ComponentDefinitions(1)
Dim oPartDoc As Document = oCompDef.Document
Dim oProps = oPartDoc.PropertySets.Item("Design Tracking Properties")

Dim partNumber As String = ""
Dim description As String = ""

Try
partNumber = oProps.Item("Part Number").Value
description = oProps.Item("Description").Value
Catch
' Ignore missing iProperties
End Try

' Item Qty from BOM row
Dim itemQty As String = oRow.ItemQuantity.ToString()

' Get Unit Quantity (Unit of Measure, handle as string)
Dim unitQty As String = ""
Dim unitQtyDouble As Double = 0

Try
' Access UnitQuantity and strip units like " in"
Dim rawUnitQty As String = oCompDef.BOMQuantity.UnitQuantity.ToString()

' Remove non-numeric characters (e.g., " in") [**CHANGED**]
Dim cleanedUnitQty As String = New String(rawUnitQty.Where(Function(c) Char.IsDigit(c) Or c = "."c).ToArray())

' Convert cleaned string to Double [**CHANGED**]
If Double.TryParse(cleanedUnitQty, unitQtyDouble) Then
' Format to 3 decimal places and append " in" [**CHANGED**]
unitQty = unitQtyDouble.ToString("F3") & " in" ' "F3" ensures 3 decimals
Else
unitQty = "Error in conversion"
End If

Catch ex As Exception
unitQty = "Error accessing UnitQuantity"
MsgBox("Error: " & ex.Message) ' Debugging error handling
End Try

' Total Quantity from BOM row
Dim totalQty As String = oRow.TotalQuantity.ToString()

' Write values to Excel
oSheet.Cells(row, 1).Value = oRow.ItemNumber
oSheet.Cells(row, 2).Value = partNumber
oSheet.Cells(row, 3).Value = description
oSheet.Cells(row, 4).Value = itemQty
oSheet.Cells(row, 5).Value = unitQty
oSheet.Cells(row, 6).Value = totalQty

row += 1
Next

' Center-align specific columns
Dim centerCols As Integer() = {1, 4, 5, 6} ' Corresponds to: Item, Item QTY, Unit QTY, QTY
For Each colIndex As Integer In centerCols
oSheet.Columns(colIndex).HorizontalAlignment = -4108 ' xlCenter
Next

' Autofit column widths
oSheet.Columns.AutoFit()

' Save and clean up
oWorkbook.SaveAs(exportFullPath)
oWorkbook.Close(False)
oExcel.Quit()

MsgBox("BOM exported successfully to:" & vbCrLf & exportFullPath)

[Andrii_Humeniuk]

Sorry, I made a mistake when adapting the code to your style. The unitQty variable should be of type String. I see you have already fixed it.

Dim unitQty As String