×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Quantity macro

Excel Quantity macro

Excel Quantity macro

(OP)
A while back someone asked how to get assembly level information into a spreadsheet. Since then I have had the pleasure of working on a BOM in Excel. I worked out a macro to parse out the quantity information and add it to its own column.

How to use:
[I'm using NX2, some items may be different on other versions]
1) change the part name style to "file name"
go to preferences -> assemblies and look near the bottom of the dialog box.
2) in the assembly navigator expand all and pack all (options on the right mouse popup menu)
3) export to spreadsheet (right mouse popup menu)
4) import code into excel
5) run macro

CODE

Option Explicit

Dim strPartNumber As String
Dim strQuantity As String

Sub Macro1()

  Call ParseQuantity

End Sub

Private Sub ParseQuantity()

  Dim i As Long
  Dim j As Long
  
  'insert quantity column
  'change this as needed
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight
  Range("B1:B1").Select
  ActiveCell.Value = "Quantity"
  Columns("B:B").Select
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    .NumberFormat = "General"
  End With
  Columns("B:B").EntireColumn.AutoFit

  'select first cell in part number column
  'change this as needed
  Range("A2:A2").Select
  
  'loop through cells that are not empty
  'when macro encounters an empty cell it stops
  Do Until ActiveCell.Value = ""
    strPartNumber = ActiveCell.Text
    i = InStr(1, strPartNumber, " x ")
    If i = 0 Then
      'string not found, quantity = 1
      ActiveCell.Offset(0, 1).Activate
      ActiveCell.Value = "1"
      ActiveCell.Offset(0, -1).Activate
    Else
      'string found, quantity > 1
      j = Len(strPartNumber)
      strQuantity = Right(strPartNumber, j - i)
      'strip off first 2 characters of quantity string - "x "
      strQuantity = Right(strQuantity, Len(strQuantity) - 2)
      'strip off last character of part number string (a space)
      strPartNumber = Left(strPartNumber, i - 1)
      'record part number with quantity stripped off the end
      ActiveCell.Value = strPartNumber
      'move cell over a column and record quantity
      ActiveCell.Offset(0, 1).Activate
      ActiveCell.Value = strQuantity
      'move cell back to partnumber column
      ActiveCell.Offset(0, -1).Activate
    End If
    'move active cell down 1 row
    ActiveCell.Offset(1, 0).Activate
  Loop

End Sub

Hope this helps somebody.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources