BOM API - Export application

Jef_E · ‎09-24-2015

Hello there,

I'm trying to create a BOM export method that is based on the API sample.

 ' 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

' 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

I made a simple modification in the "debug.print" area way at the end of the code. I added some code to write it into excel cells.

oWorkSheet.Range("B1").Value = oRow.ItemNumber

This works for these items. But.. at second I attempted to export the description like this:

Dim oDescripProperty As [Property]

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

oWorkSheet.Range("C" & y + i).Value = oDescripProperty

This result is NOT good, giving me this error when I run the code:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Exception from HRESULT: 0x800A03EC

What I think is wrong is: the [Property] type cannot be exported into Excel. Seems fair. Then I tried to get it as string but here i'm at a loss. This is waht i tried:

oWorkSheet.Range("C" & y + i).Value = oDescripProperty.ToString

Result in Excel:

System.__ComObject

So basicly my question is:

How can i convert my [Property] variable into a string?

Thank you for reading.

I hope that I can resolve this issue quickly 🙂

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

Jef_E · ‎09-24-2015

And the solution if found.. easy but didn't see it at first.

oWorkSheet.Range("C" & y + i).Value = oDescripProperty.Value

New question then.

In the API sample they use:

oPartNumProperty = oCompDef.Document.PropertySets _
                   .Item("Design Tracking Properties").Item("Part Number")

But in the API there is also mentioning for:

kPartNumberDesignTrackingProperties

How is this used? Can't seem to find a reference in the API

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

BrandonBG · ‎09-24-2015

Anything that starts with a 'k' is an enumerator, or an identifier. It doesn't hold the iProperty value.

So, from the code,

oPartNumProperty = oCompDef.Document.PropertySets _
                   .Item("Design Tracking Properties").Item("Part Number")

You could write it as

oPartNumProperty = oCompDef.Document.PropertySets _
                   .Item("Design Tracking Properties").ItemByPropID(kPartNumberDesignTrackingProperties)

As far as I know, these should function the same way.

BrandonBG

Jef_E · ‎09-25-2015

Thanks for the reply,

Well I think the line you provided fits better in my philosophy of coding, I try to use as less "between the quotes" as possible 🙂 So thank you for clarifying that out for me.

Which leads me to a next question, if you should want to open a file via the BOM how would I best go about this?

I'm currently using this line, this just gets the components full file name and then open it using the value.

Are there other options to do this? (Not that there is anything wrong with this I guess.)

oInvApp.Documents.Open(oCompDef.Document.FullDocumentName)

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

BrandonBG · ‎09-25-2015

To open the file from the BOM, I think you have to identify the row, then open the referenced file:

oInvApp.Documents.Open(BOM.BOMView.BOMRow.ComponentDefinition.Document)

Now, that line won't work as written, but that's how it's structured.

Brandon

Jef_E · ‎11-02-2015

Hello,

It's been a while.. had some urgent stuff that had a higher priority than this application..

I have got a new issue... When I export my BOM the order is based on the model data, and not on the structured list.

I'm using a code based on the programming help: "Using the BOM APIs API Sample"

What am I doing wrong? Why is this list not sorted as my BOM shows?

Source code:

Spoiler

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

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

My code: (the commented text is in dutch appolagies for this.

Spoiler

    ' Button click: BOM Export
    Private Sub INV_TOOL_BOM_EXPORT_Click(sender As Object, e As EventArgs) Handles INV_TOOL_BOM_EXPORT.Click

        ' Kopieren van template .xlsm file
        Dim oFileName As String
        oFileName = INV_TOOL_BOM_FILE_NAME.Text

        ' BOM export van hoofdassembly.
        '' Controleren als het een hoofdassembly file is

        ' Excel file openen
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oWS As Excel.Worksheet

        oXL = CreateObject("Excel.Application")
        oXL.Visible = True
        oWB = oXL.Workbooks.Open("C:\Users\jeee\Desktop\BOM Configurator R2.0.xlsm")
        oWS = oWB.Sheets("Overview")

        Dim oTitleRow As Integer
        oTitleRow = 5

        Dim oCurrentRow As Integer
        oCurrentRow = oTitleRow + 1

        ' Cells(Rij, Kolom)
        ' oWS.Cells(6, 2) = "Test"

        Call BOM_Query(oXL, oWB, oWS, oCurrentRow)

        oWB.Save()

        MsgBox("Finished")

    End Sub

    Private Sub BOM_Query(ByVal oXL As Excel.Application, ByVal oWB As Excel.Workbook, ByVal oWS As Excel.Worksheet, ByRef oCurrentRow As Integer)

        Dim oDoc As AssemblyDocument
        oDoc = oInvApp.ActiveDocument

        ' BOM openemen als referentie
        Dim oBOM As Inventor.BOM
        oBOM = oDoc.ComponentDefinition.BOM

        ' BOM levels instellen op alle niveaus
        oBOM.StructuredViewFirstLevelOnly = False

        ' BOM Structured view activeren ( moest dit niet het geval zijn, maar dit zou normaal niet kunnen)
        oBOM.StructuredViewEnabled = True

        ' BOM gestructureerde lijst openemen als referentie
        Dim oBOMView As Inventor.BOMView
        oBOMView = oBOM.BOMViews.Item("Structured")

        Call BOMRow_Query_Properties(oBOMView.BOMRows, oXL, oWB, oWS, oCurrentRow)
    End Sub

    Private Sub BOMRow_Query_Properties(oBOMRows As Inventor.BOMRowsEnumerator, ByVal oXL As Excel.Application, ByVal oWB As Excel.Workbook, ByVal oWS As Excel.Worksheet, ByRef oCurrentRow As Integer)

        ' Loop door de BOM inhoud
        Dim i As Long

        ' Export instellingen
        Dim oFile As String
        oFile = "C:\Users\jeee\Desktop\BOM Configurator R2.0.xlsm"

        For i = 1 To oBOMRows.Count

            ' Opnemen van de huidige rij als referentie
            Dim oRow As Inventor.BOMRow
            oRow = oBOMRows.Item(i)

            ' Opnemen van het component dat overeenkomt met het item van rij (i)
            Dim oCompDef As Inventor.ComponentDefinition
            oCompDef = oRow.ComponentDefinitions.Item(1)

            ' Properties verklaren
            Dim oDescriptionProp As Inventor.Property
            Dim oQTY As Integer
            Dim oItemNumber As String

            ' Controle Virtueel component
            If TypeOf oCompDef Is VirtualComponentDefinition Then

                ' Item nummer ophalen van virtueel component
                oItemNumber = oRow.ItemNumber

                ' Description ophalen van virtueel component
                oDescriptionProp = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description")

                ' Aantallen ophalen van virtueel component
                oQTY = oRow.ItemQuantity

                ' Wegschrijven naar Excel Sheet
                oWS.Cells(oCurrentRow, 2) = oItemNumber
                oWS.Cells(oCurrentRow, 3) = oDescriptionProp.Value
                oWS.Cells(oCurrentRow, 4) = oQTY

            Else

                ' Item nummer ophalen van virtueel component
                oItemNumber = oRow.ItemNumber

                ' Description ophalen van virtueel component
                oDescriptionProp = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description")

                ' Aantallen ophalen van virtueel component
                oQTY = oRow.ItemQuantity

                ' Wegschrijven naar Excel Sheet
                oWS.Cells(oCurrentRow, 2) = oItemNumber
                oWS.Cells(oCurrentRow, 3) = oDescriptionProp.Value
                oWS.Cells(oCurrentRow, 4) = oQTY

            End If

            oCurrentRow = oCurrentRow + 1

            If Not oRow.ChildRows Is Nothing Then
                Call BOMRow_Query_Properties(oRow.ChildRows, oXL, oWB, oWS, oCurrentRow)
            End If

        Next

    End Sub

' Button click: BOM Export Private Sub INV_TOOL_BOM_EXPORT_Click(sender As Object, e As EventArgs) Handles INV_TOOL_BOM_EXPORT.Click ' Kopieren van template .xlsm file Dim oFileName As String oFileName = INV_TOOL_BOM_FILE_NAME.Text ' BOM export van hoofdassembly. '' Controleren als het een hoofdassembly file is ' Excel file openen Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oWS As Excel.Worksheet oXL = CreateObject("Excel.Application") oXL.Visible = True oWB = oXL.Workbooks.Open("C:\Users\jeee\Desktop\BOM Configurator R2.0.xlsm") oWS = oWB.Sheets("Overview") Dim oTitleRow As Integer oTitleRow = 5 Dim oCurrentRow As Integer oCurrentRow = oTitleRow + 1 ' Cells(Rij, Kolom) ' oWS.Cells(6, 2) = "Test" Call BOM_Query(oXL, oWB, oWS, oCurrentRow) oWB.Save() MsgBox("Finished") End Sub Private Sub BOM_Query(ByVal oXL As Excel.Application, ByVal oWB As Excel.Workbook, ByVal oWS As Excel.Worksheet, ByRef oCurrentRow As Integer) Dim oDoc As AssemblyDocument oDoc = oInvApp.ActiveDocument ' BOM openemen als referentie Dim oBOM As Inventor.BOM oBOM = oDoc.ComponentDefinition.BOM ' BOM levels instellen op alle niveaus oBOM.StructuredViewFirstLevelOnly = False ' BOM Structured view activeren ( moest dit niet het geval zijn, maar dit zou normaal niet kunnen) oBOM.StructuredViewEnabled = True ' BOM gestructureerde lijst openemen als referentie Dim oBOMView As Inventor.BOMView oBOMView = oBOM.BOMViews.Item("Structured") Call BOMRow_Query_Properties(oBOMView.BOMRows, oXL, oWB, oWS, oCurrentRow) End Sub Private Sub BOMRow_Query_Properties(oBOMRows As Inventor.BOMRowsEnumerator, ByVal oXL As Excel.Application, ByVal oWB As Excel.Workbook, ByVal oWS As Excel.Worksheet, ByRef oCurrentRow As Integer) ' Loop door de BOM inhoud Dim i As Long ' Export instellingen Dim oFile As String oFile = "C:\Users\jeee\Desktop\BOM Configurator R2.0.xlsm" For i = 1 To oBOMRows.Count ' Opnemen van de huidige rij als referentie Dim oRow As Inventor.BOMRow oRow = oBOMRows.Item(i) ' Opnemen van het component dat overeenkomt met het item van rij (i) Dim oCompDef As Inventor.ComponentDefinition oCompDef = oRow.ComponentDefinitions.Item(1) ' Properties verklaren Dim oDescriptionProp As Inventor.Property Dim oQTY As Integer Dim oItemNumber As String ' Controle Virtueel component If TypeOf oCompDef Is VirtualComponentDefinition Then ' Item nummer ophalen van virtueel component oItemNumber = oRow.ItemNumber ' Description ophalen van virtueel component oDescriptionProp = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description") ' Aantallen ophalen van virtueel component oQTY = oRow.ItemQuantity ' Wegschrijven naar Excel Sheet oWS.Cells(oCurrentRow, 2) = oItemNumber oWS.Cells(oCurrentRow, 3) = oDescriptionProp.Value oWS.Cells(oCurrentRow, 4) = oQTY Else ' Item nummer ophalen van virtueel component oItemNumber = oRow.ItemNumber ' Description ophalen van virtueel component oDescriptionProp = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description") ' Aantallen ophalen van virtueel component oQTY = oRow.ItemQuantity ' Wegschrijven naar Excel Sheet oWS.Cells(oCurrentRow, 2) = oItemNumber oWS.Cells(oCurrentRow, 3) = oDescriptionProp.Value oWS.Cells(oCurrentRow, 4) = oQTY End If oCurrentRow = oCurrentRow + 1 If Not oRow.ChildRows Is Nothing Then Call BOMRow_Query_Properties(oRow.ChildRows, oXL, oWB, oWS, oCurrentRow) End If Next End Sub

I made a screencast to properly show my problem.

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

BrandonBG · ‎11-02-2015

Is the Excel sheet the structured BOM view in the incorrect order? Or is it the wrong BOM view?

Maybe you could resort the BOM view before exporting?

Brandon

Jef_E · ‎11-02-2015

I tried resorting before i export, same result. As i compare the exported order with the diffrent BOMviews, I can say the exported BOMview is not the "structured" but the Model Data.

But in my code is stated:

        Dim oDoc As AssemblyDocument
        oDoc = oInvApp.ActiveDocument

        ' BOM openemen als referentie
        Dim oBOM As Inventor.BOM
        oBOM = oDoc.ComponentDefinition.BOM

        ' BOM levels instellen op alle niveaus
        oBOM.StructuredViewFirstLevelOnly = False

        ' BOM Structured view activeren ( moest dit niet het geval zijn, maar dit zou normaal niet kunnen)
        oBOM.StructuredViewEnabled = True

        ' BOM gestructureerde lijst openemen als referentie
        Dim oBOMView As Inventor.BOMView
        oBOMView = oBOM.BOMViews.Item("Structured")

        Call BOMRow_Query_Properties(oBOMView.BOMRows, oXL, oWB, oWS, oCurrentRow)

So where do I go wrong?

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

BrandonBG · ‎11-03-2015

This is a guess, but the issue may be inside *Private Sub BOMRow_Query_Properties*. Can you reset the BOMView as "Structured" inside this sub? I think the code is looking at the BOMRows in the Model View by default since the Model View is BOMView 1, and Structured View is BOMView 2. Brandon

BrandonBG · ‎11-03-2015

http://forums.autodesk.com/t5/inventor-customization/bom-export-error/td-p/5883528

Brandon

Jef_E · ‎11-04-2015

@BrandonBG wrote:
This is a guess, but the issue may be inside *Private Sub BOMRow_Query_Properties*. Can you reset the BOMView as "Structured" inside this sub? I think the code is looking at the BOMRows in the Model View by default since the Model View is BOMView 1, and Structured View is BOMView 2. Brandon

I have tried this but the result is the same. The BOMView is passed with the BOMrows? Kinda bummed that this is not working for me right now.

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

BrandonBG · ‎11-04-2015

I'm still not sure you are specific enough in that sub.

Instead of:

        For i = 1 To oBOMRows.Count

            ' Opnemen van de huidige rij als referentie
            Dim oRow As Inventor.BOMRow
            oRow = oBOMRows.Item(i)

Try

        For i = 1 To oBOM.BOMViews.Item("Structured").BOMRows.Count

            ' Opnemen van de huidige rij als referentie
            Dim oRow As Inventor.BOMRow
            oRow = oBOM.BOMViews.Item("Structured").BOMRows.Item(i)

Brandon

Jef_E · ‎11-05-2015

Nope.. Tried it, same result. Thanks for the input 🙂

I also retried just using the source code.. same result (maybe I had made some mistake reworking the code)

Source code result:

Spoiler

Some input from Autodesk would be nice... or am I obligated to do a sorting in EXCEL? Seems to me that would be a LAST LAST resort. I believe it shoud export in the correct order from the structured list.

But I also done some digging. And found this.

In the source code is stated

        'Set a reference to the "Structured" BOMView
        Dim oBOMView As BOMView
        oBOMView = oBOM.BOMViews.Item("Structured")

When i follow the API using the search for "BOMView" I found this in the BOMViews Object.

Spoiler

BOMViews.Item PropertyParent Object: BOMViews
DescriptionReturns the specified BOMView object from the collection.
SyntaxBOMViews.Item( Index As Variant ) As BOMView
Parameters

Name	Description
Index	Input Variant value that specifies the BOMView to return. This can be either a numeric value indicating the index of the item in the collection or it can be a string indicating the name of a BOMView. If an out of range index or a name of a non-existent BOMView is provided, an error will occur.

BOMViews.Item PropertyParent Object: BOMViewsDescriptionReturns the specified BOMView object from the collection.SyntaxBOMViews.Item( Index As Variant ) As BOMViewParameters NameDescriptionIndexInput Variant value that specifies the BOMView to return. This can be either a numeric value indicating the index of the item in the collection or it can be a string indicating the name of a BOMView. If an out of range index or a name of a non-existent BOMView is provided, an error will occur.

Then I looked into the BOMView object.

Spoiler

BOMView.ViewType PropertyParent Object: BOMView
DescriptionProperty that returns the BOM View type. Possible return values are kModelDataBOMViewType (for the 'raw' view), kStructuredBOMViewType (for the structured view) and kPartsOnlyBOMViewType (for the parts-only view).

SyntaxBOMView.ViewType() As BOMViewTypeEnum

BOMViewTypeEnum Enumerator DescriptionBOM View types.
Methods

Name	Value	Description
kModelDataBOMViewType	62465	The 'raw' view.
kPartsOnlyBOMViewType	62467	The parts-only View.
kStructuredBOMViewType	62466	The Structured View.

BOMView.ViewType PropertyParent Object: BOMViewDescriptionProperty that returns the BOM View type. Possible return values are kModelDataBOMViewType (for the 'raw' view), kStructuredBOMViewType (for the structured view) and kPartsOnlyBOMViewType (for the parts-only view).SyntaxBOMView.ViewType() As BOMViewTypeEnumBOMViewTypeEnum Enumerator DescriptionBOM View types.Methods NameValueDescriptionkModelDataBOMViewType62465The 'raw' view.kPartsOnlyBOMViewType62467The parts-only View.kStructuredBOMViewType62466The Structured View.

But these are not incorperated into the code. So I tried it my self but clearly I don't know how 😄

Try 1: Why can't you access the BOMView from the BOM?

        'Set a reference to the "Structured" BOMView
        Dim oBOMView As BOMView
        oBOMView = oBOM.BOMView.ViewType.kStructuredBOMViewType


An unhandled exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Additional information: Public member 'BOMView' on type 'BOM' not found.

Try 2: Kinda obvious error because I found the ViewType under "BOMView" and not "BOMViews"

        'Set a reference to the "Structured" BOMView
        Dim oBOMView As BOMView
        oBOMView = oBOM.BOMViews.ViewType.kStructuredBOMViewType

An unhandled exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll

Additional information: Public member 'ViewType' on type 'BOMViews' not found.

But.. When I debug.print my bomview type it gives me the correct value 😞 WHY is this so frustrating?

kStructuredBOMViewType

62466

The Structured View.

62466
Item | Quantity | Part Number | Description | 
----------------------------------------------------------------------------------
1 | 1 | Pipe ø88,9 x 6,3 - lg. 427mm _ WN 1.4306 _ 3.1_ | Pipe ø88,9 x 6,3 - lg. 427mm
3 | 1 | Elbow 90deg DN80(ø88,9) x 3,2 - acc. DIN 2605 type 3_Steel | Elbow 90deg DN80(ø88,9) x 3,2 - acc. DIN 2605 type 3
4 | 1 | Pipe ø88,9 x 3,2 - lg. 161mm _ WN 1.4306 _ 3.1_ | Pipe ø88,9 x 3,2 - lg. 161mm
2 | 1 | Repad ø200 x 12mm (Plate 201 x 200) _ WN 1.4307 _ 3.1_ | Repad ø200 x 12mm (Plate 201 x 200)
6 | 1 | Hex Head Plug 1/4in - acc. ASME B16.11 - A2 | Hex Head Plug 1/4in - acc. ASME B16.11
5 | 1 | 61477-01_C-BW-1_Nozzle-006-Testplate | Plate ø110 x 5mm

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

Vladimir.Ananyev · ‎11-05-2015

BOMView.Export method uses the rows sequence inherited from the Model BOM view.

It does not depend on the sorting in the Structured BOM view.

You may verify it using the following test code:

Sub BOM_test()
    Dim oAsmDoc As AssemblyDocument
    Set oAsmDoc = ThisApplication.ActiveDocument
    Dim oAsmDef As AssemblyComponentDefinition
    Set oAsmDef = oAsmDoc.ComponentDefinition
   
    Dim oBOM As BOM
    Set oBOM = oAsmDef.BOM
   
    oBOM.StructuredViewEnabled = True
    oBOM.StructuredViewFirstLevelOnly = False
   
    Dim oBOMView As BOMView
    Set oBOMView = oBOM.BOMViews.Item("Structured")  'Structured
   
    Dim oBOMRow As BOMRow
    Dim oProp As Inventor.Property
    Dim oDoc As Inventor.Document
    Dim pn As String
   
    For Each oBOMRow In oBOMView.BOMRows
        Set oDoc = oBOMRow.ComponentDefinitions.Item(1).Document
        Set oProp = oDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number")
        pn = oProp.value
        Debug.Print oBOMRow.ItemNumber, oBOMRow.ItemQuantity, pn
    Next

    Call oBOMView.Export("c:\temp\AAA.xls", FileFormatEnum.kMicrosoftExcelFormat)
 
    oAsmDoc.Update
    Beep
End Sub

You may consider several workarounds.

1) you may sort rows in the resulting Excel file

2) you may export BOMRow objects to the Excel file one-by-one in any desired order.

Sometimes it is very convenient to organize references to BOM rows in the temporary sorted .NET collection (e.g., SortedList) that allows you to implement any sorting logic:

https://msdn.microsoft.com/en-us/library/system.collections.sortedlist(v=vs.110).aspx

Cheers,

Vladimir Ananyev
Developer Technical Services
Autodesk Developer Network

Jef_E · ‎11-06-2015

@Vladimir.Ananyev Like in this example?

So you could create a list like this

My list would look like this: (Left column beeing the BOMrow-ID, the right item-ID)

And then sort it so it looks like this?

So I can loop through the right column, which contains the correct export order, but export the corresponding row? Or did you mean something else?

Could you post a sample code to do this? I don't quite see it in the sample code on the MSDN site.

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

NachoShaw · ‎11-08-2015

Hey

I know its not part of the problem but why dont you insert a new row in excel for every row of the BOM you add. you wouldnt need to format the grid then as the design format of the excel sheet would follow the inserted rows down.

StartRow = 1 'or whatever row your first bom line is

Rows(StartRow + 1 & ":" & StartRow + 1).Select
Selection.Insert Shift:=xlDown

Cheers

Nacho
Automation & Design Engineer

Inventor automation Programmer (C#, VB.Net / iLogic)
Furniture, Sheet Metal, Structural, Metal fab, Tradeshow, Fabrication, CNC

Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.

Jef_E · ‎11-09-2015

I'm currently not so worried about how I export the content from the BOM to Excel. There are alot of options that could be used.

My main concern is that the exported order is not correct. After I can be 100% sure the order for my export is correct I will go to the next step, but I will keep your comment in mind.

Please kudo if this post was helpfull
Please accept as solution if your problem was solved

Inventor 2014 SP2

Community

BOM API - Export application

BOM API - Export application

BOM API - Export application