Setting up and using an Excel Based Bill of Materials can be frustrating if
the rules are not known between Excel and Solidworks. You may have run into a
couple of error messages in the process, or even ran out of memory with enough
failed attempts. Following are the rules for successfully utilizing an Excel
Based Bill of Materials in
SOLIDWORKS.
Excel has a set of rules for setting up an Excel Based Bill of Materials to be
used in
SOLIDWORKS. While trying
to create one, you may have run into a couple different error messages. The
most common message is, “Error creating Bill of Materials” as seen in
the image below.
Figure 1: Failed creation message
If there are enough failed attempts at creating the Excel Based Bill of
Materials, you may have gotten an error message indicating that there is
insufficient memory, followed by another message saying,
“Excel cannot complete this task with available resources. Choose less data
or close other applications”. If this occurs, you may have to restart the computer.
The first error message is due to the 1st rule not completed. Here we will go
through the steps and rules to successfully set up and implement an Excel
Based Bill of Materials.
Rule #1.
The minimum requirement to create the Bill of Materials in SOLIDWORKS
is the Part Number, and it must be put in a specific way, and defined in
Excel. The column name can be labeled anything but it must be defined in Excel
exactly as, “Partno”.
Here are the instructions for defining a name in Excel:
-
Select the cell that you would like to define. Go to the Formulas tab, then
in Defined Names section, click on Define Name as seen in the figure below.
Figure 2: Define Names location in Excel
The Name Manager allows editing of the names that have been defined. The
“Value” is the column header name, and the “Name” is the name in Excel
Rule #2.
If you want the Quantity or Item Number in your BOM and for it to reflect the
quantity/item number of your SOLIDWORKS parts, you will need to define the
name “quantity”/ “Itemno” (respectively) in Excel exactly as seen in the
figure below, -OR- label the column header as “quantity” / “Itemno” exactly.
You will not need to define a custom property in SOLIDWORKS for the quantity
or the item number, since it is being read in.
Figure 3: Property name defined in Excel
Rule #3.
The Bill of Materials can bring in all other custom properties established in
SOLIDWORKS by either defining the name in Excel or by matching the column
header. The names must be identical. For SOLIDWORKS, if there are spaces in
the names of your properties, they must be filled in (e.g. an underscore),
where in Excel spaces are fine. An example of this is shown below.
Figure 4: Material Property format in SOLIDWORKS
Figure 5: Material Property format in Excel
When ready to insert, the drawing view must be pre-selected for the Excel
Based Bill of Materials option to be available. After clicking ok on the
options prompt, SOLIDWORKS will take a moment or two to bring the table in the
drawing. This Bill of Materials is editable inside of SOLIDWORKS with a double
click, but will not save back to the BOM template. Editing the table inside
SOLIDWORKS you will see $$END as the last column header. This is automatically
brought in for
SOLIDWORKS to know where
the stopping point is and will not show up in the table.
