Displaying Sheet Number of Part Detail in BOM - VBA Macro Code Error

[cameron.houston]

Hi All,

At the company I work for we have longer drawing packets for assemblies and field installations (5+ Pages) with many part details. For this reason we have to go through the BOM and under the "Note" Column fill out what sheet each part detail is on. For example if we have a base view on sheet 6 of a drawing we have to find that item in the BOM and custom enter "Sheet 6" (the sheet that the part is detailed on). I am looking to create a macro that will:

Go through each sheet

Get all the Base view/Drawing Views

Capture the model number (DisplayName) of each BaseView/Drawing View

Compare that to the BOM Field "MPN (our part number field)"

If it matches place "Sheet + Page # Drawing View came from" in the notes section.

This would save a lot of tedious engineering grunt work. I am just starting out on this task, and to do this I am using a series of For Each X in Y loops. I keep getting the error "Object Required", I know what it means, but I do not see why it is happening. Below I am attaching screen shots of the error box and highlighted line that is giving me the error. When I hover over the highlighted line is gives me the dialogue "DrawingViews = Empty" but I am not sure why because I have 3 Drawing views on the first sheet. My full thus far is as followed. (Note it is nowhere near complete)

Public Sub SheetNumber()

'Get the active document and store it
Dim oDrawDoc As DrawingDocument
Set oDrawDoc = ThisApplication.ActiveDocument

Dim oActSheet As Sheet
Set oActSheet = oDrawDoc.ActiveSheet

Dim oActPartList As PartsList
Set oActPartList = oActSheet.PartsLists.Item(1)

Dim oSheets As Sheets
Set oSheets = oDrawDoc.Sheets

Dim oSheet As Sheet

For Each oSheet In oSheets

Dim oDrawingViews As DrawingViews
Set oDrawingViews = oSheet.DrawingViews

Dim oDrawingView As DrawingView

For Each oDrawingView In DrawingViews

Dim oRefDoc As ReferencedFileDescriptor
Set oRefDoc = oDrawingView.ReferencedDocumentDescriptor

Dim oRefName As String
oRefName = oRefDoc.DisplayName

oRefNameLeft = Left(oRefName, Len(oRefName) - 4)

MsgBox ("Ref Name Left = " & oRefNameLeft)

Next

Next

End Sub

Additionally, if anyone has done something like this before or something similar and could provide direction or give help in creating this macro that would be greatly appreciated!

[JamieVJohnson2]

Looks like for one, you didn't define oRefNameLeft

[cameron.houston]

Hi Jaime,

That is correct I did not set oRefNameLeft as a string yet, that being said I am actually getting a debug error higher up on the line calling out the "For Each oDrawingView in DrawingViews", see the attached pngs. I will update the oRefNameLeft, but do not think it is causing my issue at the moment. Let me know your thoughts.

Thanks,

Cameron

[JamieVJohnson2]

skip setting the object oDrawingViews and do the for each loop on sheet.drawingviews directly

if that fails, do a for index loop

for i as integer = 1 to sheet.drawingviews.count

dim dv as drawing view = sheet.drawingviews.item(i)

next

[JamieVJohnson2 · Accepted solution]

Also another issue:

oRefDoc as ReferencedFileDescriptor is not of object type DocumentDescriptor

[cameron.houston]

Hey Jaime,

So from my watch I am trying to access the "Referenced File" (see screen shot attached). It seems to be of type "ReferencedFileDescriptor"

[JamieVJohnson2 · Accepted solution]

That's fine, but you code didn't/doesn't show that, take another look.

[cameron.houston]

Hey Jaime,

implemented a couple of your changes and correcting a few typos worked. The code is as follows now.

Public Sub SheetNumber()

'Get the active document and store it
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = ThisApplication.ActiveDocument

    Dim oActSheet As Sheet
    Set oActSheet = oDrawDoc.ActiveSheet

    Dim oActPartList As PartsList
    Set oActPartList = oActSheet.PartsLists.Item(1)

    Dim oSheets As Sheets
    Set oSheets = oDrawDoc.Sheets

    Dim oSheet As Sheet

        For Each oSheet In oSheets

            Dim oDrawingViews As DrawingViews
            Set oDrawingViews = oSheet.DrawingViews
           
            Dim oDrawingView As DrawingView
       
                For Each oDrawingView In oDrawingViews

                    Dim oRefDoc As DocumentDescriptor
                    Set oRefDoc = oDrawingView.ReferencedDocumentDescriptor
       
                    Dim oRefName As String
                    oRefName = oRefDoc.DisplayName
       
                    Dim oRefNameLeft As String
                    oRefNameLeft = Left(oRefName, Len(oRefName) - 4)
       
                    MsgBox ("Ref Name Left = " & oRefNameLeft)
       
        Next

    Next

End Sub

[cameron.houston]

Here is the code as follows now, it work excellent except for a couple procedural issues. The main one being, I am referencing what column to put the note in by using its item number. However, I am not sure how to get it to work if someone has an extra column or two in there BOM. Right now if there is an extra column for example my note is shifted 1 column over so it is no longer in note. Any thoughts.

Public Sub SheetNumber()

'Get the active document and store it
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = ThisApplication.ActiveDocument

'Get the current active sheet within the drawing
    Dim oActSheet As Sheet
    Set oActSheet = oDrawDoc.ActiveSheet
   
'Get the Primary Parts List on the current active sheet
    Dim oActPartList As PartsList
    Set oActPartList = oActSheet.PartsLists.Item(1)

'Store all the parts list rows
    Dim oActPartListRows As PartsListRows
    Set oActPartListRows = oActPartList.PartsListRows

'Create an object to store an individual row
    Dim oActPartListRow As PartsListRow
   
   
'Set oSheets to store all the sheets in the document
    Dim oSheets As Sheets
    Set oSheets = oDrawDoc.Sheets

'Create an object to store a specific sheet
    Dim oSheet As Sheet

    'For each specific sheet in the document
        For Each oSheet In oSheets
           
            'Store the Sheet Number
            oSheetName = oSheet.Name
            oSheetRight = Right(oSheetName, 1)
           
            'Get all the drawing views on the sheet
            Dim oDrawingViews As DrawingViews
            Set oDrawingViews = oSheet.DrawingViews
           
            'Create an object to hold individual an drawing view
            Dim oDrawingView As DrawingView
       
                'For each specific drawing view on the page
                For Each oDrawingView In oDrawingViews

                    'Get access to the properties of the drawing view
                    Dim oRefDoc As DocumentDescriptor
                    Set oRefDoc = oDrawingView.ReferencedDocumentDescriptor
                           
                    'Get the specific property "Part Number"
                    Dim oRefName As String
                    oRefName = oRefDoc.DisplayName
                           
                    'Following 3 lines are for debugging to make sure object capture is correct
                    'Dim oRefNameLeft As String
                    'oRefNameLeft = Left(oRefName, Len(oRefName) - 4)
       
                    'MsgBox ("Ref Name Left = " & oRefNameLeft)
                   
                        'For each row in the parts list
                        For Each oActPartListRow In oActPartListRows
                           
                            'Get the MPN
                            Dim oPartsListMPN As String
                             oPartsListMPN = oActPartListRow.ReferencedFiles.Item(1).DisplayName
                           
                                'If the MPN = the drawing view number
                                If oPartsListMPN = oRefName Then
                           
                                'create a note saying what sheet it is on
                                oActPartListRow.Item(10).Value = ("SH " & oSheetRight)
                           
                                    Else
                                End If
                           
                    Next
            Next
    Next

End Sub

[JamieVJohnson2]

Identify BOM column by name instead of number, and if need be (shouldn't need to because api accepts variant), cycle through all the columns to get a name/number combo (like a sortedlist(of int, string)) so that you can access them by index of number.  If the name is missing, your column is not loaded, so force it in if you want!.

PartsListColumns.Item(stringname or integerindex) - it should work if Autodesk is consistent.

[cameron.houston]

Hey Jaime,

That was my thought as well, but the problem is I am trying to reference the column item from the PartsListRows. The PartsListRows has all the columns, but they are only accessed through Item #, no name. See the picture below. Let me know if that makes sense.

[JamieVJohnson2]

Back in the day, I used a completely different approach (VB.Net syntax - works in iLogic editor, minor changes required for VBA syntax for Macros editor):

 ''' <summary>
    ''' Accesses the parts list row and gets data from each cell by column title.
    ''' </summary>
    ''' <param name="plRow">PartsListRow with data to extract</param>
    ''' <param name="relPartsListItem">ITEM number column of row</param>
    ''' <param name="relTitle">TITLE or NOMENCLATURE OR DESCRIPTION columns of row, second found overwrites first</param>
    ''' <param name="relQTYPartsList">QTY column of row, non integer values are converted to -1</param>
    ''' <param name="relPartNo">PART NUMBER or PART OR ID NO columns of row, second found overwrites first</param>
    ''' <param name="relVendor">VENDOR and CAGE Code columns of row, second found overwrites first</param>
    ''' <param name="relColor">COLOR and MATERIAL columns of row, second found overwrites first</param>
    ''' <param name="relStatus">STATUS column of row</param>
    Public Sub GetDrawingListItemData(ByRef plRow As PartsListRow,
        ByRef relPartsListItem As String,
        ByRef relTitle As String,
        ByRef relQTYPartsList As Integer,
        ByRef relPartNo As String,
        ByRef relVendor As String,
        ByRef relColor As String,
        ByRef relStatus As String)
        For i As Integer = 1 To plRow.Count
            Dim plCell As PartsListCell = plRow(i)
            Dim plColumn As PartsListColumn = plCell.Parent.PartsListColumns(i)
            'this value is overwritten with a static value and needs to be written into the bomItem
            Select Case plColumn.PropertyType
                Case Is = PropertyTypeEnum.kItemPartsListProperty
                    If plColumn.Title = "ITEM" Then relPartsListItem = plCell.Value
                Case Is = PropertyTypeEnum.kFileProperty
                    Select Case plColumn.Title.ToUpper
                        Case "TITLE"
                            relTitle = plCell.Value
                        Case "PART NUMBER"
                            relPartNo = plCell.Value
                        Case "VENDOR"
                            relVendor = plCell.Value
                        Case "STATUS"
                            relStatus = plCell.Value
                        Case "COLOR"
                            relColor = plCell.Value
                        Case "FULLTITLE"
                            relTitle = plCell.Value
                        Case "PART OR ID NO"
                            relPartNo = plCell.Value
                        Case "NOMENCLATURE OR DESCRIPTION"
                            relTitle = plCell.Value
                        Case "CAGE CODE"
                            relVendor = plCell.Value
                        Case "MATERIAL"
                            relColor = plCell.Value
                    End Select
                Case Is = PropertyTypeEnum.kQuantityPartsListProperty
                    If plColumn.Title = "QTY" Then
                        If IsNumeric(plCell.Value) = True Then
                            relQTYPartsList = CInt(plCell.Value)
                        Else
                            relQTYPartsList = -1
                        End If
                    End If
                Case Is = PropertyTypeEnum.kItemQuantityPartsListProperty
                    If plColumn.Title = "QTY" Then
                        If IsNumeric(plCell.Value) = True Then
                            relQTYPartsList = CInt(plCell.Value)
                        Else
                            relQTYPartsList = -1
                        End If
                    End If
                Case Is = PropertyTypeEnum.kCustomProperty
                    Select Case plColumn.Title.ToUpper
                        Case "COLOR"
                            relColor = plCell.Value
                        Case "TITLE"
                            relTitle = plCell.Value
                        Case "FULLTITLE"
                            relTitle = plCell.Value
                        Case "PART NUMBER"
                            relPartNo = plCell.Value
                        Case "VENDOR"
                            relVendor = plCell.Value
                        Case "STATUS"
                            relStatus = plCell.Value
                        Case "PART OR ID NO"
                            relPartNo = plCell.Value
                        Case "NOMENCLATURE OR DESCRIPTION"
                            relTitle = plCell.Value
                        Case "CAGE CODE"
                            relVendor = plCell.Value
                        Case "MATERIAL"
                            relColor = plCell.Value
                    End Select
            End Select
        Next
    End Sub

knowing plColumn.Title is what you are looking for you could do this:

dim lstColumns as new list(of PartsListColumn)

lstcolumns.addrange(plrow.Items)

dim columnOfInterest as PartsListColumn = lstColumns.find(Function(x) x.Title=columnOfInterestTitle)

[cameron.houston]

Hey Jaime,

Do you know how to create a mouse click event for selecting which BOM I would want this to run for?

I think it would be something like 

Set oPickPartList As PartsList

Dim oPickPartList = ThisApplication.CommandManager.Pick (Don't know what to fill in)

Any thoughts?

[JamieVJohnson2]

The pick command allows you to filter out other entities, and has a LARGE list of filtering options.  This is so when your mouse hovers over a line and you want a circle, it ignores the line and only highlights the circle (etc...).  It even has an option kDrawingPartsListFilter (16901) which may apply to your concept (still trying to read your mind...).  The string after the comma is simply a prompt to tell the user when they go to pick something.

see SelectionFilterEnum Enumerator in the API help document for this list.

-From sample doc:

oObject = ThisApplication.CommandManager.Pick(kPartFeatureFilter, "Pick a feature")

[cameron.houston]

Hey Jaime,

Here is how I have it set up. When I run the macro it prompts me to select the parts list I want to get part detail sheet numbers from. Everything works great I just have yet to find a way to have it know what column my "Note" Column is. Like I had mentioned before, I understand how to grab the column by the title "Note" (Item("Note")) However when I am trying to set a value in a row for the note column (item(10)) you cannot reference it has Item("Note") I am not sure if there is a way I can get the item number saved from the "Note" Column in the beginning of the code so I could use it later on in the code?

Public Sub SheetNumber() 'Get the active document and store it Dim oDrawDoc As DrawingDocument Set oDrawDoc = ThisApplication.ActiveDocument 'Get the current active sheet within the drawing Dim oActSheet As Sheet Set oActSheet = oDrawDoc.ActiveSheet 'Get the Primary Parts List via user mouse click event Dim oActPartList As PartsList Set oActPartList = ThisApplication.CommandManager.Pick(kDrawingPartsListFilter, "Select the parts list to begin") 'Store all the parts list rows Dim oActPartListRows As PartsListRows Set oActPartListRows = oActPartList.PartsListRows 'Create an object to store an individual row Dim oActPartListRow As PartsListRow 'Set oSheets to store all the sheets in the document Dim oSheets As Sheets Set oSheets = oDrawDoc.Sheets 'Create an object to store a specific sheet Dim oSheet As Sheet 'For each specific sheet in the document For Each oSheet In oSheets 'Store the Sheet Number oSheetName = oSheet.Name oSheetRight = Right(oSheetName, 1) 'Get all the drawing views on the sheet Dim oDrawingViews As DrawingViews Set oDrawingViews = oSheet.DrawingViews 'Create an object to hold individual an drawing view Dim oDrawingView As DrawingView 'For each specific drawing view on the page For Each oDrawingView In oDrawingViews 'Get access to the properties of the drawing view Dim oRefDoc As DocumentDescriptor Set oRefDoc = oDrawingView.ReferencedDocumentDescriptor 'Get the specific property "Part Number" Dim oRefName As String oRefName = oRefDoc.DisplayName 'Following 3 lines are for debugging to make sure object capture is correct 'Dim oRefNameLeft As String 'oRefNameLeft = Left(oRefName, Len(oRefName) - 4) 'MsgBox ("Ref Name Left = " & oRefNameLeft) 'For each row in the parts list For Each oActPartListRow In oActPartListRows 'Get the MPN Dim oPartsListMPN As String oPartsListMPN = oActPartListRow.ReferencedFiles.Item(1).DisplayName 'If the MPN = the drawing view number If oPartsListMPN = oRefName Then 'create a note saying what sheet it is on oActPartListRow.Item(10).Value = ("SH " & oSheetRight) Else End If Next Next Next End Sub

[cameron.houston]

Better Format

Public Sub SheetNumber()
   
'Get the active document and store it
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = ThisApplication.ActiveDocument

'Get the current active sheet within the drawing
    Dim oActSheet As Sheet
    Set oActSheet = oDrawDoc.ActiveSheet
   
'Get the Primary Parts List via user mouse click event
    Dim oActPartList As PartsList
    Set oActPartList = ThisApplication.CommandManager.Pick(kDrawingPartsListFilter, "Select the parts list to begin")

'Store all the parts list rows
    Dim oActPartListRows As PartsListRows
    Set oActPartListRows = oActPartList.PartsListRows

'Create an object to store an individual row
    Dim oActPartListRow As PartsListRow
   
'Set oSheets to store all the sheets in the document
    Dim oSheets As Sheets
    Set oSheets = oDrawDoc.Sheets

'Create an object to store a specific sheet
    Dim oSheet As Sheet

    'For each specific sheet in the document
        For Each oSheet In oSheets
           
            'Store the Sheet Number
            oSheetName = oSheet.Name
            oSheetRight = Right(oSheetName, 1)
           
            'Get all the drawing views on the sheet
            Dim oDrawingViews As DrawingViews
            Set oDrawingViews = oSheet.DrawingViews
           
            'Create an object to hold individual an drawing view
            Dim oDrawingView As DrawingView
       
                'For each specific drawing view on the page
                For Each oDrawingView In oDrawingViews

                    'Get access to the properties of the drawing view
                    Dim oRefDoc As DocumentDescriptor
                    Set oRefDoc = oDrawingView.ReferencedDocumentDescriptor
                           
                    'Get the specific property "Part Number"
                    Dim oRefName As String
                    oRefName = oRefDoc.DisplayName
                           
                    'Following 3 lines are for debugging to make sure object capture is correct
                    'Dim oRefNameLeft As String
                    'oRefNameLeft = Left(oRefName, Len(oRefName) - 4)
       
                    'MsgBox ("Ref Name Left = " & oRefNameLeft)
                   
                        'For each row in the parts list
                        For Each oActPartListRow In oActPartListRows
                           
                            'Get the MPN
                            Dim oPartsListMPN As String
                             oPartsListMPN = oActPartListRow.ReferencedFiles.Item(1).DisplayName
                           
                                'If the MPN = the drawing view number
                                If oPartsListMPN = oRefName Then
                           
                                'create a note saying what sheet it is on
                                oActPartListRow.Item(10).Value = ("SH " & oSheetRight)
                           
                                    Else
                                End If
                           
                    Next
            Next
    Next

End Sub

[JamieVJohnson2]

So just find out each time before you place it (search by index):

for i as integer = 1 to plRow.Items.count'loop through all possible columns, because each row will have 1 cell per 1 column.

Dim plCell As PartsListCell = plRow(i)'get the cell of the looping index

Dim plColumn As PartsListColumn = plCell.Parent.PartsListColumns(i)'get the column with the same index

if plColumn.Title = "Notes" then 'verify column of this index is the correct column

plCell.Value = ("SH " & oSheetRight) 'use the cell of the verified index to do your thing

exit for 'stop looping, because you found what you are looking fore

end if

next

With a little cleanup, you can make that a few less lines.  If you are using this loop several times on a single list, then cache the column first when you find it, and just reuse.

[cameron.houston]

This is the latest code. It features the indexing like we had talked about. It is now in .NET format because I am deploying it in add in form.

Imports Inventor

Public Module CommandFunctions
    ' Example function that's called when the sample command is executed.
    Public Sub SampleCommandFunction()

        'Get the active document and store it
        Dim oDrawDoc As DrawingDocument
        oDrawDoc = g_inventorApplication.ActiveDocument

        'Get the current active sheet within the drawing
        Dim oActSheet As Sheet
        oActSheet = oDrawDoc.ActiveSheet

        'Get the Primary Parts List via user mouse click
        Dim oActPartList As PartsList
        oActPartList = g_inventorApplication.CommandManager.Pick(SelectionFilterEnum.kDrawingPartsListFilter, "Select the parts list to begin")

        'Locate where the note column is

        Dim oNoteCol As Integer

        For i = 1 To oActPartList.PartsListColumns.Count

            Dim oFindNote As String
            oFindNote = oActPartList.PartsListColumns.Item(i).Title

            If oFindNote = "NOTE" Then
                'This is for debugging the for loop
                'MsgBox ("This is the column of Note " & i)

                oNoteCol = i

            End If
        Next

        'Store all the parts list rows
        Dim oActPartListRows As PartsListRows
        oActPartListRows = oActPartList.PartsListRows

        'Create an object to store an individual row
        Dim oActPartListRow As PartsListRow

        'Set oSheets to store all the sheets in the document
        Dim oSheets As Sheets
        oSheets = oDrawDoc.Sheets

        'Create an object to store a specific sheet
        Dim oSheet As Sheet

        'For each specific sheet in the document
        For Each oSheet In oSheets

            'Store the Sheet Number
            Dim oSheetName As String
            oSheetName = oSheet.Name

            Dim oSheetRight As String
            oSheetRight = Right(oSheetName, 1)

            'Get all the drawing views on the sheet
            Dim oDrawingViews As DrawingViews
            oDrawingViews = oSheet.DrawingViews

            'Create an object to hold individual an drawing view
            Dim oDrawingView As DrawingView

            'For each specific drawing view on the page
            For Each oDrawingView In oDrawingViews

                'Get access to the properties of the drawing view
                Dim oRefDoc As DocumentDescriptor
                oRefDoc = oDrawingView.ReferencedDocumentDescriptor

                'Get the specific property "Part Number"
                Dim oRefName As String
                oRefName = oRefDoc.DisplayName

                'Following 3 lines are for debugging to make sure object capture is correct
                'Dim oRefNameLeft As String
                'oRefNameLeft = Left(oRefName, Len(oRefName) - 4)

                'MsgBox ("Ref Name Left = " & oRefNameLeft)

                'For each row in the parts list
                For Each oActPartListRow In oActPartListRows

                    'Get the MPN
                    Dim oPartsListMPN As String
                    oPartsListMPN = oActPartListRow.ReferencedFiles.Item(1).DisplayName

                    'If the MPN = the drawing view number
                    If oPartsListMPN = oRefName Then

                        'create a note saying what sheet it is on
                        oActPartListRow.Item(oNoteCol).Value = ("SH " & oSheetRight)

                    Else
                    End If

                Next
            Next
        Next

    End Sub

End Module