Extract BOM from an assembly to an EXCEL spreadsheet including derived parts.

[shastu]

Is there a way to extract the indented BOM to a new excel workbook of an assembly using VBA?  I have looked at several examples and none of them seem to work.  All I currently want to extract is the Part Number and the Comments for this project.  If the excel spreadsheet could be saved as the assembly name of the Inventor assembly that it was ran from without the user having to retype it in, then that would be fantastic.

[shastu]

This is what I have so far.  It extracts the Quantity and Part Number, but the comments are not coming in.

What did I do wrong for the comments?

Sub BOM_Export()
    Dim oApp As Application
    Set oApp = ThisApplication

    If oApp.ActiveDocument.DocumentType = kAssemblyDocumentObject Then
        Dim oAssyDoc As AssemblyDocument
        Set oAssyDoc = oApp.ActiveDocument
   
        Dim oAssyCompDef As AssemblyComponentDefinition
        Set oAssyCompDef = oAssyDoc.ComponentDefinition
   
        'Dim excel_app As Excel.Application

        ' Create the Excel application.
        Set excel_app = CreateObject("Excel.Application")

        ' Uncomment this line to make Excel visible.
        excel_app.Visible = True
   
        'Create new workbook
        Call excel_app.Workbooks.Add
   
        Dim oBomR As BOMRow
        Dim oBOMPartNo As String
        Dim oBomComments As String
   
        With excel_app
            .Range("A1").Select
            .ActiveCell.Value = "Quantity"
            .Range("B1").Select
            .ActiveCell.Value = "Part Number"
       
    Dim ad As AssemblyDocument
    Set ad = ThisApplication.ActiveDocument

    Dim acd As AssemblyComponentDefinition
    Set acd = ad.ComponentDefinition

    Dim bom As bom
    Set bom = acd.bom
   
    ' Depending on what you need
    bom.StructuredViewEnabled = True
    bom.StructuredViewFirstLevelOnly = False
    bom.PartsOnlyViewEnabled = True
 ' Structured BOM view is second if it's enabled
'    Dim bv As BOMView
'    Set bv = bom.BOMViews(2)
           
            'Iterate through parts only BOM View
            Dim i As Integer
           
            For i = 1 To oAssyCompDef.bom.BOMViews(3).BOMRows.Count
               
                'Set oBomR to current BOM Row
                Set oBomR = oAssyCompDef.bom.BOMViews(3).BOMRows(i)
               
                'Get Current Row part number from part
                oBOMPartNo = oBomR.ComponentDefinitions(1).Document.PropertySets(3).ItemByPropId(5).Value
               
                'Write values to spreadsheet
                .Range("A" & i + 1).Select
                .ActiveCell.Value = oBomR.TotalQuantity 'Quantity value
                .Range("B" & i + 1).Select
                .ActiveCell.Value = oBOMPartNo
                .Range("C" & i + 1).Select
                .ActiveCell.Value = oBomComments
           Next i
        End With
    Else
        Exit Sub
    End If

End Sub

[shastu]

I have Dim oBomComments As String,  I just can't figure out what to set oBOMComments equal to.  For example,  For the Part number how did the person know that oBOMPartNo was suppose to = "oBomR.ComponentDefinitions(1).Document.PropertySets(3).ItemByPropId(5).Value"

Do the 1,3 and 5 have specific meanings?  It gets me what I want, but I would like to understand it so I am not so reliant on this discussion group.

Thanks,

Shawn

[sajith_subramanian · Accepted solution]

Hi Shawn,

In your case you could try the following

oBomComments = oBomR.ComponentDefinitions(1).Document.PropertySets .Item("Summary formation").Item("Comments").Value

To answer your question, the 1,3 and 5 do have meanings. Its equivalent of accessing the first, third and the fifth element in a collection. That is, you can access an element in a collection either by the number or its name.

For example, in the above code, the PropertySets .Item("Summary formation") can also be accessed as PropertySets(1) but accessing an element by name generally makes the code more readable.

Regards,

Sajith
           

[shastu]

Thanks for the explanation.  When I tried copying it to my macro, I got a Run-time error '429':  ActiveX component can't create object.  I have oBomComments Dim set to String.  What else do I need to do?  I tried it both ways that you suggested.

Thanks,

Shawn

[shastu]

Looks like it was just a typo.  The word Information was just formation.  Thanks so much for your help!!!!

[shastu]

Sajith,  I really would like to be able to understand how to find this on my own.  I am trying to do the best I can to make sense of this.  I am looking in the object Browser and have my filter set to Inventor.  I was hoping I would be able to find the PropertySets that you were talking about for the comments but am unsuccessful.  Should I be looking elsewhere to find out the PropertySets for the comments property?  Thanks.