Custom Bom Export to excel template

[Anonymous]

Hello All! I have been gaining a lot of knowledge from using this forum but I need some help exporting an Inventor BOM.

My employer uses a standard excel template that all manufactured parts are entered into. (We use this for standard or custom jobs). I have attached that template here. The goal is to loop through the top level assembly and place the description in the G cells starting at G4, the Rev starting at F4, and the PN starting at E4. The top level assy will be made up of subassemblies (all I-Assemblies) whose node names at the top level are 101,102,103, etc. The program has to loop thru 101, enter the node name in the column header, enter all the qtys for that section in the j column, then 102 and put the parts in K column, etc. If a bracket is used in 101 and 102 then the program has to look thru the spreadsheet and rather than enter it on a new line, it needs to place the qty in the correct column.

My first stab was to look in the help files. I found the loop portion that looks through all BomRows one item at a time. I see the ItemQuantity variable but can't seem to access it. For now I was just trying to see if I could get a simple message box to appear. (error: object required. ---didn't I declare the object a few lines before?) See bolded below.

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 total As Long
        Set total = oRow.ItemQuantity
        MsgBox (total)
        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

Any help is appreciated. (I don't expect anyone to write the code for me, I would rather get bits and pieces and stitch it together myself.) The excel portion I have no idea what to do there. I have not written any code to interact with excel from Inventor.

ps - can anyone explain what the output of this sample program is from the help files? It doesn't seem to do anything except throw a message box....

[S_May]

Hi @Anonymous,

AUTODESK Forge Model Metadata to Excel.

greetings

S_May

[S_May]

Hi @Anonymous,

Dim doc as AssemblyDocument = ThisDoc.Document
If doc.ComponentDefinition.RepresentationsManager.ActiveLevelOfDetailRepresentation.LevelOfDetail <> LevelOfDetailEnum.kMasterLevelOfDetail  Then Return

Dim bomName As String = ThisDoc.PathAndFileName(False) + "-Bom.xls"

ThisBOM.Export("Parts Only", bomName, kMicrosoftExcelFormat)

Here is another code for the metadata after excel.

greetings

S_May