10 Replies Latest reply on May 22, 2018 3:17 AM by Yash Kothari

    Add custom column with formula to BOM table

    Yash Kothari

      I am struggling to insert a formula in a custom defined column to BOM table. I found a program of adding a partnumber column from SolidWorks help.

      2018 api - Insert Part Number Column in BOM Table Example (VBA)

      __________________________________________________________

      Option Explicit

      Dim swApp As SldWorks.SldWorks

      Dim swModel As SldWorks.ModelDoc2

      Dim boolstatus As Boolean

      Dim SelMgr As Object

      Dim theTableAnnotation As SldWorks.TableAnnotation

      Dim SelObjType As Long

      Dim TableAnnotationType As Long

       

      Sub DisplayTableColumnProps(theTableAnnotation As Object)

       

          Dim ColCount As Long

          Dim i As Long

          Dim ColType As swTableColumnTypes_e

          Dim ColTitle As String

       

          Debug.Print "Col#"; vbTab; "Type"; vbTab; "Title"

          ColCount = theTableAnnotation.ColumnCount

       

        For i = 0 To ColCount - 1

              ColType = theTableAnnotation.GetColumnType(i)

              ColTitle = theTableAnnotation.GetColumnTitle(i)

              Debug.Print i; vbTab; ColType; vbTab; ColTitle

          Next i

       

      End Sub


      Sub main()

       

          Set swApp = Application.SldWorks

          Set swModel = swApp.ActiveDoc

          Set SelMgr = swModel.SelectionManager

       

          SelObjType = SelMgr.GetSelectedObjectType3(1, -1)

          If SelObjType <> swSelANNOTATIONTABLES Then

       

              MsgBox "Select a BOM table in the drawing before running this example."

              End

          End If

       

          Set theTableAnnotation = SelMgr.GetSelectedObject6(1, -1)

          TableAnnotationType = theTableAnnotation.Type

          If TableAnnotationType <> swTableAnnotation_BillOfMaterials Then

       

              MsgBox "Select a BOM table in the drawing before running this example."

              End

          End If

       

          Debug.Print "Table before inserting a column..."

          ' Display table before inserting a column

          DisplayTableColumnProps theTableAnnotation

       

         

          ' Insert new column

       

          boolstatus = theTableAnnotation.InsertColumn2(swTableItemInsertPosition_Last, 0, "Add Item No", swInsertColumn_DefaultWidth)

          boolstatus = theTableAnnotation.SetColumnType(theTableAnnotation.ColumnCount - 1, swBomTableColumnType_ItemNumber)

           

          Debug.Print " "

          Debug.Print "Table after inserting a column..."

          ' Display table after inserting a column

          DisplayTableColumnProps theTableAnnotation

       

      End Sub

      ____________________________________________________________________

      But now i need to insert a new column containing a formula of adding the numeric values of item number (in detailed indented BOM). I need a Macro, help please.

       

      Deepak Gupta

        • Re: Add custom column with formula to BOM table
          Deepak Gupta

          You need to get the row count and then traverse thru the cell and set the text value.

           

          For i = 1 To theTableAnnotation.RowCount- 1

          theTableAnnotation.text(i, theTableAnnotation.ColumnCount-1) = "Your desired text here"

          Next i

            • Re: Add custom column with formula to BOM table
              Yash Kothari

              Thank you Deepak Gupta

              I have some questions:

              • Do I need to define any variables before this.

              • I need a formula in the text value, the formula contains the values from the 1st column of my bom table, do i need to store those values first

              • Re: Add custom column with formula to BOM table
                Yash Kothari

                Thanks Deepak

                I achieved what i wanted. I got the levelwise assembly structure by inserting different formulas.

                 

                Sub main()
                On Error GoTo handler:
                Dim BomFeatureName As String: BomFeatureName = "Bill of Materials1"
                Dim swapp As SldWorks.SldWorks
                Dim swmodel As ModelDoc2
                Dim SWannotationtable As TableAnnotation
                
                
                Dim result1 As Boolean
                Dim result2 As Boolean
                Dim result3 As Boolean
                Dim result4 As Boolean
                Dim result5 As Boolean
                Dim result6 As Boolean
                Dim result7 As Boolean
                
                
                Dim featuremanager As featuremanager
                Dim feature As feature
                Dim vfeature As Variant
                
                
                Set swapp = Application.SldWorks
                Set swmodel = swapp.ActiveDoc
                Set featuremanager = swmodel.featuremanager
                vfeatures = featuremanager.GetFeatures(True)
                For i = 0 To UBound(vfeatures)
                Set feature = vfeatures(i)
                If feature.Name = BomFeatureName Then
                Dim bomfeature As bomfeature
                Set bomfeature = feature.GetSpecificFeature2
                Dim bomtableannotation As bomtableannotation
                Set bomtableannotation = bomfeature.GetTableAnnotations(0)
                Set SWannotationtable = bomtableannotation
                Exit For
                End If
                Next i
                result1 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "Add Item No", swInsertColumn_DefaultWidth)
                
                
                ' Dim DotLen As String
                ' Dim j As Long
                
                For j = 1 To SWannotationtable.RowCount - 1
                SWannotationtable.Text(j, SWannotationtable.ColumnCount - 1) = Len(SWannotationtable.Text(j, 0)) - Len(Replace(SWannotationtable.Text(j, 0), ".", "")) + 1
                Next j
                
                result2 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "L E V E L", swInsertColumn_DefaultWidth)
                
                
                For j = 1 To SWannotationtable.RowCount - 1
                 If SWannotationtable.Text(j, 5) = "1" Then
                 SWannotationtable.Text(j, 6) = "1"
                 Else
                 SWannotationtable.Text(j, 6) = ""
                 End If
                Next
                
                result3 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "L E V E L", swInsertColumn_DefaultWidth)
                
                
                For j = 1 To SWannotationtable.RowCount - 1
                 If SWannotationtable.Text(j, 5) = "2" Then
                 SWannotationtable.Text(j, 7) = "2"
                 Else
                 SWannotationtable.Text(j, 7) = ""
                 End If
                Next
                
                
                result4 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "L E V E L", swInsertColumn_DefaultWidth)
                
                
                For j = 1 To SWannotationtable.RowCount - 1
                 If SWannotationtable.Text(j, 5) = "3" Then
                 SWannotationtable.Text(j, 8) = "3"
                 Else
                 SWannotationtable.Text(j, 8) = ""
                 End If
                Next
                
                
                result5 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "L E V E L", swInsertColumn_DefaultWidth)
                
                
                For j = 1 To SWannotationtable.RowCount - 1
                 If SWannotationtable.Text(j, 5) = "4" Then
                 SWannotationtable.Text(j, 9) = "4"
                 Else
                 SWannotationtable.Text(j, 9) = ""
                 End If
                Next
                
                
                result6 = SWannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "L E V E L", swInsertColumn_DefaultWidth)
                
                
                For j = 1 To SWannotationtable.RowCount - 1
                 If SWannotationtable.Text(j, 5) = "5" Then
                 SWannotationtable.Text(j, 10) = "5"
                 Else
                 SWannotationtable.Text(j, 10) = ""
                 End If
                Next
                
                
                Exit Sub
                handler:
                swapp.SendMsgToUser ("Cannot find BOM with the name: " + BomFeatureName)
                End Sub
                

                 

                The only thing now left is the code is little substandard because i never did coding. This code sometimes give results and sometime don't. So please can you tell me where the problem is.

              • Re: Add custom column with formula to BOM table
                Yash Kothari

                I somehow tweaked the code from other forums and modified. I was able to insert a column with header. Now the cells of the column need some value, i am stuck here. I need the values of this column as the number of digits(e.g. 1, 2, 3 ..) in the item number column of the BOM table. Please someone guide me.

                 

                The modified code is:

                Sub main()
                On Error GoTo handler:
                Dim BomFeatureName As String: BomFeatureName = "Bill of Materials1"
                Dim swapp As SldWorks.SldWorks
                Dim swmodel As ModelDoc2
                Dim swannotationtable As TableAnnotation
                Dim result As Boolean
                Dim featuremanager As featuremanager
                Dim feature As feature
                Dim vfeature As Variant
                Dim Colvalue As String
                Set swapp = Application.SldWorks
                Set swmodel = swapp.ActiveDoc
                Set featuremanager = swmodel.featuremanager
                vfeatures = featuremanager.GetFeatures(True)
                For i = 0 To UBound(vfeatures)
                Set feature = vfeatures(i)
                If feature.Name = BomFeatureName Then
                Dim bomfeature As bomfeature
                Set bomfeature = feature.GetSpecificFeature2
                Dim bomtableannotation As bomtableannotation
                Set bomtableannotation = bomfeature.GetTableAnnotations(0)
                Set swannotationtable = bomtableannotation
                Exit For
                End If
                Next i
                result = swannotationtable.InsertColumn2(swTableItemInsertPosition_Last, 0, "Addition_Item No", swInsertColumn_DefaultWidth)
                For j = 1 To swannotationtable.RowCount - 1
                swannotationtable.Text(j, swannotationtable.ColumnCount - 1) = swannotationtable
                Next j
                Exit Sub
                handler:
                swapp.SendMsgToUser ("Cannot find BOM with the name: " + BomFeatureName)
                End Sub
                

                 

                Thank you in advance

                • Re: Add custom column with formula to BOM table
                  Yash Kothari

                  Can Someone Suggest me a better program please