VBA BOM export with excel template

[ngocson8335]

Dear Expert,

I am using VB.Net to fill part's information to excel template. But something is wrong with my code.

BOM in inventor:

Relacing all components in sub-assembly 6

Private Sub btnExportExcel_Click(sender As Object, e As EventArgs) Handles btnExportExcel.Click

        invApp = Marshal.GetActiveObject("Inventor.Application")
        assemblyDoc = invApp.ActiveDocument

        Dim oTemplate As String = "C:\Users\ngocs\OneDrive\Documents\05-Personal Work\01_Test_Files\ExcelTemplates.xls"

        Dim oBOM As BOM = assemblyDoc.ComponentDefinition.BOM

        oBOM.StructuredViewFirstLevelOnly = False
        oBOM.StructuredViewEnabled = True


        Dim oBOMView As BOMView
        oBOMView = oBOM.BOMViews.Item("Structured")

        Dim oPartNumber As Inventor.Property = Nothing
        oPartNumber = assemblyDoc.PropertySets("Design Tracking Properties").Item("Part Number")

        Dim oPartNumProperty As String
        oPartNumProperty = oPartNumber.Value


        Dim xlApp As Object = CreateObject("Excel.Application")
        Dim xlwb As Object = xlApp.Workbooks.Open(oTemplate)
        Dim xlws As Object = xlwb.Worksheets(1)

        xlApp.Visible = True
        xlws.name = "Structured " & oPartNumProperty

        Dim oStartRow As Integer = 1
        
        Call QueryBOMRowProperties(oBOMView.BOMRows, xlApp, xlwb, xlws, oStartRow)
    End Sub

    Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, ByVal xlApp As Object, ByVal xlwb As Object, ByVal xlws As Object, oStartRow As Integer)


        For i = 1 To oBOMRows.Count

            Dim oRow As BOMRow
            oRow = oBOMRows.Item(i)

            Dim oCompDef As ComponentDefinition
            oCompDef = oRow.ComponentDefinitions.Item(1)

            Dim oPartNumProperty As Inventor.Property

            If TypeOf oCompDef Is VirtualComponentDefinition Then

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

                xlws.Cells(oStartRow, 1) = oRow.ItemNumber
                xlws.Cells(oStartRow, 2) = oPartNumProperty.Value
                xlws.Cells(oStartRow, 3) = oRow.ItemQuantity

                oStartRow = oStartRow + 1

            Else

                oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")
                xlws.Cells(oStartRow, 1) = oRow.ItemNumber
                xlws.Cells(oStartRow, 2) = oPartNumProperty.Value
                xlws.Cells(oStartRow, 3) = oRow.ItemQuantity

                oStartRow = oStartRow + 1


                If Not oRow.ChildRows Is Nothing Then
                    Call QueryBOMRowProperties(oRow.ChildRows, xlApp, xlwb, xlws, oStartRow)
                End If

            End If
        Next

    End Sub

Please help me.

Many thanks.

Ngoc Son

[JamieVJohnson2]

If statements are usually the bane of everybody's existence.  Yours is very interesting to me, you have 1 condition with 2 blocks.  The first block does not check for child rows, and the second block does.  Perhaps your condition is the wrong assumption.  Is it possible that row 6 (assembly) IS a VirtualComponentDefinition, and therefor not getting its children loaded?

 If TypeOf oCompDef Is VirtualComponentDefinition Then
                oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")
                xlws.Cells(oStartRow, 1) = oRow.ItemNumber
                xlws.Cells(oStartRow, 2) = oPartNumProperty.Value
                xlws.Cells(oStartRow, 3) = oRow.ItemQuantity
                oStartRow = oStartRow + 1
            Else
                oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")
                xlws.Cells(oStartRow, 1) = oRow.ItemNumber
                xlws.Cells(oStartRow, 2) = oPartNumProperty.Value
                xlws.Cells(oStartRow, 3) = oRow.ItemQuantity
                oStartRow = oStartRow + 1
                If Not oRow.ChildRows Is Nothing Then
                    Call QueryBOMRowProperties(oRow.ChildRows, xlApp, xlwb, xlws, oStartRow)
                End If
            End If

It would be simpler to combine the code of the 2 blocks and only run the bottom check:

               oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")
                xlws.Cells(oStartRow, 1) = oRow.ItemNumber
                xlws.Cells(oStartRow, 2) = oPartNumProperty.Value
                xlws.Cells(oStartRow, 3) = oRow.ItemQuantity
                oStartRow = oStartRow + 1

                If Not oRow.ChildRows Is Nothing Then
                    Call QueryBOMRowProperties(oRow.ChildRows, xlApp, xlwb, xlws, oStartRow)
                End If

[ngocson8335]

Hi Mr.

 Is it possible that row 6 (assembly) IS a VirtualComponentDefinition, and therefore not getting its children loaded?

=> No it's not. It gets all value but when it runs to the sub 7 it overrides on the row of sub 6. 

Please see my attached Video for more details.

Thanks 

[JamieVJohnson2 · Accepted solution]

I see your real problem.  oStartRow.  You are expecting it to act as a single number reference for every use of it.  But it is actually only working as a temp reference for each run of Sub QueryBOMRowProperties.  So should be a simple fix... define it as ByRef oStartRow instead of ByVal (for VBA ByVal is default when you don't specify, but that changes later in VB.Net to ByRef as default)  ByRef will use the same one data pointer for EVERY call, and the row will add up, not add up-return-reset-and add up again.

[ngocson8335]

Hi Sir,

Thanks for your explanation. It's working well.

Thanks, thanks thanks, Sir.

Ngoc Son