6 Replies Latest reply on Dec 16, 2014 10:09 AM by David Dewey

    Sorting BOM using API

    Trevor Wunn

      Good Morning Friends,

       

      I'm trying to create a macro that sorts my BOMs. I found almost exactly what I need from solidworks help, the problem is that solidworks doesn't like one of the actions the help gave me. I've pasted the code given to me below. The problem is in red. Thanks in advance for your help.

       

      -Trevor

       

      '---------------------------------------------------------------------------

      ' Preconditions:

      ' 1. Open a drawing with one of the following tables:

      '    * General

      '    * Hole chart

      '    * Weldment cut list

      '    * Bill of materials parts-only table that

      '      contains four columns: Item No, Part Number, Description, and Qty.

      ' 2. Click the move-table icon in the upper-left corner

      '    of the table in the view.

      '    The PropertyManager opens on the table.

      ' 3. If the table is a BOM table, ensure that

      '    PropertyManager > Item Numbers > Follow assembly order is not selected.

      '

      ' Postconditions:

      ' 1. Inspect the Immediate Window.

      ' 2. The tables are sorted as follows:

      '    * If it is a BOM table:

      '      * Primary sort is on column three (Qty).

      '      * Secondary sort is on column one (Part Number).

      '      * There is no tertiary sort.

      '      * All column sorts are ascending.

      '      * Rows are sorted into categories in the following order:

      '          1. Parts

      '          2. User-defined

      '    * If it is a general table, the sort is descending on the first column.

      '    * If it is a hole table, the sort is ascending on the Tag column.

      '    * If it is a weldment cut list table, the sort is descending on the third

      '      column.

      ' --------------------------------------------------------------------------

      Dim swApp As SldWorks.SldWorks

      Dim swModDoc As SldWorks.IModelDoc2

      Dim swTable As SldWorks.ITableAnnotation

       

       

       

      Option Explicit

       

       

      Public Sub Main()

          Set swApp = Application.SldWorks

          GetSelTable

          SortSelTable

      End Sub

       

       

      Public Sub GetSelTable()

          Set swModDoc = swApp.ActiveDoc

          Dim swSM As ISelectionMgr

          Set swSM = swModDoc.SelectionManager

          Set swTable = swSM.GetSelectedObject6(1, -1)

          swModDoc.ClearSelection2 (True)

      End Sub

       

       

      Public Sub SortSelTable()

       

       

          Dim tableType As swTableAnnotationType_e

          tableType = swTable.Type

          Dim status As Boolean

          Select Case tableType

              Case swTableAnnotationType_e.swTableAnnotation_General

                  status = SortGeneralTable()

                  End

              Case swTableAnnotationType_e.swTableAnnotation_HoleChart

                  status = SortHoleChartTable()

                  End

              Case swTableAnnotationType_e.swTableAnnotation_WeldmentCutList

                  status = SortWeldmentCutlistTable()

                  End

              Case swTableAnnotationType_e.swTableAnnotation_BillOfMaterials

                  status = SortBOMTable()

                  End

              Case Else

                  Debug.Print ("Unspecified table type selected.")

                  End

          End Select

       

       

      End Sub

       

       

      Public Function SortGeneralTable() As Boolean

          Debug.Print ("Table type selected: swTableAnnotation_General")

          Dim swSpecTable As IGeneralTableAnnotation

          swSpecTable = swTable

          Dim status As Boolean

          status = swSpecTable.Sort(0, False) 'sort descending

       

       

      End Function

       

       

      Public Function SortHoleChartTable() As Boolean

          Debug.Print ("Table type selected: swTableAnnotation_HoleChart")

          Dim swSpecTable As IHoleTableAnnotation

          Set swSpecTable = swTable

          Dim status As Boolean

          status = swSpecTable.Sort(0, True) 'sort ascending

      End Function

       

       

      Public Function SortWeldmentCutlistTable() As Boolean

          Debug.Print ("Table type selected: swTableAnnotation_WeldmentCutList")

          Dim swSpecTable As IWeldmentCutListAnnotation

          Set swSpecTable = swTable

          Dim status As Boolean

          status = swSpecTable.Sort(2, False) 'sort descending

      End Function

       

       

      Public Function SortBOMTable() As Boolean

          Debug.Print ("Table type selected: swTableAnnotation_BillOfMaterials")

          Dim swSpecTable As IBomTableAnnotation

          Set swSpecTable = swTable

          Dim swSortData As BomTableSortData <<<< It doesn't approve of this line, tells me there is a compile error: user defined type not defined.

             

       

       

          Set swSortData = swSpecTable.GetBomTableSortData

       

       

          ' Specify the sort order indexes for three columns in the table

          ' Specify the direction of sort for the three sort order indexes

          swSortData.ColumnIndex(0) = 3  ' primary sort

          swSortData.Ascending(0) = True ' sort ascending

          swSortData.ColumnIndex(1) = 1  ' secondary sort

          swSortData.Ascending(1) = True ' sort ascending

          swSortData.ColumnIndex(2) = -1 ' no tertiary sort

             

       

       

          Dim pos1 As Integer

          pos1 = swSortData.ColumnIndex(0)

          Debug.Print ("Column for primary sort is " & pos1) ' should be 3

          Dim pos2 As Integer

          pos2 = swSortData.ColumnIndex(1)

          Debug.Print ("Column for secondary sort is " & pos2) ' should be 1

          Dim pos3 As Integer

          pos3 = swSortData.ColumnIndex(2)

          Debug.Print ("Column for tertiary sort is " & pos3) ' should be -1

             

       

       

          Dim listGrpArray(2) As Integer

          Dim bWantGrp As Boolean

          bWantGrp = True

       

       

          If bWantGrp Then

              ' Sort rows into part and user-defined categories

              listGrpArray(0) = swBomTableSortItemGroup_None

              listGrpArray(1) = swBomTableSortItemGroup_Parts

              listGrpArray(2) = swBomTableSortItemGroup_Other

          End If

             

       

       

          swSortData.ItemGroups = listGrpArray

             

       

       

          ' After sorting, do not re-number the items

          swSortData.DoNotChangeItemNumber = True

       

       

          Dim status As Boolean

          status = swSpecTable.Sort(swSortData)

          Debug.Print ("BOM table sorted: " & status)

       

       

      End Function

        • Re: Sorting BOM using API
          Peter Farnham

          '---------------------------------------------------------------------------

          ' Preconditions:

          ' 1. Open a drawing with one of the following tables:

          '    * General

          '    * Hole chart

          '    * Weldment cut list

          '    * Bill of materials parts-only table that

          '      contains four columns: Item No, Part Number, Description, and Qty.

          ' 2. Click the move-table icon in the upper-left corner

          '    of the table in the view.

          '    The PropertyManager opens on the table.

          ' 3. If the table is a BOM table, ensure that

          '    PropertyManager > Item Numbers > Follow assembly order is not selected.

          '

          ' Postconditions:

          ' 1. Inspect the Immediate Window.

          ' 2. The tables are sorted as follows:

          '    * If it is a BOM table:

          '      * Primary sort is on column three (Qty).

          '      * Secondary sort is on column one (Part Number).

          '      * There is no tertiary sort.

          '      * All column sorts are ascending.

          '      * Rows are sorted into categories in the following order:

          '          1. Parts

          '          2. User-defined

          '    * If it is a general table, the sort is descending on the first column.

          '    * If it is a hole table, the sort is ascending on the Tag column.

          '    * If it is a weldment cut list table, the sort is descending on the third

          '      column.

          ' --------------------------------------------------------------------------

          Dim swApp As SldWorks.SldWorks

          Dim swModDoc As SldWorks.IModelDoc2

          Dim swTable As SldWorks.ITableAnnotation




          Option Explicit



          Public Sub Main()

              Set swApp = Application.SldWorks

              GetSelTable

              SortSelTable

          End Sub



          Public Sub GetSelTable()

              Set swModDoc = swApp.ActiveDoc

              Dim swSM As ISelectionMgr

              Set swSM = swModDoc.SelectionManager

              Set swTable = swSM.GetSelectedObject6(1, -1)

              swModDoc.ClearSelection2 (True)

          End Sub



          Public Sub SortSelTable()



              Dim tableType As swTableAnnotationType_e

              tableType = swTable.Type                                                  I get the error 91 here varible not set

              Dim status As Boolean

              Select Case tableType

                  Case swTableAnnotationType_e.swTableAnnotation_General

                      status = SortGeneralTable()

                      End

                  Case swTableAnnotationType_e.swTableAnnotation_HoleChart

                      status = SortHoleChartTable()

                      End

                  Case swTableAnnotationType_e.swTableAnnotation_WeldmentCutList

                      status = SortWeldmentCutlistTable()

                      End

                  Case swTableAnnotationType_e.swTableAnnotation_BillOfMaterials

                      status = SortBOMTable()

                      End

                  Case Else

                      Debug.Print ("Unspecified table type selected.")

                      End

              End Select



          End Sub



          Public Function SortGeneralTable() As Boolean

              Debug.Print ("Table type selected: swTableAnnotation_General")

              Dim swSpecTable As IGeneralTableAnnotation

              swSpecTable = swTable

              Dim status As Boolean

              status = swSpecTable.Sort(0, False) 'sort descending



          End Function



          Public Function SortHoleChartTable() As Boolean

              Debug.Print ("Table type selected: swTableAnnotation_HoleChart")

              Dim swSpecTable As IHoleTableAnnotation

              Set swSpecTable = swTable

              Dim status As Boolean

              status = swSpecTable.Sort(0, True) 'sort ascending

          End Function



          Public Function SortWeldmentCutlistTable() As Boolean

              Debug.Print ("Table type selected: swTableAnnotation_WeldmentCutList")

              Dim swSpecTable As IWeldmentCutListAnnotation

              Set swSpecTable = swTable

              Dim status As Boolean

              status = swSpecTable.Sort(2, False) 'sort descending

          End Function



          Public Function SortBOMTable() As Boolean

              Debug.Print ("Table type selected: swTableAnnotation_BillOfMaterials")

              Dim swSpecTable As IBomTableAnnotation

              Set swSpecTable = swTable

              Dim swSortData As BomTableSortData ' <<<< It doesn't approve of this line, tells me there is a compile error: user defined type not defined.                                                 

                 



              Set swSortData = swSpecTable.GetBomTableSortData



              ' Specify the sort order indexes for three columns in the table

              ' Specify the direction of sort for the three sort order indexes

              swSortData.ColumnIndex(0) = 3  ' primary sort

              swSortData.Ascending(0) = True ' sort ascending

              swSortData.ColumnIndex(1) = 1  ' secondary sort

              swSortData.Ascending(1) = True ' sort ascending

              swSortData.ColumnIndex(2) = -1 ' no tertiary sort

                 



              Dim pos1 As Integer

              pos1 = swSortData.ColumnIndex(0)

              Debug.Print ("Column for primary sort is " & pos1) ' should be 3

              Dim pos2 As Integer

              pos2 = swSortData.ColumnIndex(1)

              Debug.Print ("Column for secondary sort is " & pos2) ' should be 1

              Dim pos3 As Integer

              pos3 = swSortData.ColumnIndex(2)

              Debug.Print ("Column for tertiary sort is " & pos3) ' should be -1

                 



              Dim listGrpArray(2) As Integer

              Dim bWantGrp As Boolean

              bWantGrp = True



              If bWantGrp Then

                  ' Sort rows into part and user-defined categories

                  listGrpArray(0) = swBomTableSortItemGroup_None

                  listGrpArray(1) = swBomTableSortItemGroup_Parts

                  listGrpArray(2) = swBomTableSortItemGroup_Other

              End If

                 



              swSortData.ItemGroups = listGrpArray

                 



              ' After sorting, do not re-number the items

              swSortData.DoNotChangeItemNumber = True



              Dim status As Boolean

              status = swSpecTable.Sort(swSortData)

              Debug.Print ("BOM table sorted: " & status)



          End Function

          • Re: Sorting BOM using API
            Artem Taturevych

            I assume you are using SolidWorks 2011 or older. This functionality is only available in SolidWorks 2012 and newer. If your SolidWorks is 2012 check the references you may need to change them to SolidWorks 2012.

            __________________________

            Regards,

            Merry Christmas and Happy New Year!

            Artem Taturevych

            Application Engineer at Intercad

            http://intercad.com.au/

            Tel: +61 2 9454 4444

            • Re: Sorting BOM using API
              David Dewey

              I realize this is sort of an old thread... but many of my drawings only have 1 BOM table... is there a way to run this script without having to preselect the table? I would hope to just pick up the first BOM table found in the feature tree of the drawing.

                • Re: Sorting BOM using API
                  Trevor Wunn

                  Sort of, If all of your BOMs are named the same(Mine are usually named BillofMaterials1, BillofMaterials2, BillofMaterials3, etc...) you can use selectbyID to grab it. Or you can just traverse the tree looking specifically for a BOM feature instead of all table features. I actually ended up doing that because i didn't care about anything but the BOM.

                   

                  Here's the code i ended up with

                   

                  Public swApp As SldWorks.SldWorks

                  Public swModel As SldWorks.ModelDoc2

                  Public swDraw As SldWorks.DrawingDoc

                  Public swBomTable As SldWorks.BomTableAnnotation

                  Public swBomFeat As SldWorks.BomFeature

                  Public swSortData As SldWorks.BomTableSortData

                  Public swSelMgr As SldWorks.SelectionMgr

                  Public swFeat As SldWorks.Feature

                  Public sortArray(2) as String

                  Public vTables as Variant

                  Public boolStatus as Boolean

                   

                  Public Function Sort_BOM_Data()

                   

                  Set swApp = Application.SldWorks

                  Set swModel = swApp.ActiveDoc

                  Set swDraw = swModel

                  Set swSelMgr = swModel.SelectionManager

                  Set swFeat = swModel.FirstFeature

                   

                  Do While Not swFeat Is Nothing

                      If "BomFeat" = swFeat.GetTypeName Then

                          swFeat.Select (False)

                          Set swBomFeat = swFeat.GetSpecificFeature2

                          vTables = swBomFeat.GetTableAnnotations

                          Set swBomTable = vTables(0)

                      End If

                      Set swFeat = swFeat.GetNextFeature

                  Loop

                   

                   

                  Set swSortData = swBomTable.GetBomTableSortData

                   

                   

                  swSortData.ColumnIndex(0) = 1

                  swSortData.Ascending(0) = True

                  swSortData.ColumnIndex(1) = 2

                  swSortData.Ascending(1) = True

                   

                   

                  sortArray(0) = swBomTableSortItemGroup_Assemblies

                  sortArray(1) = swBomTableSortItemGroup_Parts

                  sortArray(2) = swBomTableSortItemGroup_None

                   

                   

                  swSortData.ItemGroups = sortArray

                   

                  swSortData.DoNotChangeItemNumber = False

                   

                  boolStatus = swBomTable.Sort(swSortData)

                   

                  swModel.ClearSelection2 (True)

                   

                  End Function

                  1 person found this helpful
                    • Re: Sorting BOM using API
                      David Dewey

                      Thank you Trevor. I do appreciate the help!

                       

                      I'm curious, I'm getting some odd behavior on older BOM tables. If I insert a new BOM table the sorting works correctly every time. If I'm working with an older table I get very unpredictable sorting... sometimes running the same sort scheme twice in a row will change how it is sorting... very odd. Have you seen something similar?