7 Replies Latest reply on May 1, 2015 8:13 AM by John Stoltzfus

    Custom Excel BOM

    Mark Greenwell

      HI,

       

      I am for the 1st time trying to set up an Excel based BOM as opposed to the standard SolidWorks BOM.

       

      I have fount the Excel BOM templates and thought I could just edit one to suit.

       

      While some properties now appear on the BOM some are taking the wrong information.

       

      Attached are some screen shots.

       

      I have an assembly with multiple part all with custom properties.

       

      I want the BOM to take the information from these custom properties,

      I cannot figure out how to link the Evaluated Value to the BOM, these would be in this screen shot, Length (500) and Section Size. (Plt 30*165)

       

      properties.PNG

       

      This is what appears on the BOM, which are Value / text expression for length and nothing for the Section Size.

       

      I guess it could be how I have set the Excel file up but not sure what I have done wrong.

       

      BOM.PNG

       

      Also attached is the edited excel file.

       

      Could some one please explain what I need to do.

       

      Thanks

       

      Mark (SolidWorks 2015 sp2.1)

        • Re: Custom Excel BOM
          Kevin Chandler

          Hello,

           

          To test my suspicions, I tried your template on a test assy/dwg and it worked fine (at least for the length property, which is all I defined).

           

          I believe your issue is this: Your custom properties aren't in your custom properties text file so the Excel BOM doesn't "see" them.

           

          On the properties dialog, click the "Edit List" button to add your properties.

          You'll only have to do this once for each custom property (& whenever you create a new property).

          Plus, you only need to "publish" the customs that require this kind of visibility.

          It doesn't appear to be case-sensitive. Mine is defined as "LENGTH", while yours is "Length" and it came into the BOM fine.

           

          If you're in a shared environment, I suggest putting your custom properties text file on a common file share, so added properties are available to everyone at all times.

          Also, try to standardize (agree, make sure people check before publish, etc.) on your names to cut down on repetitive efforts,

          For example: "Length" instead of "Long".

          Either is fine, but since they're basically the same, you won't end up publishing "near misses" and you won't have SW models with properties of similar context but with different spellings.

           

          Our shard location is "Z:\Engineering\SolidWorks Data\Custom Properties".

          As you can see, this path is SW version independent, so there's no more mods required when you update SW.

          I'd also suggest putting other common SW items in their own folder here as well for the same reasons.

           

          If you do relocate your custom properties text file, you must update its location in (everyone's) SW.

          Go to System Options\File Locations\Custom Property Files and browse to your new location. (Delete the old path to save troubles later on.)

           

          I hope this helps.

           

          Cheers,

           

          Kevin

            • Re: Custom Excel BOM
              Mark Greenwell

              Hi Kevin,

               

               

              Adding  them to the custom text file doesn't do anything.

               

              I thinks the problem is the Excel BOM is reading from the value / text column where I want information from the evaluated column.

               

              For the length value this is automatically filled in depending on the 1st sketch line I dimension I.e. 935, and shown as evaluated value

               

              For the size this again is automatically filled in by the 2nd sketch line I dimension and also by the extrude I first use I.e. Plt 15 * 450 again as an evaluated value.

               

              If I over ride these and manually type in these figures the BOM it works ok.

              However this takes away the automatically populating of these columns I have at the moment.

               

              What I cant figure out is if I use the SolidWorks BOM it uses the Evaluated values so everything is ok.

               

              What I need to know is how to get the Excel BOM to also read from the Evaluated Colum as well.

               

              Couple of screen shots to explain.

               

              Properties.PNG

               

              Auto filled in

              BOM.PNG

               

              Manually type in info

              BOM 2.PNG

               

              Thanks

               

              Mark (SolidWorks 2015 sp2.1)

            • Re: Custom Excel BOM
              John Stoltzfus

              Mark the Custom Property list you show on the left "Property Name" - those same properties should show in your "Edit Custom Property List"

               

              Those fields need to be the same in your BOM -

               

               

               

               

               

              In the BOM you can add columns and link them to the same fields however like Kevin mentioned those need to be in your "Edit" list

                • Re: Custom Excel BOM
                  Mark Greenwell

                  Hi John,

                   

                  Bit lost now on my original post I attached the BOM.

                   

                  The fields I need to fill in are taken from the Property name, Length and Size.

                   

                  These are shown in the BOM, but the do not fill in properly, all the other boxes do.

                   

                  Thanks

                   

                  Mark

                    • Re: Custom Excel BOM
                      John Stoltzfus

                      You can do the same with the standard SW BOM - I think the Excel BOM can act a little strange at times - Insert the SW BOM and add your properties the same way and then right click on the BOM to save it as a template - I have 4 different BOM templates so I can choose which ones to use depending on the information I need for certain documents

                        • Re: Custom Excel BOM
                          Mark Greenwell

                          Hi John,

                           

                           

                          My SW BOM works fine it takes the info from the custom list and fills in how I want to see it.

                           

                           

                          I have never bothered with the Excel BOM before and thought I would have a mess around to see if it was any better than a SW BOM.

                           

                           

                          Looks like it's not as good or at least not as easy to set up.

                           

                           

                          Here is screen shot of my SW BOM I wanted to get the excel BOM which I attached to populate the same.

                           

                          BOM.PNG

                           

                          Thanks

                           

                          Mark

                    • Re: Custom Excel BOM
                      John Stoltzfus

                      It took me years to go from the Excel based BOM to SW BOM - in the beginning I had major issues with it, probably more ignorance then anything else, however then I started to have issues with the Excel BOM and the SW BOM is much more user friendly to setup or so I thought.  Now I use only the SW BOM and like I said certain documents require additional information then I save the BOM as an Excel file and use it in my ECN's or other docs...

                       

                      I was just seeing that your "Edit" dialog box wasn't filled out like your Custom Properties -

                       

                      Do you use Custom Tab Property Builder?