Solidworks Macro - BOM
Solidworks Macro - BOM
(OP)
I want to extend on the Bill of Material function that Solidworks already has. Basically I want a macro to make a Bill of Materials with my custom template (this part I can probably figure out). Then I want to save that BOM as an Excel File (again I could probably figure that out too). Okay hard part:
1. I want to now open Excel and run a macro I already developed to take the BOM in Excel format and do some stuff (arrange things, add a summation of a column,...) and then print it.
2.Excel would then close and I would return to Solidworks with my printed copy of the BOM sitting in the printer.
3. Ending most likely would be a delete of the BOM table from the drawing. (Another one I could probably figure out)
Basically I need to know how to call another program (Excel) from Solidworks, run a macro, and then close Excel.
Any help would be greatly appreciated,
Thanks,
Jason
1. I want to now open Excel and run a macro I already developed to take the BOM in Excel format and do some stuff (arrange things, add a summation of a column,...) and then print it.
2.Excel would then close and I would return to Solidworks with my printed copy of the BOM sitting in the printer.
3. Ending most likely would be a delete of the BOM table from the drawing. (Another one I could probably figure out)
Basically I need to know how to call another program (Excel) from Solidworks, run a macro, and then close Excel.
Any help would be greatly appreciated,
Thanks,
Jason






RE: Solidworks Macro - BOM
2. Use the Quit method to close the Excel application. You could toss this in at the end of your Auto_Open macro.
3. Deleting the BOM is simple. Just record a macro of your actions. You may have to traverse the views to find the one that contains the BOM. (let me know if you need help with this).
4. To control Excel from SolidWorks, include the Excel Object Library in your project references (SolidWorks VBA Editor). Then, you could do something like this:
Workbooks.Open FileName:= _
"C:\YourFile.xls", ReadOnly:=True _
Workbooks.Application.Visible = True
'Change a cell
Range("B2").FormulaR1C1 = sEqn
Workbooks.Close
Hope this helps!
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Solidworks Macro - BOM
It would be nice and easier if you could get excel to open entirely. Now I know you can turn on an option so excel opens in a separate window, but this doesn't work when you open a BOM. I wish it did! (Sounds like an enhancement request to me.) Since excel doesn't open in a separate window, not all the options are available to users. Like the "Zoom" for example, and many other things.
I just tried putting a button in a BOM that would run a macro I made. It worked and I closed out the BOM. But if you go back into it, the button has been squished.
What "dsi" has suggested should work.
I just wish SW would let users have the same ability in a BOM that one has with a Design Table.
Well that's my .02 worth of complaining this morning.
Cheers,
Scott Baugh, CSWP
George Koch Sons,LLC
Evansville, IN 47714
sjb@kochllc.com
RE: Solidworks Macro - BOM
RE: Solidworks Macro - BOM
And another question:
Okay, I still can't make the BOM work, however I have managed to make solidworks open Excel and print out my file.
1. I need to finish figuring out how to make the internal BOM program run using a macro. Then I can combine with my working macros to print out the document.
2. In the assembly file that the BOM will reference, there are Custom Properties I have set (Title, Description). When I create the drawing file, some of those properties are automatically entered into my title block. When I create a BOM I use those properties for each part file). I want to now take the properties of Title and Description from the assembly file and pass them to the excel file that I have already created using the BOM tool and other macros for formatting so that file will have a main Title and description. See example below:
Main Title
Main Description
Bill Of Materials
Item Part No Part Description Vendor
1 3345 xyz bob
2 3347 ggg mike
3 2235 ttt eric
So I can do the part after Bill of materials using the BOM tool (manually anyways) but I can figure out how to pass the Main Title and Main description.
Thanks,
Jason
--My questions will probably never end !!!
RE: Solidworks Macro - BOM
- You have drawings with linked BOMs
- You want a SolidWorks utility to:
- extract custom props
- extract the BOM from the drawing
- create a form with the extracted values
- manipulate the formatting
- print the BOM
I am sure that I can help with most of this. Just let me know if the above describes your basic needs.
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Solidworks Macro - BOM
- Each individual part in my assembly has custom properties (Specifically: Title, Description, Material, Finish, Cost, Vendor, cdate and Reference). I have changed the template created by solidworks (bomtemp.xls) to read some of those custom properties as well as all of the original properties the BOM utility reads (quantity, partno, item no) when the Solidworks BOM utility runs. So I don't believe I need a BOM program because the one solidworks has built in works fine for extracting each individual part's properties. The only problem I am having with the BOM utility is after recording a macro in SW to create a BOM (which I can do while the macro is recording), when I try to replay the macro it tells me "Property not Found or Missing" for the line "part.insertBOM". So currently, I cannot automate the BOM process. As I am sure you know, this BOM utility is done from a drawing that has been created from an Assembly file. Perhaps it would be to my benefit to create a new BOM utility that would run from the assembly itself. However, this I am sure, is beyond my scope.
- The Assembly file has custom properties similar to those of each individual part. When I create a drawig of a part I use some of the properties (such as Title, Description, Material, Finish, Reference, cdate) to be automatically linked and shown in my drawing title block. Similarly, when I create a drawing of the assembly file the title, cdate, and Description should appear in my title block. I want to use that Assembly Title, and Assebly Description to be at the Top of my BOM print out (as shown in my previous thread). I have created a VBA exe that I can run from Solidworks (using the shell command) that will open the file bom.xls, copy the necessary info, paste it into a excel template, calculate numbers such as cost times quantity, then sum of cost times quantity to equal total cost of assembly, then the exe opens an input box asking for title and descrition which I will input manually, then it prints out the Finished BOM and closes the excel program. I don't want to manually enter the title and description, I want to pass it as a variable to the VBA exe program. That title and description comes all the way back from the assembly file which was passed to the drawing title block as a linked property.
Ok so let me recap:
1. I will manually create a drawing file of the assembly.
2. I want to then start a macro that will selected a drawing view.
3. The macro will then run the BOM Utility and save it to the file bom.xls (same file everytime, it will just get overwritten each time I run the macro).
4. The macro will select and delete the BOM from the drawing.
5. The macro now calls the VBA exe program to do the necessary formatting and calculations by opening up the bom.xls and a separate template excel file.
5. The macro passes to the VBA exe program the title and description of the assembly file which was passed to the drawing file title block.
6. The VBA exe places those in the designated cell in the excel template file.
7. Excel template file prints and closes without saving.
8. Macro ends.
The VBA works fine with the exception of getting the title and description automatically.
Ok so are you with me? I know this is a long thread but I want to be as specific as possible. Please let me know what my options are. Feel free to email me at:
jayjay06@aol.com
Thanks Again,
Jason
RE: Solidworks Macro - BOM
I don't think that the SolidWorks API supports the insertion of a BOM at this time. Here is my best suggestion.
- create the drawings manually
- pick a view and Insert > BOM
- run this program to extract the contents
- delete the BOM from the drawing manually
Put this code in a SolidWorks macro file. Make sure that you add the Microsoft Excel Object Library to the Project references (Tools > References)
I assume that you are using the latest version of SolidWorks!
NOTE: If you are linking dimensions in your BOM you are going to have trouble. I have developed a routine to convert the linked dimensions.
Option Explicit
Dim swApp As Object
Dim swPart As Object
Dim swView As Object
Dim swBOM As Object
Public Type BOM_Data
Rev As String
Item As String
Qty As String
Desc As String
Wgt As String
Matl As String
Spec1 As String
Spec2 As String
PartNo As String
End Type
Public LineItem() As BOM_Data
Sub main()
Dim ret As Variant, sTmp As String
Dim i As Integer, iItems As Integer
Dim sMisc(7) As String
Dim BomViewsModelName As String
Dim dataArray As Variant
'Attach to SolidWorks
On Error Resume Next
Set swApp = GetObject(, "SldWorks.Application")
If Err.Number <> 0 Then
MsgBox "Can not Find SldWorks.Application" & vbCrLf & _
"ErrNo: " & Err.Number & " ErrMsg: " & Err.Description _
, vbOKOnly, "Error in ExportBOM()"
Err.Clear
Exit Sub
End If
On Error GoTo 0
'Comment this out to debug
On Error GoTo ErrorEB
Set swPart = swApp.ActiveDoc
Set swView = swPart.GetFirstView 'This is actually the template
'Get the BOM
Set swBOM = swView.GetBomTable
'Find the BOM - must find the view that contains the BOM
Do While swBOM Is Nothing And Not swView Is Nothing
Set swView = swView.GetNextView
Set swBOM = swView.GetBomTable
Loop
If swBOM Is Nothing Then
MsgBox "Can NOT find a BOM on the current drawing!"
Exit Sub
End If
'Attach to the BOM
ret = swBOM.Attach2
If ret = False Then
MsgBox "Error Attaching to BOM"
Exit Sub
End If
'Put the BOM table in an array
iItems = swBOM.GetRowCount - 1
ReDim LineItem(iItems)
For i = 1 To iItems
LineItem(i).Rev = swBOM.GetEntryText(i, 0)
LineItem(i).Item = swBOM.GetEntryText(i, 1)
LineItem(i).Qty = swBOM.GetEntryText(i, 2)
LineItem(i).Desc = swBOM.GetEntryText(i, 3)
LineItem(i).Wgt = swBOM.GetEntryText(i, 4)
LineItem(i).Matl = swBOM.GetEntryText(i, 5)
LineItem(i).Spec1 = swBOM.GetEntryText(i, 6)
LineItem(i).Spec2 = swBOM.GetEntryText(i, 7)
LineItem(i).PartNo = swBOM.GetEntryText(i, 8)
Next i
'Detach from the BOM
swBOM.Detach
'Convert Linked Dimensions Here - Skipped
'Open the Excel File
'Use Workbooks.Open to open an existing file
Workbooks.Add
Workbooks.Application.Visible = True
'Write out the data
For i = 1 To iItems
Range("A" & i).FormulaR1C1 = LineItem(i).Rev
Range("B" & i).FormulaR1C1 = LineItem(i).Item
Range("C" & i).FormulaR1C1 = LineItem(i).Qty
Range("D" & i).FormulaR1C1 = LineItem(i).Desc
Range("E" & i).FormulaR1C1 = LineItem(i).Wgt
Range("F" & i).FormulaR1C1 = LineItem(i).Matl
Range("G" & i).FormulaR1C1 = LineItem(i).Spec1 & Space(2) & LineItem(i).Spec2
Range("H" & i).FormulaR1C1 = LineItem(i).PartNo
Next i
Range("A4").Select
'***************************************
'Rearrange and Print the Excel File Here
'***************************************
MsgBox "BOM Exported Successfully!"
GoTo CleanUp
ErrorEB:
MsgBox "Error in ExportBOM() Utility" & vbCrLf & Err.Description
Err.Clear
CleanUp:
Set swApp = Nothing
Set swPart = Nothing
Set swView = Nothing
Set swBOM = Nothing
End Sub
Hope this helps!
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Solidworks Macro - BOM
The assembly file has a title and a description that I have set that I would like to be in the heading of the printed out BOM. Currently I set up a form at the beginning of the program and I enter them manually. Ultamitely I would like to pass that title and description straight from the assembly file to the BOM, however the BOM only has the drawing file of the assembly open when running. If I had to I could give the drawing file a title and description (by the way these I set in the custom properties) and then extract those. So first of all how can I extract the custom properties (and I suppose I want to assign them to a variable in the script to be used later by a different subroutine) and second can I extract the title and description of and assembly file from its drawing file. By the way the title and description are passed to the drawing title block using the linked property command of a Note.
Thanks Again, and hopefully this will be it!!!!!
Jason
RE: Solidworks Macro - BOM
Public Sub GetTitleBlockValues()
Dim View As Object
Dim note
Dim sNoteName As String
Dim sTitle As String, sDescription As String
'(WITH THE DRAWING FILE OPEN)
Part.EditTemplate
'This is actually the template
Set View = Part.GetFirstView
'Get First Note Object
Set note = View.GetFirstNote
Do While Not note Is Nothing
sNoteName = note.GetName
'Get data if we have the right note
If sNoteName Like "NoteNameOfTheDescription" Then
sDescription = note.GetText
ElseIf sNoteName Like "NoteNameOfTheTitle" Then
sTitle = note.GetText
End If
Set note = note.GetNext
Loop
Part.EditSheet
End Sub
Let me know if this helps!
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Solidworks Macro - BOM
Thanks,
Jason
RE: Solidworks Macro - BOM
Option Explicit
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
' ChangeNoteName
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
Dim swApp As Object
Dim Model As Object
Dim SelMgr As Object
Dim note As Object
Dim annotation As Object
Dim sName As String
Dim sMsg As String
Dim res
Const swSelNOTES = 15
Const swDocDRAWING = 3
Sub Main()
Set swApp = CreateObject("SldWorks.Application")
Set Model = swApp.ActiveDoc
'Verify a Drawing is Opened
If (Model Is Nothing) Or (Model.GetType <> swDocDRAWING) Then
MsgBox "You Must Have a Drawing Opened"
Exit Sub
End If
'Verify a Note is Selected
Set SelMgr = Model.SelectionManager
If (SelMgr.GetSelectedObjectCount = 0) Then
MsgBox "You Need to Select a Note First"
Exit Sub
End If
If (SelMgr.GetSelectedObjectCount > 1) Then
MsgBox "Select Only One Note"
Exit Sub
End If
'NOTE
ElseIf (SelMgr.GetSelectedObjectType(1) = swSelNOTES) Then
Set note = SelMgr.GetSelectedObject2(1)
sMsg = "Change Name From " & note.GetName & " To What?"
sName = InputBox(sMsg, "Change Note Name", "")
If Len(sName) > 0 Then
res = note.SetName(sName)
'Verify the Name Was Changed Properly
If (note.GetName <> sName) Then
MsgBox "Error Changing Name!"
Else
MsgBox "Note Name Was Changed To " & sName
End If
Else
MsgBox "ChangeNoteName Cancelled"
End If
'INVALID
Else
MsgBox "The Selected Item is Not a Valid Note"
End If
End Sub
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: Solidworks Macro - BOM
By the way you told me before that SW API does not support BOM insertion and I think you are correct. I found out a way to do it using send keys:
http://www.nhcad.com/html/sendkeys.asp
So now I can do everything except delete it (although I am having a problem waiting for sendkeys to finish because I need to do a check for to see if the BOM has finished being made.) In the code you sent for attaching to the BOM there was a line "ret = swBOM.attach2" is there something other than attach2 I can use (like select) so that it will only select the BOM instead of attaching to it. Then I could easily delete it.
RE: Solidworks Macro - BOM
No, the BOM table does not have a Select method. If you fire up the SolidWorks API Help screen, there is a SolidWorks Object section on the Contents tab. You can use this to browse all of the methods and properties available to specific objects.
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.