Export Assembly/.iam Structured All Level BOM to premade Excel/.xls

[Jeffrey_skinnerXLYU4]

I have a iLogic script that I am having trouble getting the .IAM Structured BOM to export to Excel with "All Levels". I have searched and I am not able to find a solution. I know it is something I am overlooking.

Could anyone take a look and give me some advice?

Code:

'Export Folder location
oPATH = ("C:\Desktop\")

'Copy Template Excel / Copied File Excel
Dim oExcelTemp As String = String.Concat ("C:\Desktop\ExportTemp.xlsx")
Dim oBOMName As String = String.Concat(oPATH + ThisDoc.FileName(False) + ".xlsx")

Try
	IO.File.Copy(oExcelTemp, oBOMName, True)
Catch ex As Exception
	MessageBox.Show(ex.Message)
End Try


Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

'Import BOM Custom Layout
oBOM.ImportBOMCustomization("C:\Desktop\Temp_BOM.xml") ' edit this line to point to the exported XML


'STRUCTURED BoM 
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oBOM.StructuredViewFirstLevelOnly = False
oStructuredBOMView = oBOM.BOMViews.Item ("Structured")

'PARTS ONLY BoM 
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")


xlApp = CreateObject("Excel.Application")

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

xlWorkbook = xlApp.Workbooks.Open(oBOMName)

xlWorksheet = xlWorkbook.Worksheets.Item("Engineering")
	
Dim row As Integer
row = 3

xlWorksheet.Range("A2").Value = "ITEM"
xlWorksheet.Range("B2").Value = "Part Number"
xlWorksheet.Range("C2").Value = "UNIT"
xlWorksheet.Range("D2").Value = "QTY"
xlWorksheet.Range("E2").Value = "DESCRIPTION"
xlWorksheet.Range("F2").Value = "REV"
xlWorksheet.Range("G2").Value = "MATERIAL"
xlWorksheet.Range("H2").Value = "LENGTH"
xlWorksheet.Range("I2").Value = "SHEET METAL LENGTH"
xlWorksheet.Range("J2").Value = "SHEET METAL WIDTH"
xlWorksheet.Range("K2").Value = "PRECENT"
xlWorksheet.Range("L2").Value = "ROUTER ID"


'Dim bRow As bomRow
bRows = oStructuredBOMView.BOMRows
'bRows = oPartsOnlyBOMView.BOMRows
For Each bRow In bRows

	Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document
	
	Dim docPropertySet As PropertySet
	Dim oCustomPropSet As PropertySet
	Dim oSumPropertySet As PropertySet
	docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
	oCustomPropSet = rDoc.PropertySets.Item("Inventor User Defined Properties")
	oSumPropertySet = rDoc.PropertySets.Item("Summary Information")
	
Try
	xlWorksheet.Range("A" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("B" & row).Value = docPropertySet.Item("Part Number").Value
	xlWorksheet.Range("C" & row).Value = "EA"
	xlWorksheet.Range("D" & row).Value = bRow.ItemQuantity
	xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("F" & row).Value = oSumPropertySet.Item("Revision Number").Value
	xlWorksheet.Range("G" & row).Value = docPropertySet.Item("Material").Value
	xlWorksheet.Range("H" & row).Value = oCustomPropSet.Item("Length").Value
	xlWorksheet.Range("I" & row).Value = oCustomPropSet.Item("SheetMetalLength").Value
	xlWorksheet.Range("J" & row).Value = oCustomPropSet.Item("SheetMetalWidth").Value
	xlWorksheet.Range("K" & row).Value = oCustomPropSet.Item("PERC").Value
	xlWorksheet.Range("L" & row).Value = oCustomPropSet.Item("ROUTER").Value
Catch ex As Exception
	'MessageBox.Show(ex.Message)
End Try

	row = row + 1

Next


xlWorkbook.Save
xlWorkbook.Close (True)
xlApp.Quit


i = MessageBox.Show("Would you like to preview the BOM?", "iLogic”, MessageBoxButtons.YesNo)
If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If 
If launchviewer = 1 Then ThisDoc.Launch(oBOMName)

[WCrihfield · Accepted solution]

Hi @Jeffrey_skinnerXLYU4.  I generally do not export all levels of an assembly's BOM, so I am not super familiar with how it might look in your Excel file after the fact, but I do know that your current code is not stepping down into the BOMRow.ChildRows, so you are most likely missing out on all that lower level data.  In order to properly iterate all possible child rows and repeat the same 'write data out' process, you will either need to copy all those lines of code down into the lower level loop of the ChildRows, or you will need to transform your overall code into multiple routines.  To transform your code into multiple routines, you would need to enclose the 'main' routine into a Sub Main...your code here...End Sub block of code...then create another custom Sub routine just for the 'writing data out' step.  Then maybe send each BOMRow to that other custom Sub/Function to write out its data to Excel, or send the whole BOMRowsEnumerator to that custom routine...whichever suits your needs better.  When you use the multi-routine approach, you avoid needing to copy the same code into multiple locations, which should help reduce the overall size of the code.  You can check if a BOMRow has any ChildRows by setting BOMRow.ChildRows as the value of a BOMRowsEnumerator Type variable, then checking if it 'Is Nothing' right afterwards.  If it has no child rows, it will return Nothing.

[Jeffrey_skinnerXLYU4]

Hello @WCrihfield,

Thank you for your advice. I knew it had to be something that I was overlooking. I am still learning some of the advance coding.

So I made the Sub Main & Public Sub sections.

Thank you again for your help.

New Script:

Sub Main ()
'Export Folder location
oPATH = ("C:\Users\jeffrey.skinner\Desktop\Excel2Traverse\")

'Copy Template Excel to new name.
Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim oBOM As BOM = oDoc.ComponentDefinition.BOM
Dim oBOMView As BOMView
Dim oExcelTemp As String = String.Concat ("C:\Users\jeffrey.skinner\Desktop\Excel2Traverse\ExportTemp.xlsx")
Dim oBOMName As String = String.Concat(oPATH + ThisDoc.FileName(False) + ".xlsx")

Try
	IO.File.Copy(oExcelTemp, oBOMName, True)
Catch ex As Exception
	MessageBox.Show(ex.Message)
End Try


'--------------------------------------------------------------------------------------

'Import Custom BOM Layout.
oBOM.ImportBOMCustomization("G:\INVENTOR\External iLogic\JCF-ExtRules\JCF_BOM.xml")

'STRUCTURED BoM Settings
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oBOM.StructuredViewFirstLevelOnly = False
oBOMView = oBOM.BOMViews.Item ("Structured")

'PARTS ONLY BoM Settings
'oBOM.PartsOnlyViewEnabled = True
'Dim oPartsOnlyBOMView As BOMView
'oBOMView = oBOM.BOMViews.Item("Parts Only")

'---------------------------------------------------------------------------------------

'Insert BOM data into copy Excel file
Call sAssmBOMChildRows(oBOMView.BOMRows, iCurrRow)


' Ask if user would like to view Exported BOM.
i = MessageBox.Show("Would you like to preview the BOM?", "J.C. Ford Export BOM”, MessageBoxButtons.YesNo)
If i = vbYes Then : launchviewer = 1 : Else : launchviewer = 0 : End If 
If launchviewer = 1 Then ThisDoc.Launch(oBOMName)

End Sub

Public Sub sAssmBOMChildRows(oBOMRows As BOMRowsEnumerator, iCurrRow As Integer)
'Run thru the BOM Row.
Static CurrentRow As Integer = iCurrRow
Dim r As Long
Dim oWriteCell As String

'Path Of copied Excel file.
oPATH = ("C:\Users\jeffrey.skinner\Desktop\Excel2Traverse\")
Dim oBOMName As String = String.Concat(oPATH + ThisDoc.FileName(False) + ".xlsx")
Dim oDoc As AssemblyDocument= ThisApplication.ActiveDocument
Dim oBOM As BOM = oDoc.ComponentDefinition.BOM
Dim oBOMView As BOMView

'-------------------------------------------------------------------------------------

' Access/Open copied Excel file.
xlApp = CreateObject("Excel.Application")
xlApp.Visible = True 
xlWorkbook = xlApp.Workbooks.Open(oBOMName)
xlWorksheet = xlWorkbook.Worksheets.Item("Engineering")

' Add Property Name on Top Row.
Dim row As Integer
row = 1

xlWorksheet.Range("A1").Value = "ITEM"
xlWorksheet.Range("B1").Value = "Part Number"
xlWorksheet.Range("C1").Value = "UNIT"
xlWorksheet.Range("D1").Value = "QTY"
xlWorksheet.Range("E1").Value = "DESCRIPTION"
xlWorksheet.Range("F1").Value = "REV"
xlWorksheet.Range("G1").Value = "MATERIAL"
xlWorksheet.Range("H1").Value = "LENGTH"
xlWorksheet.Range("I1").Value = "SHEET METAL LENGTH"
xlWorksheet.Range("J1").Value = "SHEET METAL WIDTH"
xlWorksheet.Range("K1").Value = "PRECENT"
xlWorksheet.Range("L1").Value = "ROUTER ID"

' Add BOM data to copied Excel
For r = 1 To oBOMRows.Count
	
	Dim oRow As BOMRow = oBOMRows.Item(r)
	Dim oCompDef As ComponentDefinition = oRow.ComponentDefinitions.Item(1)
	Dim oPropSets As PropertySets = oCompDef.Document.PropertySets
	
	'Property Sets
	oDesignTrackingPropertySet = oPropSets.Item("Design Tracking Properties")
	oCustomPropertySet = oPropSets.Item("Inventor User Defined Properties")
	oSummaryPropertySet = oPropSets.Item("Summary Information")

	
	'iProperties
    Try
     oWriteCell = "A" & r
	 xlWorksheet.Range(oWriteCell) = oRow.ItemNumber
	   
	 oWriteCell = "B" & r
	 xlWorksheet.Range(oWriteCell) = oDesignTrackingPropertySet.Item ("Part Number").Value
	
	 oWriteCell = "C" & r
	 xlWorksheet.Range(oWriteCell) = "EA"

	 oWriteCell = "D" & r
	 xlWorksheet.Range(oWriteCell) = oRow.ItemQuantity

	 oWriteCell = "E" & r
	 xlWorksheet.Range(oWriteCell) = oDesignTrackingPropertySet.Item ("Description").Value

	 oWriteCell = "F" & r
	 xlWorksheet.Range(oWriteCell) = oSummaryPropertySet.Item("Revision Number").Value

	 oWriteCell = "G" & r
	 xlWorksheet.Range(oWriteCell) = oDesignTrackingPropertySet.Item ("Material").Value

	 oWriteCell = "H" & r
	 xlWorksheet.Range(oWriteCell) = oCustomPropertySet.Item ("Length").Value

	 oWriteCell = "I" & r
	 xlWorksheet.Range(oWriteCell) = oCustomPropertySet.Item ("SheetMetalLength").Value

	 oWriteCell = "J" & r
	 xlWorksheet.Range(oWriteCell) = oCustomPropertySet.Item ("SheetMetalWidth").Value

	 oWriteCell = "K" & r
	 xlWorksheet.Range(oWriteCell) = oCustomPropertySet.Item ("PERC").Value
	
	 oWriteCell = "L" & r
	 xlWorksheet.Range(oWriteCell) = oCustomPropertySet.Item ("JCF-ROUTER1").Value
		
    Catch ex As Exception
	   'MessageBox.Show(ex.Message)
    End Try

	CurrentRow = CurrentRow = 1
	
	If Not oRow.ChildRows Is Nothing Then
		Call sAssmBOMChildRows(oBOMView.BOMRows, iCurrRow)
	Else
	End If
	
Next r

xlWorkbook.Save
xlWorkbook.Close (True)
xlApp.Quit

End Sub

[Jeffrey_skinnerXLYU4]

@WCrihfield Sorry but I think I jumped the gun. The Assembly I tested did not have Sub Parts. I am getting this Error, when I test a Assembly with Sub-Parts. I feel it is something to do with the Structured BOM view is not expanding all levels.

[Error]

[Current Excel Result]

[Think it is pulling this view]

[Instead of this view]