Is there any possibility for creating a custom BOM like this format?
Is there any possibility for creating a custom BOM like this format?
is there any macro for this?
I`ve found one more way via tree house, export to excel,you'll get table like this
Notice different indent, you can use it for restructuring.I use MathCAD for all such purposes.
Hi,
No need for macro. If you export your table out, then you can do it simply using basic Excel functions. Look up Len, Substitute & repeat function. You can expand the formula to move text to individual cells per your example. Use Google if you have difficulties.
Elmar
Hello,
This is similar to this previous post: https://forum.solidworks.com/message/501375#comment-501375
The attachede PDF is from my reply there.
If it's of interest, the SW files used to make it are attached in this old post.
Cheers,
Kevin
EDIT: You may need to do an Excel based BOM to get the quantity math to work by finding each child's parent quantity.
With basic SW BOM, if I'm remembering correctly from the old post, I don't believe its possible to locate each parent quantity to be able to do the quantity math for its children.
Hello again,
Attached is an Excel file that has the logic to do the child quantity math based on the outline of the PDF BOM in my previous post.
Intermediate columns would have to be hidden but it appears to work and its dynamic, so a changing BOM should be reflected properly.
Cheers,
Kevin
EDIT: I forgot to add the column for all qtys in one column as shown above.
Attached is #2 Excel with this column added, otherwise it's the same as the first file.
Hi Kevin,
You can combine the child-reference in one lookup formula for any size of indented list. If your first column is the Index (IN). Going right is level (LVL), description (DESC9, qty per component (QTY/ASM) and last your total quantity. Column "I" will indent the description in "D" to show a graphic display of the child relationship. Column "K" is adding up the qty per to calculate the total quantity as per image 2.
copy/paste sample: =IFERROR(LOOKUP(2,1/($H$2:H2=(H2-1)),$K$2:K2),1)*J2
Image 1
Image 2
If you export out your BOM with indented numbering selected, then you can easily transfer the cell values as per Image 2.
Change the formula in "I" to =CONCATENATE(REPT(" ,",H2),D2) that way you add a space & comma.
Highlight the cells and copy/paste the content into a txt file. Simply import the txt file back into Excel and select comma-separated list and all cell values will end up in it's own excel cell.
But will we be able to save the excel format back to solidworks as Solidworks BOM Template?
No, SW can't do what you want unless you break the parametric link. You can export the BOM and import a modified xls Table.
But that table is static and won't update with model changes. The SW Bom changes. You can use an Excel Bom but again, if rows change you will have to modify the file to reflect the changes.
Elmar
Hello,
Attached is the Excel BOM file.
Be sure to add it as indented.
Thank you Elmar Klammer for the Excel tips.
Great suggestions, but when I got down to doing the BOM in Excel, the format changed, so I had to adjust the logic.
I hope this helps.
Cheers,
Kevin
EDIT: Found an error, please standby...
Hi Kevin,
You need to export the BOM to text file. Excel causes issues with indented Numbering in cases like 1.1.10 cause it omits trailing 0 and turns it into 1.1.1 That being said, if you use the text import option in Excel make sure you set the format to text. If you don't do that then you get fractions as dates and the above mentioned trim issue.
But then it works like a charm. Make yourself a template with the formulas and you have a modified BOM in a jiffy. I recommend using indent with numbering. It allows you sort the table without loosing the initial setup.
Elmar
Hello again,
Repairs to the above are attached.
Columns need to be hidden.
Cheers,
Kevin
Only with API, IMHO