8 Replies Latest reply on Dec 22, 2014 1:42 PM by Paul Flores

    Programmatically saving BOM to Excel

    Mike Campbell

      I've surfed the forum for solutions. Only one potentially fits my need if only I could get it to work. Note that the BOM does get created but does not get saved out. There are no errors, just no file.

      Option Explicit

      'Preconditions: Assembly file is active and includes components

      'Postconditions: A file is saved which contains BOM info

       

      Dim swApp           As SldWorks.SldWorks

      Dim swModel         As SldWorks.ModelDoc2

      Dim swBOMTable      As SldWorks.BomTableAnnotation

      Dim swTable         As SldWorks.TableAnnotation

      Dim swAnn           As SldWorks.Annotation

      Dim strBOMTemplate  As String

      Dim strOutputPath   As String

       

      Sub main()

       

      strBOMTemplate = "D:\Users\204065774\Documents\SolidWorks\Mike's templates\standard BOM.sldbomtbt"

       

      Set swApp = Application.SldWorks

      Set swModel = swApp.ActiveDoc

      Set swBOMTable = swModel.Extension.InsertBomTable(strBOMTemplate, 0, 0, 3, "default") 'BOM is created in the assembly

       

      Set swTable = swBOMTable

       

      strOutputPath = "c:\" & "BOMTable_" & Left(swModel.GetTitle(), Len(swModel.GetTitle) - 7) & ".xls"

      Debug.Print strOutputPath

       

      swTable.SaveAsText strOutputPath, vbTab 'Nothing happens here. No errors, no file, nothing.

       

      End Sub

        • Re: Programmatically saving BOM to Excel
          Jason Kerns

          Have you tried saving to that location manually?

          I tried to save to my "C:\" location but do not have permission.

          • Re: Programmatically saving BOM to Excel
            Roland Schwarz

            Here's a macro that scans a BOM and copies it to the clipboard in a format that is Excel-friendly.

             

            BOM to Clipboard macro

            • Re: Programmatically saving BOM to Excel
              Mike Campbell

              Here's a working example of exporting the BOM from the assembly to Excel.

              Option Explicit

              'Preconditions: Assembly file is active and includes components.

              'Postconditions: An Excel file is saved which contains BOM info for a configuration named "default". _

              The file is saved in D:\SWIM_CACHE. Each time the macro is executed the new file overwrites the old one. _

              The file name follows the pattern "BOMTable_(assembly file name).xls"

               

              'Add Microsoft Excel to references

               

              Dim swApp           As SldWorks.SldWorks

              Dim swModel         As SldWorks.ModelDoc2

              Dim swBOMTable      As SldWorks.BomTableAnnotation

              Dim swTable         As SldWorks.TableAnnotation

              Dim swAnn           As SldWorks.Annotation

              Dim strBOMTemplate  As String 'Path and name of SolidWorks BOM template

              Dim strFilename     As String 'Path and name of Excel file (.xls)

              Dim strTemp         As String 'Path and name of text file (.txt)

               

              Sub main()

               

              'The SolidWorks BOM template must reside in a location common to all users

              strBOMTemplate = "\\tnwp09662\Solidworks Options Files\Read Only\BOM Templates\BOM-standard.sldbomtbt"

               

              Set swApp = Application.SldWorks

              Set swModel = swApp.ActiveDoc

               

              'BOM is created in the assembly for a configurations named "default"

              Set swBOMTable = swModel.Extension.InsertBomTable(strBOMTemplate, 0, 0, 3, "default")

               

              Set swTable = swBOMTable

               

              strFilename = "D:\SWIM_CACHE\" & "BOMTable_" & Left(swModel.GetTitle(), Len(swModel.GetTitle) - 7) & ".xls"

              strTemp = "C:\Temp\Temp.xls"

               

              'Save text file (.txt)

              swTable.SaveAsText strTemp, "" 'vbTab is default delimiter for text file (.txt)

               

              OpenExcelFile strTemp, strFilename 'Call sub

               

              End Sub

               

              'Postcondion: Text file (.txt) is saved as Excel file (.xls)

               

              Sub OpenExcelFile(Temp As String, Filename As String)

               

              Dim ExcelBOM As Excel.Workbook

               

              Set ExcelBOM = Workbooks.Open(Temp) 'Open .TXT file in Excel

               

              If DoesFileExist(Filename) = True Then 'Call function

                  Kill Filename 'If .XLS file already exists, delete it.

              End If

               

              ExcelBOM.SaveAs Filename, 39, , , , , , 2 'Save .TXT file as .XLS

               

              ExcelBOM.Close

               

              End Sub

               

              'Postcondition: File is determined to exist or not.

              Function DoesFileExist(Filename As String) As Boolean

               

              If Len(Dir$(Filename)) > 0 Then

                  DoesFileExist = True

                  Else: DoesFileExist = False

              End If

              End Function

              • Re: Programmatically saving BOM to Excel
                Paul Flores

                Thanks Mike, this mostly works for me, but I have a few problems.

                 

                When I run this, I get an error message:

                     Compile error:

                     User-defined type not defined

                 

                The following line is highlighted in yellow on the debugger

                     Sub OpenExcelFile(Temp As String, Filename As String)

                 

                And the following is highlighted in blue

                    Dim ExcelBOM As Excel.Workbook

                 

                 

                That said, the Temp.xls file is created and I can open it from the folder as long as I hit the Yes button on this pop-up message:

                 

                The file you are trying to open, 'Temp.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

                 

                Any ideas on what's going on here?

                Using Solidworks 2014 and Excel 2010.

                • Re: Programmatically saving BOM to Excel
                  Mike Campbell

                  Notice "Add Microsoft Excel to references". Did you do that?

                  1 person found this helpful