Excel BOM's

SolidWorks Tips & Tricks - EXCEL based Bill of Materials
My own versions of tips and tricks that I use when working with SolidWorks. These tips are not intended to be a repeat of tips you can find elsewhere on the internet.

GENERAL TIPS.

  • Familiarize yourself with the following sections in the SolidWorks help file:
    • 'Bill of Materials Overview'
  • At a minimum, all bill of material templates must contain the following columns:
    • Item Number
    • Quantity
    • Part Number
    These columns do not have to appear in the drawing, but they must exist in the bill of material template. To prevent a column from appearing in a drawing, hide (do not delete) the column in the template.

EXCEL'S CONCATENATE FEATURE

  • Use EXCEL's 'CONCATENATE' feature to automatically combine data from multiple properties into a single field, then hide (do not delete) the original columns. This is useful when you have multiple description custom properties (to fit in drawing) but you want all lines to show in a single field in the assembly bill of materials.
USE CUSTOM BOM TEMPLATES
  • Custom BOM templates allow you to control what is shown in the Bill of Materials on a drawing. Below are a couble of good examples of how to create these custom BOM's.

    Allow a mix of standard and generic part numbers and descriptions in BOM.
    • Copy existing bomtemp.xls to bomtemp(generic).xls
    • Edit bomtemp(generic).xls template in Excel.
      • Add four columns immediately to the right of the 'DESCRIPTION' column.
      • Label the columns E thru H as 'GENERICPARTNO', 'GENERICDESCR', 'PART NO.', and 'DESCRIPTION' in the order specified.
      • For cell E1, in the 'Name box', enter 'GENERICPARTNO'.
      • For cell F1, in the 'Name box', enter 'GENERICDESCR'.
      • In cell G1 ('PART NO.' column) add the formula '=IF(ISBLANK(E2),C2,E2)'.
      • In cell H1 ('DESCRIPTION' column) add the formula '=IF(ISBLANK(F2),D2,F2)'.
      • Copy the formulas down from G2 and H2 to G??? (??? = the biggest BOM you anticipate).
      • Select columns C thru F. Right click on a selected column and select 'Hide'.
      • Save and close the bomtemp(generic).xls template and place in your BOM template directory.
    • Edit the component model (part or assembly) that needs the generic part number and description:
      • Add the custom properties 'GENERICPARTNO' and 'GENERICDESCR' to the 'Custom File Properties'
      • Fill in the appropriate values.
      • Note: A value must be defined. If a blank is needed, enter a space.
    • Edit the drawing:
      • Delete existing BOM if it exists.
      • Insert a new Bill of Materials and select the 'bomtemp(generic).xls' template.
      • Note: if any changes to the assembly are made, you must open the BOM template in the drawing to force an update of the visible columns.
    • How It Works: Via normal SolidWorks operations, the custom properties (based on column name) are read from the component model and placed into the corresponding columns in the BOM. Using the Excel formulas (in the new BOM template), if the 'Generic' columns have a value, it is used, otherwise, the normal value is used.
    • Download sample bomtemp(generic).xls file.

    Identify a raw material or base part for a part model.
    Yes! You can insert a Bill of Material into a part drawing.
    • Copy existing bomtemp.xls to bomtemp(basepart).xls
    • Edit bomtemp(basepart).xls template in Excel.
      • Add one column immediately to the right of the 'PART NO.' column.
      • Hide (do not delete) existing 'PART NO.' column.
      • Label the columns D as 'PART NO.'
      • For cell D1, in the 'Name box', enter 'BASEPARTNO'.
      • For cell E1, in the 'Name box', enter 'BASEDESCR'.
      • Save and close the bomtemp(basepart).xls template and place in your BOM template directory.
    • Edit the component model (part or assembly) that needs the base part number and description:
      • Add the custom properties 'BASEPARTNO' and 'BASEDESCR' to the 'Custom File Properties'
      • Fill in the appropriate values.
    • Edit the drawing:
      • Insert a new Bill of Materials and select the 'bomtemp(basepart).xls' template.
    • How It Works: Via normal SolidWorks operations, the custom properties (based on column name) are read from the component model and placed into the corresponding columns in the BOM. We are just redefining what properties are shown in the Bill of Material. In this case, all properties are read from the part model.
    • Download sample bomtemp(basepart).xls file.