Excel Quantity macro 1

[cowski]

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

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.