3 Replies Latest reply on Feb 22, 2018 11:02 AM by Jay Donihoo

    BOM macro: Find string in Part Number column and return sum of QTY's

    Jay Donihoo

      Hello,

       

           I would like to look through the bom to find the cells that contain certain strings. I then want it to add up and return the qty's column. No doubt this can be cleaner. This is what I have  that seems like it works for storing the qty's in an array.

       

      Sub qty_Array()

       

          Dim QTYarray() As Integer

          Dim size As Integer

          Dim Probe As String

          Dim i As String

               

      Set swApp = Application.SldWorks

      Set drawdoc = swApp.ActiveDoc

      Set View = drawdoc.GetFirstView

       

      size = 1

      index = 0

      ReDim QTYarray(size)

          

          Set TableAnn = View.GetFirstTableAnnotation

          Do While Not TableAnn Is Nothing

          If TableAnn.Type = 2 Then '2 is the bom table type

              Set Bom = TableAnn

              Exit Do

          End If

          Set TableAnn = TableAnn.GetNext

      Loop

       

      For Row = 1 To Bom.RowCount - 1

      If InStr(1, Bom.Text(Row, 1), "WR", vbBinaryCompare) Or InStr(1, Bom.Text(Row, 1), "W", vbBinaryCompare) > 1 _

      Or InStr(1, Bom.Text(Row, 1), "BC", vbBinaryCompare) Or InStr(1, Bom.Text(Row, 1), "P", vbBinaryCompare) > 1 Then

          MsgBox (Bom.Text(Row, 3))

          QTYarray(index) = Bom.Text(Row, 3)

             

          size = size + 1

          ReDim Preserve QTYarray(size)

          index = index + 1

         MsgBox (QTYarray(1)) 'Next

      Else

      End If

        Next

          End Sub

       

      QTY's are located: Bom.Text(Row, 3)
      Any help would be appreciated.

        • Re: BOM macro: Find string in Part Number column and return sum of QTY's
          Simon Turner

          Tidied up a bit for you:

           

           

          Option Compare Text

          'Sets the default to case insensitive. Probably what you want

           

          Sub qty_Array()

               Dim QTYarray() As Integer

               Dim size As Integer

               Dim Probe As String

               Dim i As String

               Dim Part As ModelDoc2

               Dim drawDoc As DrawingDoc

               Dim aView As View

               Dim aNumber As String

           

               Set swApp = Application.SldWorks

               Set Part = swApp.ActiveDoc

               If Part Is Nothing Then Exit Sub

               If Part.GetType <> 2 Then Exit Sub

               Set drawDoc=Part

               Set aView = drawDoc.GetFirstView

           

               size = -1

              

               Set TableAnn = aView.GetFirstTableAnnotation

               Do While Not TableAnn Is Nothing

                    If TableAnn.Type = 2 Then '2 is the bom table type

                         Set Bom = TableAnn

                         Exit Do

                    End If

                    Set TableAnn = TableAnn.GetNext

               Loop

               If Bom is Nothing Then Exit Sub

           

               For Row = 1 To Bom.RowCount - 1

                    aNumber = Bom.Text(Row, 1)

                    If InStr(aNumber, "WR") > 0 Or InStr(aNumber, "W") > 0 Or InStr(aNumber, "BC") > 0 Or InStr(aNumber, "P") > 0 Then

                         size = size + 1

                         ReDim Preserve QTYarray(size)

                         QTYarray(size) = Bom.Text(Row, 3)

                         Debug.Print QTYarray(size)

                    End If

               Next

          End Sub

           

            • Re: BOM macro: Find string in Part Number column and return sum of QTY's
              Jay Donihoo

              Simon,

               

                   It kind of worked. Something is happening at "If Part.GetType <> 2 Then Exit Sub" Guessing the 2 is not the correct type? I worked it out yesterday to get the array storing right The only problem I'm having now is adding the numbers in the array together. Seems the only thing I can find online for doing something similar is through excel. Surely I'm not the only one that's ever tried adding array values together outside of Excel. Maybe there's a better way to do this? I picked an array as this is always a variable list of instances and qty's. I'm open to other ideas.

               

            • Re: BOM macro: Find string in Part Number column and return sum of QTY's
              Jay Donihoo

              In case anyone else is looking for the answer:

               

              Sub qty_Array()

               

                  Dim QTYarray() As Variant

                  Dim size As Integer

                  Dim QTY As Variant

                  Dim index As Integer

                  Dim sum as integer

                     

              Set swApp = Application.SldWorks

              Set drawDoc = swApp.ActiveDoc

              Set View = drawDoc.GetFirstView

               

              size = 1

              index = 0

              ReDim QTYarray(size)

                

                  Set TableAnn = View.GetFirstTableAnnotation

                  Do While Not TableAnn Is Nothing

                  If TableAnn.Type = 2 Then

                      Set Bom = TableAnn

                      Exit Do

                  End If

                  Set TableAnn = TableAnn.GetNext

              Loop

               

              For Row = 1 To Bom.RowCount - 1

              If InStr(1, Bom.Text(Row, 1), "WR", vbBinaryCompare) Or InStr(1, Bom.Text(Row, 1), "W", vbBinaryCompare) > 1 _

              Or InStr(1, Bom.Text(Row, 1), "BC", vbBinaryCompare) Or InStr(1, Bom.Text(Row, 1), "P", vbBinaryCompare) > 1 Then

                  'MsgBox (Bom.Text(Row, 3))

                  QTYarray(index) = Bom.Text(Row, 3)

                    

                  size = size + 1

                  MsgBox (QTYarray(index))

                  ReDim Preserve QTYarray(size)

                  index = index + 1

               

              Else

              End If

              Next

               

              For Each i In QTYarray

              sum = sum + i

              Next i

              'MsgBox (sum)

               

              End Sub