Solidworks Macro - BOM 1

[jjellin]

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

 

[dsi]

1. You could possibly put the Excel macro in a sub called Autpen so it will run upon opening the file. If you have macro virus protection enabled, you will have to 'Enable Macros' upon opening. Sounds like you have the format manipulation and printing covered.

2. Use the Quit method to close the Excel application. You could toss this in at the end of your Autpen 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.

 

[SBaugh]

Jason,

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

 

[jjellin]

Heres a new problem I'm having. When I record the macro to insert the BOM and I run it it gives me a "Propert not Found" for the line "part.insertBOM". I checked to make sure a drawing view was selected, but no matter what I do I cannot initialize the Solidworks BOM routine with a Solidworks macro. Any ideas?

 

[jjellin]

That was supposed to say "Property not found".

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 !!!

 

[dsi]

Let me see if I understand your needs:

- 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.

 

[jjellin]

I believe were on the same page, but let me clarify a few things.

- 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

 

[dsi]

jj:

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 &quot;Can not Find SldWorks.Application&quot; & vbCrLf & _
               &quot;ErrNo: &quot; & Err.Number & &quot;  ErrMsg: &quot; & Err.Description _
               , vbOKOnly, &quot;Error in ExportBOM()&quot;
        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 &quot;Can NOT find a BOM on the current drawing!&quot;
        Exit Sub
    End If
    'Attach to the BOM
    ret = swBOM.Attach2
    If ret = False Then
        MsgBox &quot;Error Attaching to BOM&quot;
        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(&quot;A&quot; & i).FormulaR1C1 = LineItem(i).Rev
        Range(&quot;B&quot; & i).FormulaR1C1 = LineItem(i).Item
        Range(&quot;C&quot; & i).FormulaR1C1 = LineItem(i).Qty
        Range(&quot;D&quot; & i).FormulaR1C1 = LineItem(i).Desc
        Range(&quot;E&quot; & i).FormulaR1C1 = LineItem(i).Wgt
        Range(&quot;F&quot; & i).FormulaR1C1 = LineItem(i).Matl
        Range(&quot;G&quot; & i).FormulaR1C1 = LineItem(i).Spec1 & Space(2) & LineItem(i).Spec2
        Range(&quot;H&quot; & i).FormulaR1C1 = LineItem(i).PartNo
    Next i
    Range(&quot;A4&quot;).Select
    
    '***************************************
    'Rearrange and Print the Excel File Here
    '***************************************
    
    MsgBox &quot;BOM Exported Successfully!&quot;
    GoTo CleanUp
ErrorEB:
    MsgBox &quot;Error in ExportBOM() Utility&quot; & 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.

 

[jjellin]

Ok, everything is working and it runs great. Now I have one small step left.

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

 

[dsi]

Since you have the drawing title block updated, just use their values. This code will traverse through the template notes and find the one's you need. To find the name of a particular note (needed in the program), record a macro while you delete the note. Save the macro and hit the UNDO button to retrieve the note. Then, use the name to the left of the @ symbol recorded by SolidWorks. I have a routine that I use to change the name of notes, weld symbols ... Since SolidWorks defaults to a name like "DetailItem41", I can change the name to "DwgTitle".

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.

 

[jjellin]

Ok I'm back with another question. I think this is an easy one. I've just now how a chance to play with the code you gave in the last thread. I can make it work on a drawing that I specifically find the "DetailItem#@". But those numbers change from drawing to drawing so I can only reference that same drawing. If I am reading the above correctly I think you said that I can change the "DetailItem#2" to be specific like "Description@" or "Title2". But I can't for the life of me figure out how to do that. I am trying to do it with the drawing templates and the only way I can modify is by right clicking and selecting properties on the note. In the text box I have something like $PRPSHEET:"Description", which will reference the assembly or part file. Is that where I can make this change?

Thanks,
Jason

 

[dsi]

There is no function in SolidWorks to do this, you need a custom program. Here is my macro for changing the object name of a note. Just copy this into a macro file and assign it to one of the macro buttons. Then, select a note on the drawing and hit the button. You will be prompted to enter a new name for the item. One thing to take note of is the drawing view in which the note was entered. If this is just a standard drawing note, it gets assigned to the active view. You will need to make sure that you maintain consistancy in multiple drawings.

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(&quot;SldWorks.Application&quot;)
    Set Model = swApp.ActiveDoc
    'Verify a Drawing is Opened
    If (Model Is Nothing) Or (Model.GetType <> swDocDRAWING) Then
            MsgBox &quot;You Must Have a Drawing Opened&quot;
            Exit Sub
    End If
    'Verify a Note is Selected
    Set SelMgr = Model.SelectionManager
    If (SelMgr.GetSelectedObjectCount = 0) Then
            MsgBox &quot;You Need to Select a Note First&quot;
            Exit Sub
    End If
    If (SelMgr.GetSelectedObjectCount > 1) Then
            MsgBox &quot;Select Only One Note&quot;
            Exit Sub
    End If
    'NOTE
    ElseIf (SelMgr.GetSelectedObjectType(1) = swSelNOTES) Then
        Set note = SelMgr.GetSelectedObject2(1)
        sMsg = &quot;Change Name From &quot; & note.GetName & &quot; To What?&quot;
        sName = InputBox(sMsg, &quot;Change Note Name&quot;, &quot;&quot;)
        If Len(sName) > 0 Then
            res = note.SetName(sName)
            'Verify the Name Was Changed Properly
            If (note.GetName <> sName) Then
                MsgBox &quot;Error Changing Name!&quot;
            Else
                MsgBox &quot;Note Name Was Changed To &quot; & sName
            End If
        Else
            MsgBox &quot;ChangeNoteName Cancelled&quot;
        End If
    'INVALID
    Else
        MsgBox &quot;The Selected Item is Not a Valid Note&quot;
    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.

 

[jjellin]

Thanks, this all works great.

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.

 

[dsi]

That's some good info on the SendKeys method. I did not even think about going that route.

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.