10 Replies Latest reply on Jan 9, 2015 3:20 AM by Markku Lehtola

    Excel Based BOM macro to save file

    Mark Stillman

      HI all,

      we have just stared to use the Excel Based BOM function in Solidworks 2014. This solves a load of issues for us and is actually useful.

      I was wondering how to create a macro to save the bom to a specific folder on out network. Ive tried running the macro recorder but it doesn't make any sense

       

      Dim swApp As Object

      Dim Part As Object
      Dim boolstatus As Boolean
      Dim longstatus As Long, longwarnings As Long

      Sub main()

      Set swApp = _
      Application.SldWorks

      Set Part = swApp.ActiveDoc
      boolstatus = Part.Extension.SelectByID2("@Drawing View1", "BOM", 9.80850184849844E-02, 0.344196913033501, 0, False, 0, Nothing, 0)
      End Sub

      I would like to just click a macro and it save to my e:\documents\BOM\ folder with the document name and the version number if possible.

      I would appreciate some help with this please.

      Thanks in advance

      Mark

        • Re: Excel Based BOM macro to save file
          Keith Rice

          Mark,

           

          How are you saving an Excel-based BOM via the GUI? I don't see any option to save the BOM when I right click on it.

           

          Unfortunately, I don't think this is possible via the API. The only way to get the API object for an Excel-Based BOM table is through IView::GetBomTable, but this API call is deprecated. I tested it in 2013 and it didn't work. I'm not sure why no additional API calls were created to work with Excel-based BOMs. You could email SolidWorks API support and request this functionality if you wanted. (Not going to happen, since this functionality is deprecated. See Josh Brady's comment below.

           

          Keith

          SolidWorks API Video Tutorials

            • Re: Excel Based BOM macro to save file
              Mark Stillman

              hi Keith,

              thanks for the reply

              you have to click to highlight the BOM and then go File> Save as , the dialog comes up automatically with xlsx.

              excel save.png

              it seems like the whole thing is a half implemented piece.

              Your supposed to be able to add summary info into the bom and I've followed the vague instructions to try and add the document name to the excel sheet but it doesn't do anything.

              you can link the excel sheet to the file but its not much use once you put into the pdm vault.

              contacted solid solutions but they didn't have much of an idea on what to do with it either.

              Mark

              • Re: Excel Based BOM macro to save file
                Daniel Andersson

                Keith,

                 

                Just a thought. Wouldn't it possible to access the BOM with ITableAnnotation and then process each cell by code and write it to an excel file (using Excel API)?

                 

                If that works the only thing is the formatting etc that needs to be handled as well... which makes the code to be a lot more compared to if there would have API functions to just export it.

              • Re: Excel Based BOM macro to save file
                Bill Florac

                This can be done but it can be tricky.  You have to manually get the assembly, get the current configuration (or the one you want) and drill down to all it's parts. Along the way, you need to check to make sure that the part is not "excluded from the BOM" and a few other flags (virtual, hidden, suppressed, envelope..) to make sure that you want this part. It gets even more complicated if you have cut lists and such. You may want or need to drill down to the next level too. All this is done for you when you put the BOM on the drawing and save it as an excel spread sheet. I do all this with the Solidworks Document Management API but I'm sure it can be done with the Solidworks API too.

                 

                Get Document

                     Get Document - Config

                          Get Config - Components

                               Check Component

                                    Add to Excel

                • Re: Excel Based BOM macro to save file
                  Adam Hoffman

                  Try having a look at this thread:

                   

                  Macro: Generate BOM, Export to Excel

                   

                  This is the only way I have found to save a BOM out as an Excel file with the API. If you need it in a specific format, then you will have to use the other suggestions and traverse the assembly and create an Excel workbook piece by piece with a lengthy program. Hope this helps.

                  • Re: Excel Based BOM macro to save file
                    Mark Stillman

                    Hi sorry for not getting back sooner been on Christmas holidays +5.

                    Thinks there is some confusion, maybe my fault with what im describing.

                    Solidworks 2014 has an Excel Based Bill of Materials which makes a link to a excel spread sheet.

                    selection.jpg

                    The only way you can save the excel sheet is to click on the bom then select Save As and then save it as an Excel file

                    selection2.jpg

                    I have a macro which is a hacked about version of Lenny Kikstra's Document Export, which saves all the file formats I need into a shared directory on our net work, JPG, PDF, DWG,DXF etc and SAT models.

                    Anyway im trying to add the bom save section into it.

                    Ive tried recording a macro of selecting the BOM then the save as but it doesn't seem to come up.

                     

                    Dim swApp As Object

                    Dim Part As Object
                    Dim boolstatus As Boolean
                    Dim longstatus As Long, longwarnings As Long

                    ______________________________________________

                    Sub main()

                    Set swApp = _
                    Application.SldWorks

                    Set Part = swApp.ActiveDoc
                    boolstatus = Part.Extension.SelectByID2("@Drawing View1", "BOM", 0.123688849057995, 0.327034678124557, 0, False, 0, Nothing, 0)
                    End Sub

                     

                    this seems to only select the bom and nothing else.

                    if I go via Feature manager and right click on the Bill of materials! icon and do edit I get this


                    Dim swApp As Object

                    Dim Part As Object
                    Dim boolstatus As Boolean
                    Dim longstatus As Long, longwarnings As Long

                    _______________________________________________

                    Sub main()

                    Set swApp = _
                    Application.SldWorks

                    Set Part = swApp.ActiveDoc
                    boolstatus = Part.Extension.SelectByID2("@Drawing View1", "BOM", 0, 0, 0, False, 0, Nothing, 0)
                    boolstatus = Part.Extension.SelectByID2("@Drawing View1", "BOM", 0, 0, 0, False, 0, Nothing, 0)
                    Dim myModelView As Object
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Part.ClearSelection2 True
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    Set myModelView = Part.ActiveView
                    myModelView.FrameState = swWindowState_e.swWindowMaximized
                    End Sub

                     

                     

                    this opens the bom in this window

                    selection3.jpg

                    which I think is an excel screen via active X ( not to good with this stuff hence post)

                    sorry for the long post

                    Mark

                      • Re: Excel Based BOM macro to save file
                        Josh Brady

                        What is it about Excel-based BOMs that you like?  Can the functionality be done with a regular SolidWorks BOM?

                         

                        The reason I ask is that Excel-based BOMs have been obsolete and pretty much unsupported since like SW2005 or something. They haven't been removed from the software, but haven't been developed either.  Programmatically, there's a whole lot more that can be done with SolidWorks BOMs, and they can easily be saved as Excel files.  I would highly recommend not using Excel-based BOMs.  With every release of SW I keep expecting them to be gone.

                      • Re: Excel Based BOM macro to save file
                        Markku Lehtola

                        Hi,

                         

                        this saves SW-bom as txt file, it's easy to add filename stuff for it

                         

                         

                         

                        Sub main()

                        Set swApp = Application.SldWorks

                        Set DrawDoc = swApp.ActiveDoc

                        Set View = DrawDoc.GetFirstView

                        Set TableAnn = View.GetFirstTableAnnotation

                        Do While Not TableAnn Is Nothing

                            If TableAnn.Type = 2 Then '2 is the bom table type

                                Set BOM = TableAnn

                                Exit Do

                            End If

                            Set TableAnn = TableAnn.GetNext

                        Loop

                        If BOM Is Nothing Then

                            MsgBox "BOM was not found"

                        Else

                            Dim Txt As String

                            Dim Row As Long

                            Dim Col As Long

                            Dim fso As New FileSystemObject

                            Dim ts As TextStream

                               

                            Set ts = fso.CreateTextFile("C:\test.txt", True)

                            For Row = 0 To BOM.RowCount - 1

                            Txt = Empty

                                For Col = 0 To BOM.ColumnCount - 1

                                   Txt = Txt & BOM.Text(Row, Col) & ";"

                                Next Col

                              

                                ts.writeline (Txt)

                            Next Row

                          

                            ts.Close

                        End If

                        MsgBox "Done!"

                        End Sub

                         

                        references:

                        refs.jpg

                         

                        br

                        Markku

                        www.sloworks.fi