Home > How-To > Create a Custom Excel BOM

Create a Custom Excel BOM

April 26th, 2009

While using an Excel based BOM (as opposed to a standard BOM) is not as flexible as a standard Bill of Materials, there are various customisations which can be done. E.g. if you want a complete project costing (taking into account price per meter length of piping or weldments). Note: in recent versions of SolidWorks (2008,2009 etc.) it is possible to do totals and equations in standard BOMs, however they require some post manual tweaking.

This post shows how a custom Excel based Bill of Materials for a SolidWorks Assembly drawing can be created.

  • Find out where BOM templates are stored: Open SolidWorks -> Tools -> Options -> File Locations -> BOM Templates
  • Open bomtemp-all.xls  Look at the various Headings in Row 1.
  • Remove/Add Columns to suit making sure to type in the SolidWorks File Property name into the “Name Property” box (see image below).
  • Do NOT remove the Item No. Column or Part No. Column (otherwise your Excel BOM will act strange, adding several duplicate rows into the BOM.) (I spent quite a while caught on this.) FYI this fact is noted in the SolidWorks Knowledge Base. Customers on subscription can view the SolidWorks Knowledge Base at http://customerportal.solidworks.com
Name Properties used in Excel

Name Properties used in Excel

Its also possible to see all Name Properties in an Excel file: Insert -> Name -> Define (Excel 2000/2003)
(In Excel 2007, its called “Name Manager” and its on the Formulas button. Alt+m+n also works)
This shows up the list of Name Properties in the Excel file. Be extremely careful!

Once the Excel file is saved, go to SolidWorks, pre-select the view (typically the Isometric view) and then go: Insert -> Tables -> Excel Based Bill of Materials. Choose your template file, and voila. Note: The name you entered in the “Name Property” box in Excel must correspond with the Property in the PART. Go to the Part, File -> Properties -> Custom and make sure the Property name is present.

If you formatted the Headings in your Excel template, and wish for these to come through, make sure to have “Use the document note font when creating the table” unchecked when initially Inserting the Excel Based BOM.

In a recent Piping SolidWorks Project, I wanted a complete costing done on my BOM. I created several columns, including “Unit Cost”, “Pipe Cost”, “Total Unit Cost” and “Total Pipe Cost”. In the once-off parts I created a unitcost SW Property in the parts. For the piping (same with weldments), I opened the source pipe file (used in routing), and added a costpipe property (which had a cost per meter). An image of the completed table can be seen below. This Excel template file can be downloaded from here. (There are 2 columns containing calculations which are hidden. This level of detail would not have been possible in a standard Bill of Materials).

Excel based BOM used in Project Costing Example

Excel based BOM used in Project Costing Example

This level of customisation available within SolidWorks is just the tip of the iceberg!

EDIT ——————-

So I came across an issue with Excel BOM’s which caught me.
It is not possible to have properties such as SW-Title, SW-Author, SW-Filename, or any property with a hyphen ( – ) in its name in an Excel based Bill of Materials. This limitation is caused by Microsoft Excel. In the Name Property box for each cell, it is not possible to have a hyphen ( – ). The workaround given by SolidWorks (S-015804) (On the Knowledge Base at http://customerportal.solidworks.com ) is to create a Custom Property (as normal in SolidWorks). E.g. to have SW-Title appear in a BOM, you need to create a Custom Property called SWTitle and link it to: $PRP:”SW-Title”. Then when creating the Excel BOM template, insert the Name Property SWTitle. Of course if you have a project already created, and only realised this at the end, its a little more difficult. SolidWorks Task Scheduler may be able to add this Custom Property to all of your files.

Hopefully this will help someone, and me too in case I forget!
————————-

How-To

  1. Sean MacNeil
    May 21st, 2009 at 17:41 | #1

    Thank you for the article, the download was helpful. Was having trouble loading the supplied excel bom from Solidworks, it kept erroring out and not populating. Which in turn the operation would then cease and not produce a bom, not even a blank sheet. Root cause the cell names were not entered for the column names. By using your spread sheet I was able to reolve this issue. There is the possiblility piping cost maybe in our design future as well. Vista 64 SW09. Again many thanks!!

  2. May 21st, 2009 at 19:36 | #2

    Good to hear you got it working Sean. I’m glad I provided the Excel download, which was actually an after-thought.

  3. donno
    August 20th, 2009 at 23:50 | #3

    nice job.
    Question: can multiple QTY columns be used in an Excel-based BOM, like in a SW BOM. eg: you have 4 configurations of an assy, and you want your BOM to have 4 QTY columns, one for each config ?

  4. August 21st, 2009 at 00:22 | #4

    Good question Donno. From testing it there in SolidWorks, the Standard BOM does show an extra column for Quantity in multiple configurations.

    Although I could be wrong, from testing this in SW, and looking at the SolidWorks Knowledge Base, reading “Solution Id: S-026416”, it appears that its not possible to have an Excel BOM show multiple QTY / Quantity columns for multiple configurations. If the configuration of the Assembly, in which the Excel BOM is based on, is changed, then the BOM’s single QTY column will reflect the current active configuration showing.

    You could insert 2 Excel BOMs (in SW2009), 1 Excel BOM for each configuration, and go that route.

  5. biren
    December 19th, 2009 at 11:47 | #5

    I am not getting cut length in my bom.
    I am using default solidworks templates.

    What should be problem???

    you can get me on birendm gmail.com

  6. December 21st, 2009 at 22:01 | #6

    Hi Biren,

    Typically you have to insert a “Weldment Cut List” to get the “cut length” to appear in a BOM. If you don’t have a weldment part, you won’t see the option for a Weldment Cut List in a Drawing.

    In SolidWorks 2008, a extra option appeared when creating a SolidWorks based Bill of Materials. This extra option was “Detailed Cut List”.
    If you want your Weldments and Cut Lengths to appear in a BOM, then you must:
    A. Have SolidWorks 2008 or later.
    B. Have used a Weldment feature in at least one part (and see the Cut-List in the part design tree, and right-click and go “update”)
    C. In the Drawing -> Insert -> Tables -> Bill of Materials, choose “Indented” and select the tick box “Detailed Cut List”.
    D. If you do not see a “Length” column, hover the mouse over the BOM, right-click on the “A” column header, and go Insert -> Column Right. In the drop-down choose Custom Property, and in the second drop-down choose “Length”.

    Hope this helps.

  7. jiten Sompura
    December 25th, 2009 at 08:30 | #7

    Thank you sburke for the nice info.
    One question is can i insert the grand total row also from excel.
    Regards,
    Jiten

  8. January 4th, 2010 at 23:52 | #8

    Hi Jiten,

    You can indeed insert the grand total row, however it requires a manual step. Once you insert the Excel BOM and its on the drawing, you need to right-click on the BOM and go “Edit”. You then need to add in a row manually, and insert the cell =sum(B2:B10).

    There are some caveats:
    If you add a second part, the total quantity won’t fully update until you go Edit on the BOM and open the embedded Excel Window.
    If you add a new part, the row you added will stay, and the new part will be listed in a new row below.
    With a little workarounds could be gotten. I.E. add a row at the very top, and show the Total Quantity there.

    Also, there is an excellent SolidWorks Screencast on using Microsoft Excel: http://www.solidworks.com/sw/DemoLibrary/tipsforengineers.htm

  9. IVAN NOGALO
    August 25th, 2011 at 21:48 | #9

    I NEED TO CREATE A VISUAL BOM CHECKLIST. HOW CAN I ADD A PREVIEW IMAGE TO A BOM, NOT FROM AN EXPLODED VIEW.

  10. August 25th, 2011 at 22:07 | #10

    Ivan: As far as I know, you can’t automatically create a BOM which will have a preview or thumbnail image for each row. In SolidWorks 2010, with a regular Bill of Materials (not Excel based), you can expand out the left column, and as you hover the mouse over the cells, you can see a thumbnail image. Unfortunately there is no option to show this thumbnail in each row. (Video of new 2010 BOM thumbnail images -> http://www.youtube.com/watch?v=MQXtia8EfHM
    It’s a nice idea and definitely a good enhancement request. It would be worth looking on the SolidWorks Forums to see if there are any other people asking about this. There might be some API method, however it would require some coding.

    At this stage, you might have to take a screenshot image of each item and manually add the images in beside each row in a SW BOM in the Drawing.

  11. Snolol
    September 15th, 2011 at 03:26 | #11

    Great stuff you’ve got here!
    I am trying to create a template for our relatively large models.
    Couple of questions
    Can I have a detailed cut list go into a excel based BOM???

    If so is there a “property code” for the Cut List Name???

    and finally, Is there a way to use the detailed numbering in an excel based BOM???

    Thanks for your wonderful help

  12. September 16th, 2011 at 13:52 | #12

    Snolol: I’m afraid it’s not possible to have a detailed cut-list in an Excel based BOM. (If you saved all bodies out of a Weldment Part and made an assembly, then the lengths of each part might show up in the Excel based BOM. However there are limitations and issues with saving out all bodies in a Weldment Part. Any newly added/removed bodies won’t update in the Assembly).

    I’ve also found Excel based BOMs slower on large Assemblies.
    You would be best to use the standard SolidWorks BOM and manually add in equations (which has its limitations also).

    Maybe the new Costing tool in 2012 will be expanded in the next year to include costs for Weldments instead of just for Sheet Metal and Machined parts.

  13. mihu
    November 20th, 2012 at 20:58 | #13

    Hello. Nice tutorial, but I have problem. I have gray icon of Excel BOM.
    Look: http://img841.imageshack.us/img841/9311/beznzvuho.png

    Thanks for help.

  14. November 20th, 2012 at 21:02 | #14

    @mihu: Select the assembly view first (by clicking on the dotted orange border of a view) and then go Tables -> Excel Based Bill of Materials

  15. mihu
    November 20th, 2012 at 21:47 | #15

    Omg.. Im noob :( Thank you very much, it was easy :)

    Can You help me with diferent problem? With Run macro.

  1. No trackbacks yet.