Export BOM from Assembly to Excel with options

[JoshuaNAM]

Hi,

I'm trying to export a structurend BOM-list from an assembly directly to Excel. Is this possible or must I first export the data to a drawing file?

I've been copying and pasting code and have come up with the following code. The last line doesn't seem to work. Exporting with the options is somehow not working. My iLogic knowledge is limited and any helpp would really be welcome.

Cheers Josh

SyntaxEditor Code Snippet

'check that the active document Is an assembly file
If ThisApplication.ActiveDocument.DocumentType <> kAssemblyDocumentObject Then
	MessageBox.Show("Please run this rule from the assembly file.", "iLogic")
	Exit Sub
End If

Dim oAsmDoc As AssemblyDocument
oAsmDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oAsmDoc.ComponentDefinition.BOM
'''==========================================================================================
'''You can change the output path by editing oPATH below
oAsmName = Left(oAsmDoc.DisplayName, Len(oAsmDoc.DisplayName) - 4)
oRevNum = iProperties.Value("Project", "Revision Number")
oPath = ThisDoc.Path
oFolder = oPath & "\" & oAsmName & "-Rev_" & oRevNum & "-PF"

'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


''STRUCTURED BoM ===========================================================================
'' the structured view to 'all levels'
oBOM.StructuredViewFirstLevelOnly = False
' Make sure that the structured view is enabled.
oBOM.StructuredViewEnabled = True
Dim oStructuredBOMView As BOMView
oStructuredBOMView = oBOM.BOMViews.Item("Structured")

'check For existing XLS file And delete it If found
If Dir(oFolder & "\" & oAsmName & "-Rev_" & oRevNum & ".xls") <> "" Then
Kill(oFolder & "\" & oAsmName & "-Rev_" & oRevNum & ".xls")
End If

' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file to use For formatting colors, fonts, etc
oOptions.Value("Template") = "F:\Hgn-SHUP\Technische bibliotheken\Inventor\01 SupportFiles\Inventor 2014\Templates\Template Partslist.xls"

'specify the columns to export         
oOptions.Value("ExportedColumns") = "ITEM;QTY;DESCRIPTION;PART NUMBER;MATERIAL"

'specify the start cell
oOptions.Value("StartingCell") = "A5"

'specify the XLS tab name, I used the filename of the drawing
oOptions.Value("TableName") = oAsmName & "-Rev_" & oRevNum 'without extension

'include the parts list title row
oOptions.Value("IncludeTitle") = True

'autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True


' export the Partslist to Excel with above options
oStructuredBOMView.Export(oFolder & "\" & oAsmName & "-Rev_" & oRevNum & ".xls", PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

[bradeneuropeArthur · Accepted solution]

Hi,

You copied a mixture of BOM and Partlist code....

Since they are not the same this is not going to work!

THESE ARE ALL PARTLIST OPTIONS ....... NOT TO BE USED FOR BOM...............

' create a new NameValueMap object
oOptions = ThisApplication.TransientObjects.CreateNameValueMap

'specify an existing template file to use For formatting colors, fonts, etc
oOptions.Value("Template") = "F:\Hgn-SHUP\Technische bibliotheken\Inventor\01 SupportFiles\Inventor 2014\Templates\Template Partslist.xls"

'specify the columns to export         
oOptions.Value("ExportedColumns") = "ITEM;QTY;DESCRIPTION;PART NUMBER;MATERIAL"

'specify the start cell
oOptions.Value("StartingCell") = "A5"

'specify the XLS tab name, I used the filename of the drawing
oOptions.Value("TableName") = oAsmName & "-Rev_" & oRevNum 'without extension

'include the parts list title row
oOptions.Value("IncludeTitle") = True

'autofit the column width in the xls file
oOptions.Value("AutoFitColumnWidth") = True


' export the Partslist to Excel with above options
oStructuredBOMView.Export(oFolder & "\" & oAsmName & "-Rev_" & oRevNum & ".xls", PartsLis...................

The BOM has no further options.

regards,

[JoshuaNAM]

Have you got tips on how I can learn to master this?

[bradeneuropeArthur]

Yes. But I am not at my desk. I would guide you ASAP

[bradeneuropeArthur]

What do you prefer. BOM or partlist?

[JoshuaNAM]

I'd prefer exporting BOM to excel above partlist to excel.
Both Structured as PartsOnly need to be exported and provided with their status: WIP or Released etc

Then saved as pdf...

But perhaps to ambitious for now at my level of understanding

[bradeneuropeArthur]

You could already take a look in the programming help. There you can find this too....

I will help you soon.

[bradeneuropeArthur]

Hi,

Public Sub BOMExport()
    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.
    Dim oDoc As AssemblyDocument
    Set oDoc = ThisApplication.ActiveDocument

    ' Set a reference to the BOM
    Dim oBOM As BOM
    Set oBOM = oDoc.ComponentDefinition.BOM
    
    ' Set the structured view to 'all levels'
    oBOM.StructuredViewFirstLevelOnly = False

    ' Make sure that the structured view is enabled.
    oBOM.StructuredViewEnabled = True

    ' Set a reference to the "Structured" BOMView
    Dim oStructuredBOMView As BOMView
    Set oStructuredBOMView = oBOM.BOMViews.Item("Structured")
    
    ' Export the BOM view to an Excel file
    oStructuredBOMView.Export "C:\temp\BOM-StructuredAllLevels.xls", kMicrosoftExcelFormat
  
    ' Make sure that the parts only view is enabled.
    oBOM.PartsOnlyViewEnabled = True

    ' Set a reference to the "Parts Only" BOMView
    Dim oPartsOnlyBOMView As BOMView
    Set oPartsOnlyBOMView = oBOM.BOMViews.Item("Parts Only")

    ' Export the BOM view to an Excel file
    oPartsOnlyBOMView.Export "C:\temp\BOM-PartsOnly.xls", kMicrosoftExcelFormat
End Sub

Or this one:

Public Sub BOMQuery()
    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.
    Dim oDoc As AssemblyDocument
    Set oDoc = ThisApplication.ActiveDocument

    Dim FirstLevelOnly As Boolean
    If MsgBox("First level only?", vbYesNo) = vbYes Then
        FirstLevelOnly = True
    Else
        FirstLevelOnly = False
    End If
    
    ' Set a reference to the BOM
    Dim oBOM As BOM
    Set oBOM = oDoc.ComponentDefinition.BOM
    
    ' Set whether first level only or all levels.
    If FirstLevelOnly Then
        oBOM.StructuredViewFirstLevelOnly = True
    Else
        oBOM.StructuredViewFirstLevelOnly = False
    End If
    
    ' Make sure that the structured view is enabled.
    oBOM.StructuredViewEnabled = True
    
    'Set a reference to the "Structured" BOMView
    Dim oBOMView As BOMView
    Set oBOMView = oBOM.BOMViews.Item("Structured")
        
    Debug.Print "Item"; Tab(15); "Quantity"; Tab(30); "Part Number"; Tab(70); "Description"
    Debug.Print "----------------------------------------------------------------------------------"

    'Initialize the tab for ItemNumber
    Dim ItemTab As Long
    ItemTab = -3
    Call QueryBOMRowProperties(oBOMView.BOMRows, ItemTab)
End Sub

Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ItemTab As Long)
    ItemTab = ItemTab + 3
    ' Iterate through the contents of the BOM Rows.
    Dim i As Long
    For i = 1 To oBOMRows.Count
        ' Get the current row.
        Dim oRow As BOMRow
        Set oRow = oBOMRows.Item(i)

        'Set a reference to the primary ComponentDefinition of the row
        Dim oCompDef As ComponentDefinition
        Set oCompDef = oRow.ComponentDefinitions.Item(1)

        Dim oPartNumProperty As Property
        Dim oDescripProperty As Property

        If Typeof oCompDef Is VirtualComponentDefinition Then
            'Get the file property that contains the "Part Number"
            'The file property is obtained from the virtual component definition
            Set oPartNumProperty = oCompDef.PropertySets _
                .Item("Design Tracking Properties").Item("Part Number")

            'Get the file property that contains the "Description"
            Set oDescripProperty = oCompDef.PropertySets _
                .Item("Design Tracking Properties").Item("Description")

            Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
                oPartNumProperty.Value; Tab(70); oDescripProperty.Value
        Else
            'Get the file property that contains the "Part Number"
            'The file property is obtained from the parent
            'document of the associated ComponentDefinition.
            Set oPartNumProperty = oCompDef.Document.PropertySets _
                .Item("Design Tracking Properties").Item("Part Number")

            'Get the file property that contains the "Description"
            Set oDescripProperty = oCompDef.Document.PropertySets _
                .Item("Design Tracking Properties").Item("Description")

            Debug.Print Tab(ItemTab); oRow.ItemNumber; Tab(17); oRow.ItemQuantity; Tab(30); _
                oPartNumProperty.Value; Tab(70); oDescripProperty.Value
            
            'Recursively iterate child rows if present.
            If Not oRow.ChildRows Is Nothing Then
                Call QueryBOMRowProperties(oRow.ChildRows, ItemTab)
            End If
        End If
    Next
    ItemTab = ItemTab - 3
End Sub

[MechMachineMan]

---

@info wrote:
Have you got tips on how I can learn to master this?

---

Read peoples signatures on the forums - there is usually lots of good links in there.

Read autodesk website's information.

Read the programming help.

Look up the free videos of autodesk university lectures on the topics.

Use google.

Learn VBA or vb.net in general.