Putting Object Occurence in a table/Parts List

[RNDinov8r]

So, I have come upon this link which has the code that allows you to put an occurence description/number in a BOM Balloon call out. It works great. What I haven't been able to find is a way to take those occurences and put them in a table or parts list. Has anyone ever attemped this? I have attached a picture of what I am trying to accomplish. The table shown was created by "brute force". I even found code which allows for the creation of a custom table on the fly, but I lack the VBA knowledge to be able to tell the table to put the "Occurence Tracking" data (shown as sensor description in the table in the image) in the correct column.

Here's what I have so far for the Table Creation. 

Public Sub CreateCustomTable()
' Set a reference to the drawing document.
' This assumes a drawing document is active.
Dim oDrawDoc As DrawingDocument
Set oDrawDoc = ThisApplication.ActiveDocument

' Set a reference to the active sheet.
Dim oSheet As Sheet
Set oSheet = oDrawDoc.ActiveSheet

' Set the column titles
Dim oTitles(1 To 2) As String
oTitles(1) = "Part Number"
oTitles(2) = "Sensor Description"

' Set the contents of the custom table (contents are set row-wise)
Dim oContents(1 To 16) As String
oContents(1) = "FS-N11CP"
oContents(2) = "Bowl Part In Place"
oContents(3) = "FS-N11CP"
oContents(4) = "Bowl Part In Place"
oContents(5) = "OISP-014"
oContents(6) = "Gripper Rotary 180°"
oContents(7) = "OISP-014"
oContents(8) = "Gripper Rotary Home"
oContents(9) = "D-M9PVSAPC"
oContents(10) = "Gripper Open"
oContents(11) = "D-M9PVSAPC"
oContents(12) = "Gripper Closed"
oContents(13) = "D-M9PSAPC"
oContents(14) = "Gripper @ Home"
oContents(15) = "D-M9PSAPC"
oContents(16) = "Gripper @ Pick"

' Set the column widths (defaults to the column title width if not specified)
Dim oColumnWidths(1 To 2) As Double
oColumnWidths(1) = 5
oColumnWidths(2) = 8

' Create the custom table
Dim oCustomTable As CustomTables
Set oCustomTable = oSheet.CustomTables.Add("Input Locations & Descriptions", ThisApplication.TransientGeometry.CreatePoint2d(15, 15), _
2, 8, oTitles, oContents, oColumnWidths)
' Where "_2" is the number of columns and "8" is the number of rows

' Change the 3rd column to be left justified.
oCustomTable.Columns.Item(3).ValueHorizontalJustification = kAlignTextLeft

' Create a table format object
Dim oFormat As TableFormat
Set oFormat = oSheet.CustomTables.CreateTableFormat

' Set inside line color to red.
oFormat.InsideLineColor = ThisApplication.TransientObjects.CreateColor(255, 0, 0)

' Set outside line weight.
oFormat.OutsideLineWeight = 0.1

' Modify the table formats
oCustomTable.OverrideFormat = oFormat
End Sub

[chandra.shekar.g]

@RNDinov8r,

Try the following VBA code to below custom table.

Public Sub CreateCustomTable()
    ' Set a reference to the drawing document.
    ' This assumes a drawing document is active.
    Dim oDrawDoc As DrawingDocument
    Set oDrawDoc = ThisApplication.ActiveDocument
    
    ' Set a reference to the active sheet.
    Dim oSheet As Sheet
    Set oSheet = oDrawDoc.ActiveSheet
    
    ' Set the column titles
    Dim oTitles(1 To 3) As String
    oTitles(1) = "Part Number"
    oTitles(2) = "Sensor Description"
    oTitles(3) = "Occurance Tracking"
    
    ' Set the contents of the custom table (contents are set row-wise)
    Dim oContents(1 To 24) As String
    oContents(1) = "FS-N11CP"
    oContents(2) = "Bowl Part In Place"
    oContents(3) = "30"
    oContents(4) = "FS-N11CP"
    oContents(5) = "Bowl Part In Place"
    oContents(6) = "30"
    oContents(7) = "OISP-014"
    oContents(8) = "Gripper Rotary 180°"
    oContents(9) = "29"
    oContents(10) = "OISP-014"
    oContents(11) = "Gripper Rotary Home"
    oContents(12) = "29"
    oContents(13) = "D-M9PVSAPC"
    oContents(14) = "Gripper Open"
    oContents(15) = "28"
    oContents(16) = "D-M9PVSAPC"
    oContents(17) = "Gripper Closed"
    oContents(18) = "28"
    oContents(19) = "D-M9PSAPC"
    oContents(20) = "Gripper @ Home"
    oContents(21) = "27"
    oContents(22) = "D-M9PSAPC"
    oContents(23) = "Gripper @ Pick"
    oContents(24) = "27"
    
    ' Set the column widths (defaults to the column title width if not specified)
    Dim oColumnWidths(1 To 3) As Double
    oColumnWidths(1) = 4.5
    oColumnWidths(2) = 5.5
    oColumnWidths(3) = 5.75
    
    oDrawDoc.StylesManager.ActiveStandardStyle.ActiveObjectDefaults.TableStyle.HeadingGap = 0.1
    
    ' Create the custom table
    Dim oCustomTable As CustomTable
    Set oCustomTable = oSheet.CustomTables.Add("Input Locations & Descriptions", ThisApplication.TransientGeometry.CreatePoint2d(15, 15), _
                                        3, 8, oTitles, oContents, oColumnWidths)
    
    ' Change the 3rd column to be left justified.
    oCustomTable.Columns.Item(1).ValueHorizontalJustification = kAlignTextLeft
    oCustomTable.Columns.Item(2).ValueHorizontalJustification = kAlignTextLeft
    oCustomTable.Columns.Item(3).ValueHorizontalJustification = kAlignTextLeft

    ' Create a table format object
    Dim oFormat As TableFormat
    Set oFormat = oSheet.CustomTables.CreateTableFormat

    ' Set inside line color to red.
    oFormat.InsideLineColor = ThisApplication.TransientObjects.CreateColor(255, 0, 0)
    
    ' Set outside line weight.
    oFormat.OutsideLineWeight = 0.05
    
    ' Modify the table formats
    oCustomTable.OverrideFormat = oFormat
    
End Sub

Please feel free to contact if there is any queries.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[RNDinov8r]

Chandra,

I'm sorry, I wasn't clear. Thank you for looking into this, though. I am trying to avoid the "brute force" method by having to type in the Sensor Description and Part No, since there will always be different quantities and descriptions on different machines. I want the p/n and the "sensor description" (which is part of occurrence tracking VB in the link in my initial post) to get pulled into the parts list, rather than me have to manually go in and change this particular portion of code for every assembly (Given that some of our machines use 50-100 sensors, you can see manually tracking would be an issue) or sub assembly:

oContents(1 To 24) As String

oContents(1) = "FS-N11CP"

oContents(2) = "Bowl Part In Place"

oContents(3) = "FS-N11CP"

oContents(4) = "Bowl Part In Place"

oContents(5) = "OISP-014"

oContents(6) = "Gripper Rotary 180°"

oContents(7) = "OISP-014"

oContents(8) = "Gripper Rotary Home"

oContents(9) = "D-M9PVSAPC"

oContents(10) = "Gripper Open"

oContents(11) = "D-M9PVSAPC"

oContents(12) = "Gripper Closed"

oContents(13) = "D-M9PSAPC"

oContents(14) = "Gripper @ Home"

oContents(15) = "D-M9PSAPC"

oContents(16) = "Gripper @ Pick"

I dont' actually care about the "item number" (27, 28, 29, & 30) but I do care about "part number" and "sensor description". I just want them to be auto populated rather than manually entered (as shown in the above code lines).

I have attached another picture as well so you can see where I change the "Occurence Number" to a "Sensor Description" in the assembly. this is how I give the information to my controls department so they know how to write the code for the PLC...essentially, I am creating a tag for them to use. This infomation gets put into a parts list (what I'm trying to solve above) that ultimately gets exported to excel, and they then dump it into their PLC code. The idea is to reduce manually re-entering tags and data once it has been created.

[chandra.shekar.g · Accepted solution]

@RNDinov8r,

Based on above image, occurrence names of sensor contains part number + ':' + sensor description. With this assumption, I tried some below VBA code. Assumed that Drawing Document always contains assembly document. VBA code runs only for Drawing document.

Sub Main()

    Dim oDoc As DrawingDocument
    Set oDoc = ThisApplication.ActiveDocument
    
    Dim oAsmDoc As AssemblyDocument
    Set oAsmDoc = oDoc.ActiveSheet.DrawingViews.Item(1).ReferencedDocumentDescriptor.ReferencedDocument
    
    Dim oDef As AssemblyComponentDefinition
    Set oDef = oAsmDoc.ComponentDefinition
        
    Dim sensorCnt As Integer
    Dim oContents() As String
    Dim IsInteger As Boolean
    Dim LArray() As String
    Dim occ As ComponentOccurrence
    For Each occ In oDef.Occurrences
        
        LArray = Split(occ.Name, ":")
        
        IsInteger = ConvertToInteger(LArray(1))
        
        If IsInteger = False Then
            sensorCnt = sensorCnt + 1
        End If
    Next
    
    If sensorCnt > 0 Then
        sensorCnt = sensorCnt * 2
        ReDim oContents(sensorCnt - 1) As String
        Dim i As Integer
        i = 0
        For Each occ In oDef.Occurrences
            LArray = Split(occ.Name, ":")
            IsInteger = ConvertToInteger(LArray(1))
            If IsInteger = False Then
                oContents(i) = LArray(0)
                oContents(i + 1) = LArray(1)
                i = i + 2
            End If
        Next
    End If
    
    ' Set a reference to the active sheet.
    Dim oSheet As Sheet
    Set oSheet = oDoc.ActiveSheet
    
    ' Set the column titles
    Dim oTitles(1 To 2) As String
    oTitles(1) = "Part Number"
    oTitles(2) = "Sensor Description"
    
    
    ' Set the column widths (defaults to the column title width if not specified)
    Dim oColumnWidths(1 To 2) As Double
    oColumnWidths(1) = 4.5
    oColumnWidths(2) = 5.5
    
    oDoc.StylesManager.ActiveStandardStyle.ActiveObjectDefaults.TableStyle.HeadingGap = 0.1
    
    ' Create the custom table
    Dim oCustomTable As CustomTable
    Set oCustomTable = oSheet.CustomTables.Add("Input Locations & Descriptions", ThisApplication.TransientGeometry.CreatePoint2d(45, 15), _
                                        2, sensorCnt / 2, oTitles, oContents, oColumnWidths)
    
    ' Change the 3rd column to be left justified.
    oCustomTable.Columns.Item(1).ValueHorizontalJustification = kAlignTextLeft
    oCustomTable.Columns.Item(2).ValueHorizontalJustification = kAlignTextLeft

    ' Create a table format object
    Dim oFormat As TableFormat
    Set oFormat = oSheet.CustomTables.CreateTableFormat

    ' Set inside line color to red.
    oFormat.InsideLineColor = ThisApplication.TransientObjects.CreateColor(255, 0, 0)
    
    ' Set outside line weight.
    oFormat.OutsideLineWeight = 0.05
    
    ' Modify the table formats
    oCustomTable.OverrideFormat = oFormat
    

    
End Sub

Function ConvertToInteger(v1 As String) As Boolean
    On Error GoTo 100:
     Dim i As Integer
     i = CInt(v1)
     ConvertToInteger = True
     Exit Function
100:
     ConvertToInteger = False
End Function

Please feel free to contact if there is any queries.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[RNDinov8r]

That was exactly what I was trying to do. Really appreciate that. I have a long way to go it appears before I get to programming like that! Again, much appreciated.

[RNDinov8r]

So, I have been playing around Chandra and I discovered that this code works great for an Assembly/Drawing where I need to evaluate occurrence labels that are only within that assembly (first layer)...however, I discovered, if I run this on an assembly with sub assemblies which contain the occurrence labels, it does not find them. It is not apparent to me in the code why it would not burrow down a level or two levels or further for that matter. Would a traversing subroutine, similar to what is found here be used?

[MechMachineMan]

Try changing both instances of 

oDef.Occurrences

to

oDef.Occurrences.AllLeafOccurrences

and see if that works for you.

[RNDinov8r]

I now get this a run-time '9' error.

It is specific to:

          IsInteger = ConvertToInteger(LArray(1))

I have tried other numbers and qty does not seem to matter. 

[MechMachineMan]

My assumption would be that it's telling you some occurrence names do not have a ":" character in them, so it's throwing it off.

[chandra.shekar.g · Accepted solution]

@RNDinov8r,

It is assumed that every occurrence in assembly is containing ":" character. Try the below VBA code to avoid that error.

Sub Main()

    Dim oDoc As DrawingDocument
    Set oDoc = ThisApplication.ActiveDocument
    
    Dim oAsmDoc As AssemblyDocument
    Set oAsmDoc = oDoc.ActiveSheet.DrawingViews.Item(1).ReferencedDocumentDescriptor.ReferencedDocument
    
    Dim oDef As AssemblyComponentDefinition
    Set oDef = oAsmDoc.ComponentDefinition
        
    Dim sensorCnt As Integer
    Dim oContents() As String
    Dim IsInteger As Boolean
    Dim LArray() As String
    Dim occ As ComponentOccurrence
    For Each occ In oDef.AllLeafOccurrences
        On Error Resume Next
        LArray = Split(occ.Name, ":")
        
        IsInteger = ConvertToInteger(LArray(1))
        
        If IsInteger = False Then
            sensorCnt = sensorCnt + 1
        End If
    Next
    
    If sensorCnt > 0 Then
        sensorCnt = sensorCnt * 2
        ReDim oContents(sensorCnt - 1) As String
        Dim i As Integer
        i = 0
        For Each occ In oDef.Occurrences
            LArray = Split(occ.Name, ":")
            IsInteger = ConvertToInteger(LArray(1))
            If IsInteger = False Then
                oContents(i) = LArray(0)
                oContents(i + 1) = LArray(1)
                i = i + 2
            End If
        Next
    End If
    
    ' Set a reference to the active sheet.
    Dim oSheet As Sheet
    Set oSheet = oDoc.ActiveSheet
    
    ' Set the column titles
    Dim oTitles(1 To 2) As String
    oTitles(1) = "Part Number"
    oTitles(2) = "Sensor Description"
    
    
    ' Set the column widths (defaults to the column title width if not specified)
    Dim oColumnWidths(1 To 2) As Double
    oColumnWidths(1) = 4.5
    oColumnWidths(2) = 5.5
    
    oDoc.StylesManager.ActiveStandardStyle.ActiveObjectDefaults.TableStyle.HeadingGap = 0.1
    
    ' Create the custom table
    Dim oCustomTable As CustomTable
    Set oCustomTable = oSheet.CustomTables.Add("Input Locations & Descriptions", ThisApplication.TransientGeometry.CreatePoint2d(45, 15), _
                                        2, sensorCnt / 2, oTitles, oContents, oColumnWidths)
    
    ' Change the 3rd column to be left justified.
    oCustomTable.Columns.Item(1).ValueHorizontalJustification = kAlignTextLeft
    oCustomTable.Columns.Item(2).ValueHorizontalJustification = kAlignTextLeft

    ' Create a table format object
    Dim oFormat As TableFormat
    Set oFormat = oSheet.CustomTables.CreateTableFormat

    ' Set inside line color to red.
    oFormat.InsideLineColor = ThisApplication.TransientObjects.CreateColor(255, 0, 0)
    
    ' Set outside line weight.
    oFormat.OutsideLineWeight = 0.05
    
    ' Modify the table formats
    oCustomTable.OverrideFormat = oFormat
    

    
End Sub

Function ConvertToInteger(v1 As String) As Boolean
    On Error GoTo 100:
     Dim i As Integer
     i = CInt(v1)
     ConvertToInteger = True
     Exit Function
100:
     ConvertToInteger = False
End Function

Please feel free to contact if there is any queries.

If solves problem, click on "Accept as solution" / give a "Kudo".

Thanks and regards,

[RNDinov8r]

that did the trick. I am going to play around with this a little more on various models, but I think this does the trick. Really appreciate that.