Accessing structured BOM type though VBA for excel

[Alex.booth]

Sorry if this is the wrong section.

We are looking into using a VBA script in excel to populate a standard parts list .xlsm template from the ipropperties of an assembly using most of the code found here:-

http://beinginventive.typepad.com/being-inventive/2011/11/export-parts-list-to-excel-in-vba.html

We've added to the code to inclued filling in the title box of the parts list template and are happy with the result so far.

Though it has been requested that we are to maintain two seperate parts list, one for Purchased items and  one for manufactured items.

The property of the BOM structure is already defined in Inventor (Purchased, Normal..etc) and saved with the part, but there doesn't seem to be a "design Tracking Property" that defines it?

Here's the VBA macro (to be added into excel)

Ideally I would have two different macros. one that runs on the manufactured parts list sheet, the other on the purchased items sheet.

(the layout of both parts lists are very similar)

Public Sub BOM_Export_All_Levels()
    Dim oApp As Inventor.Application
    Set oApp = GetObject(, "Inventor.Application")
   
    Dim odoc As Inventor.AssemblyDocument
    Dim oBOM As Inventor.BOM
   'Create a FileDialog object as a File Picker dialog box.
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
    'Use a With...End With block to reference the FileDialog object.
    With fd
   
    .AllowMultiSelect = False
    .Filters.Add "Inventor Assembly", "*.iam"
    .FilterIndex = 2
    .Title = "Select Inventor Assembly"
   
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the button.
       
        If .Show = -1 Then
            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
                Set odoc = oApp.Documents.Open(vrtSelectedItem, False)
               
                Set oBOM = odoc.ComponentDefinition.BOM
               
                ' Set whether first level only or all levels.
                Firstlevelonly = False
                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")
                Call QueryBOMRowProperties(oBOMView.BOMRows)
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
   
    'fill in Tiltle Block from assy iproperties
        'Part Number (Sub-assy drawing No)
        Dim invPartNumberProperty As Property
        Set invPartNumberProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Part Number")
        Cells(4, 4).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
       
        'Order Number(Sub-assy Project No)
        Dim invProjectProperty As Property
        Set invProjectProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Project")
        Cells(2, 4).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Project").Value
       
        'Sub-assy description
        Dim invdescriptionProperty As Property
        Set invdescriptionProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Description")
        Cells(4, 2).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Description").Value
       
        'Project description - custom property
        Dim invcustomProperty1 As Property
        On Error Resume Next
        Set invcustomProperty1 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("Project Description")
        Cells(2, 2).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("Project Description").Value
       
        'PLCode - custom property2
        Dim invcustomProperty2 As Property
         On Error Resume Next
        Set invcustomProperty2 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLCode")
        Cells(3, 1).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLCode").Value
       
        'PLNumber - custom property3
        Dim invcustomProperty3 As Property
         On Error Resume Next
        Set invcustomProperty3 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLNumber")
        Cells(4, 1).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLNumber").Value
       
    Set bomrow = Nothing
    Set oBOM = Nothing
    Set odoc = Nothing
    Set oApp = Nothing
        
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator)
    '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)
        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
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate

            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file Custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-3).Activate
           
        Else
            'Get the file property that contains the "Part Number"
            'The file property is obtained from the virtual component definition
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-3).Activate
           
            'Recursively iterate child rows if present.
            If Not oRow.ChildRows Is Nothing Then
                Call QueryBOMRowProperties(oRow.ChildRows)
            End If
        End If
    Next
End Sub

Any help would be much apriciated.

Alex

[MechMachineMan]

Yes, because the BOM Structure can actually be one thing for the document, and another for each of the occurrences, thus making it an iProperty would not be useful.

Your safest be is to either pull the BOM structure from the BOM, or to access the component definition of the occurrence and query that for the BOM structure. API help should provide you with ample help to figure out the rest.

Good luck!

[Alex.booth · Accepted solution]

 I figured out how to do this, so I'll post this up here for anyone who wishes to use it.

Our company uses seperate parts lists for boughtout and manufactured parts list, so this addition has added more options but useful depending on how disaplined the CAD user is at setting the property of their parts they create.

How I'm currently using the macro is to create 6 modules with the script below changing just the true / false staments to give different results.

The modules are called:-

All Level - This dumps all parts of all level assemblies including fabrications  

All Level Manufactured - As All Level but only includes Manufactured parts (or curently anything other than purchased)

All Level Purchased - As All Level but only includes Purchased Parts

Top Level - This looks at the top level assembly only and list all parts of it (Manufactured and Purchased)

Top Level Manufacture - As Top Level but only includes Manufactured parts (or curently anything other than purchased) (Example as below)

Top Level Purchased - As Top Level but only includes Purchased Parts.

The modules are accessed though a userform and then run.

Public Sub BOM_Export_Top_Level_Man()
    Dim oApp As Inventor.Application
    On Error GoTo Errorhandler1
    Set oApp = GetObject(, "Inventor.Application")
    On Error GoTo 0
    On Error GoTo Errorhandler2
    ChDir ("C:\$VaultWorkingFolder\Designs")
    On Error GoTo 0
    Dim odoc As Inventor.AssemblyDocument
    Dim oBOM As Inventor.BOM
   'Create a FileDialog object as a File Picker dialog box.
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
    'Use a With...End With block to reference the FileDialog object.
    With fd
   
    .AllowMultiSelect = False
    .Filters.Add "Inventor Assembly", "*.iam"
    .FilterIndex = 2
    .Title = "Select Inventor Assembly"
   
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the button.
       
        If .Show = -1 Then
            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
                'MsgBox "The path is: " & vrtSelectedItem
                Set odoc = oApp.Documents.Open(vrtSelectedItem, False)
               
                Set oBOM = odoc.ComponentDefinition.BOM
               
                ' Set whether first level only or all levels.
                Firstlevelonly = True
                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")
                Call QueryBOMRowProperties(oBOMView.BOMRows)
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        MsgBox "Macro Cancelled"
        Exit Sub
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
   
    'fill in Tiltle Block from assy iproperties
        'Part Number(Sub-assy drawing No)
        Dim invPartNumberProperty As Property
        Set invPartNumberProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Part Number")
        Cells(4, 4).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
       
        'Order Number(Sub-assy Project No)
        Dim invProjectProperty As Property
        Set invProjectProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Project")
        Cells(2, 4).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Project").Value
       
        'Sub-assy description
        Dim invdescriptionProperty As Property
        Set invdescriptionProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Description")
        Cells(4, 2).Value = odoc.PropertySets.Item("Design Tracking Properties").Item("Description").Value
       
        'Project description - custom property
        Dim invcustomProperty1 As Property
        On Error Resume Next
        Set invcustomProperty1 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("Project Description")
        Cells(2, 2).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("Project Description").Value
       
        'PLCode - custom property2
        Dim invcustomProperty2 As Property
        Set invcustomProperty2 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLCode")
        Cells(3, 1).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLCode").Value
       
        'PLNumber - custom property3
        Dim invcustomProperty3 As Property
        Set invcustomProperty3 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLNumber")
        Cells(4, 1).Value = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLNumber").Value
        On Error GoTo 0
    Set bomrow = Nothing
    Set oBOM = Nothing
    Set odoc = Nothing
    Set oApp = Nothing
    Exit Sub
Errorhandler1:
MsgBox "Inventor Aplication Not Open"
Exit Sub
Errorhandler2:
MsgBox "Working Folder Not Found"
       
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator)
    '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)
       
        'Debug.Print oCompDef.BOMStructure
            'kDefaultBOMStructure = 51969
            'kNormalBOMStructure = 51970
            'kPhantomBOMStructure = 51971
            'kReferenceBOMStructure = 51972
            'kPurchasedBOMStructure = 51973
            'kInseparableBOMStructure = 51974
            'kVariesBOMStructure = 51975
           
     
    Dim BOMNo As Long
    BOMNo = oCompDef.BOMStructure
    Dim BOitem As Boolean
     
    'find if part is purchased or not using the enumerator from the list above
    If BOMNo = 51973 Then
    BOitem = True
    Else
    BOitem = False
    End If
      'Debug.Print BOitem
      'Change this value to True to only include purchased items
    If BOitem = False Then
   
       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
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
                      
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file Custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-3).Activate
            On Error GoTo 0
           
        Else
            'Get the file property that contains the "Part Number"
            'The file property is obtained from the virtual component definition
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-3).Activate
            On Error GoTo 0
            'Recursively iterate child rows if present.
            If Not oRow.ChildRows Is Nothing Then
                Call QueryBOMRowProperties(oRow.ChildRows)
            End If
        End If
    End If
    Next
End Sub

[Alex.booth]

I'm now looking into adding the base units to a column on my parts list.

so far I've worked out  I need

BOMQuantity.Baseunits.

Though I trying to figure out how to add it to the above code.

[Alex.booth]

Been doing some testing on this and It turns out for somereason all level Bought out "Purchased" items doesn't do "All Levels" But the code suggests it should. It seems to only run top level.

I must be missing something...

Heres the code that should only list all "Purchased" items on all assembly levels.

The All level "manufacturing" one works fine (i.e. lists all items excep purchased)

The code in red needs work I think, any ideas?

Alex

Public Sub BOM_Export_All_Level_Bo()
    Dim oApp As Inventor.Application
    On Error GoTo Errorhandler1
    Set oApp = GetObject(, "Inventor.Application")
    On Error GoTo 0
    Dim odoc As Inventor.AssemblyDocument
    Dim oBOM As Inventor.BOM
   'Create a FileDialog object as a File Picker dialog box.
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant
    'Use a With...End With block to reference the FileDialog object.
    With fd
   
    .AllowMultiSelect = False
    .Filters.Add "Inventor Assembly", "*.iam"
    .FilterIndex = 2
    .InitialFileName = "C:\$VaultWorkingFolder\Designs"
    .Title = "Select Inventor Assembly"
   
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the button.
       
        If .Show = -1 Then
            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems
                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
                'MsgBox "The path is: " & vrtSelectedItem
                Set odoc = oApp.Documents.Open(vrtSelectedItem, False)
               
                Set oBOM = odoc.ComponentDefinition.BOM
               
                ' Set whether first level only or all levels.
                Firstlevelonly = False
                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")
                Call QueryBOMRowProperties(oBOMView.BOMRows)
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        MsgBox "Macro Cancelled"
        Exit Sub
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing
   
    'fill in Tiltle Block from assy iproperties
        'Part Number(Sub-assy drawing No)
        Dim invPartNumberProperty As Property
        Set invPartNumberProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Part Number")
        Cells(4, 5).Value = invPartNumberProperty.Value
       
        'Order Number(Sub-assy Project No)
        Dim invProjectProperty As Property
        Set invProjectProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Project")
        Cells(2, 5).Value = invProjectProperty.Value
       
        'Sub-assy description
        Dim invdescriptionProperty As Property
        Set invdescriptionProperty = odoc.PropertySets.Item("Design Tracking Properties").Item("Description")
        Cells(4, 2).Value = invdescriptionProperty.Value
       
        'Project description - custom property
        Dim invcustomProperty1 As Property
        On Error Resume Next
        Set invcustomProperty1 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("Project Description")
        Cells(2, 2).Value = invcustomProperty1.Value
       
        'PLCode - custom property2
        Dim invcustomProperty2 As Property
        Set invcustomProperty2 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLCode")
        Cells(3, 1).Value = invcustomProperty2.Value
       
        'PLNumber - custom property3
        Dim invcustomProperty3 As Property
        Set invcustomProperty3 = odoc.PropertySets.Item("Inventor User Defined Properties").Item("PLNumber")
        Cells(4, 1).Value = invcustomProperty3.Value
        On Error GoTo 0
             
        'Get designer name from assembly insert into footer
        Dim Designer As Property
        Set Designer = odoc.PropertySets.Item("Design Tracking Properties").Item("Designer")
        'Worksheets("ManufacturingPL").PageSetup.RightFooter = "DESIGNER: " & Designer.Value & vbLf & "ORIGINATOR: " & Environ("USERNAME")
        Worksheets("BoughtoutPL").PageSetup.RightFooter = "DESIGNER: " & Designer.Value & vbLf & "ORIGINATOR: " & Environ("USERNAME")
       
       
    Set bomrow = Nothing
    Set oBOM = Nothing
    Set odoc = Nothing
    Set oApp = Nothing
    Exit Sub
Errorhandler1:
MsgBox "Inventor Aplication Not Open"
Exit Sub
       
End Sub
Private Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator)
    '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)
       
        'Debug.Print oCompDef.BOMStructure
            'kDefaultBOMStructure = 51969
            'kNormalBOMStructure = 51970
            'kPhantomBOMStructure = 51971
            'kReferenceBOMStructure = 51972
            'kPurchasedBOMStructure = 51973
            'kInseparableBOMStructure = 51974
            'kVariesBOMStructure = 51975
           
     
    Dim BOMNo As Long
    BOMNo = oCompDef.BOMStructure
    Dim BOitem As Boolean
     
    If BOMNo = 51973 Then
    BOitem = True
    Else
    BOitem = False
                   
    End If
      Debug.Print BOitem
    If BOitem = True Then
   
       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
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
                      
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Add EA to the UOM column(Currently not linked)
            ActiveCell.Value = "EA"
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file Custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-4).Activate
            On Error GoTo 0
           
        Else
            'Get the file property that contains the "Part Number"
            'The file property is obtained from the virtual component definition
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file property that contains the "Description"
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Description").Value
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the quantity
            ActiveCell.Value = oRow.ItemQuantity
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Add EA to the UOM column(Currently not linked)
            ActiveCell.Value = "EA"
            ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate
           
            'Get the file custom property that contains the "Remarks"
            On Error Resume Next
            ActiveCell.Value = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("Remarks").Value
            ActiveCell.Offset(RowOffset:=1, ColumnOffset:=-4).Activate
            On Error GoTo 0
            'Recursively iterate child rows if present.
            If Not oRow.ChildRows Is Nothing Then
                Call QueryBOMRowProperties(oRow.ChildRows)
            End If
        End If
    End If
    Next
End Sub

[Alex.booth]

 Nevermind. Fixed it.

Dim BOMNo As Long
    BOMNo = oCompDef.BOMStructure
    Dim BOitem As Boolean
     
    If BOMNo = 51973 Then
    BOitem = True
    Else
    BOitem = False
    If Not oRow.ChildRows Is Nothing Then
                Call QueryBOMRowProperties(oRow.ChildRows)
            End If
       End If
      'Debug.Print BOitem
    If BOitem = True Then