I would like to cut & paste (as a link) from an SW BOM into an Excel BOM template. Linked, so as to update whenever SW BOM updates. Has anybody found a successful way?
Automatically Update BOM in Excel.mp4 - YouTube
Thank you for your response. However, I would like to do that same process, but with the SW BOM, as opposed to the excel-based BOM.
In short: no.
The best solution I've found is to save the SW BOM to a .txt file and use Excel's "Data" tab to import the .txt file with proper delimiter (space, I believe) chosen. However, this still requires saving out the .txt file each time you wish to update the Excel file.
That is, more or less, how we do it now. We save out as an *.xls, then cut & paste into our excel template. However, nothing updates, which is why I am going thru this exercise.
The Excel-based BOM, seems glitchy, and I have read about the possibility of it going away in future versions of SW. So I don't want a short term solution.
You're correct, the Excel-based BOM is definitely glitchy. I've tried countless ways of linking SW to excel, but it was hopeless. We use an excel BOM template which has information not available in SW, as well as a header and footer. We "save as" the SW BOM to a .xls file, then copy and paste into the Excel BOM template with "match destination formatting" in order to maintain bold, italics, underline, merged, etc. cells.
It's extremely frustrating having to re-save and re-copy/paste every time an assembly changes.
Hello Robert. One possibility may be to jury rig excel a bit:
1) Simply save out your BOM as an excel file (let's call it BOM A)
2) Create another excl file (Let's call it BOM B)
3) (We are assuming BOM B is the actual, excel BOM template you desire, with its accompanying formatting)
4) Then, set cells in BOM B equal to the cells in BOM A that you want to pull information from. For example, let's say cell A1, in BOM B, equals "Part Number". Then you can set cell A2 = to the first cell that you desire to pull a part number from in BOM A. You can then copy the formula down as far as you wish.
5) What we are doing here is making BOM B simply an, "information puller".
6) Now imagine you save and close out BOM A and BOM B
7) You then make a change to the assembly so that its BOM is now updated to have, let's say, MORE part numbers
8) You can then save out the BOM under the same name, "BOM A". This will overwrite the original with your version that has more part numbers
9) There is no need to open BOM A after this
10) But if you open BOM B, cells in the "Part Number" column were linked to BOM A so it will pull in the part number changes automatically (assuming you've copied the formulas, pulling info from BOM A, down far enough)
11) This way all you have to do is save out the BOM, from SolidWorks, as the same name each time, open up your "BOM B", template, allow the changes to update and there you have it. You can put all your desired formatting onto BOM B, set as many cells in BOM B = to cells in BOM A that you wish and even prevent 0's using an if statement.
I would be happy to provide more details if need be. I hope this helps.
Thank you for the suggestions. I am going to experiment a bit with it. From my initial tests, it seems to work nicely. A few caveats that I will have to deal with:
Looks like when we move the job from local hard drive (C:) to network (S:), excel looses the link. Which may be a good thing. Someone else opening the file will not over-write it with the current BOM A, on my hard drive.
The downside is, excel looses the link. If I do want to update it, I have to Edit the links back to C:\....\BOM A. The danger in that is now the links are updated, and a builder, purchaser or other people can open it, update the links to whatever job last resided on my hard drive....which may be completely unrelated.
Not really the smooth answer I was hoping for.
Aaron, any other ideas?
1) As long as you don't move things it should be fine. You could keep BOM A and BOM B in a shared folder on your S: drive. That would keep things linked
2) If someone tries to work on either BOM A or BOM B, when they are open on another machine, they will not be able to save anything so that is one check & balance
3) From there you might consider macros
4) If you click on the view tab in Excel you can access macros to the right
5) If you choose, "Macros", "Record Macros", you can create one
6) They basically record your clicks
7) Imagine, then, that sheet 1 of BOM B has the "living data" that was pulled from BOM A
8) Now imagine that you record a Macro that does the following:
1. Creates a new sheet
2. Copies the living data from sheet 1
3. Pastes in onto the new sheet and formats it properly (this will create a rev of the BOM)
9) You could then insert a form control button (from the developer tab) on sheet 1 that activates the macro (rt click on the button and choose assign macro)
10) So, in summary, your workflow would be as follows:
1. Save BOM A from SolidWorks
2. Open BOM B
3. Run the macro
11) You will then have a new rev, of the most current BOM, that is sort-able and archived
12) You can also use password protection to control access (look this up in the help files. you can protect workbooks, sheets and regions of a sheet)
how about use a absolute address like
\\ [computer name] \ Folder Name \ File Name
Retrieving data ...