Macro Export Bill of Materials to Excel

2D plans Labelling system
1

Hi all

I'm a beginner in macro, we use Solidworks at the BE and I want to automate some tasks. Currently, I export the bills of materials to Excel since the drawing and I integrate them into our ERP. I'd like a macro to export the nomenclature with the same name as the drawing and in the same folder.

I tried via Chatgpt impossible.

I've done some research but I can't find what I'm looking for, does anyone have a macro similar to my request in their toolbox?

2

Check out this topic:
https://forum.mycad.visiativ.com/t/export-nomenclature-vers-excel-avec-une-macro-vba/104588
Or:
https://forum.mycad.visiativ.com/t/export-nomenclature-vers-excel-avec-une-macro-vba/106042
And to finish this one:
https://forum.mycad.visiativ.com/t/exportation-vba-nomenclature-solidworks-vers-excel/106397
Edit: with the search of the forum the topics are also findable and without the need for a chat! :wink:

3 Likes
3

Thank you for your quick answer, I had seen these topics but the first topic it integrates a nomenclature, exports it and deletes it. I would like the macro to select the nomenclature that is in the drawing and export it to xlsx.

4

Hello @Jérémy_Guillon ;
You should be more precise in your requests...

  • The solidworks version
  • The Excel version
  • the name of the , or sheets of drawings
  • The name and type of Nomenclatures...
  • the destination location (dynamic or static?)…

(when in doubt, the macro below exports all the BOMs found in a drawing to a *.xlsx file ( C:\Temp\Nomenclature_MiseEnPlan.xlsx)

Sub ExportNomenclatureMiseEnPlan()
    Dim swApp As Object
    Dim swDraw As Object
    Dim swTable As Object
    Dim swSheet As Object
    Dim swView As Object
    Dim swTableAnnotation As Object
    Dim excelApp As Object
    Dim wb As Object
    Dim ws As Object
    Dim i As Long, j As Long
    Dim rowCount As Long, colCount As Long
    
    ' Initialiser SolidWorks
    Set swApp = GetObject(, "SldWorks.Application")
    Set swDraw = swApp.ActiveDoc
    
    If swDraw Is Nothing Then
        MsgBox "Aucune mise en plan active."
        Exit Sub
    End If
    
    ' Créer une instance Excel
    Set excelApp = CreateObject("Excel.Application")
    excelApp.Visible = True
    Set wb = excelApp.Workbooks.Add
    Set ws = wb.Sheets(1)
    
    ' Localiser la table BOM dans la mise en plan
    ' Supposons qu'il y ait une seule table BOM
    For Each swTableAnnotation In swDraw.GetTableAnnotations
        If swTableAnnotation.Type = 0 Then ' 0 pour BOM
            Set swTable = swTableAnnotation
            Exit For
        End If
    Next
    
    If swTable Is Nothing Then
        MsgBox "Aucune table de nomenclature trouvée."
        Exit Sub
    End If
    
    ' Récupérer le nombre de lignes et colonnes
    rowCount = swTable.RowCount
    colCount = swTable.ColumnCount
    
    ' Parcourir la table et exporter
    For i = 0 To rowCount - 1
        For j = 0 To colCount - 1
            ws.Cells(i + 1, j + 1).Value = swTable.Text(i, j)
        Next j
    Next i
    
    ' Enregistrer le fichier Excel
    Dim savePath As String
    savePath = "C:\Temp\Nomenclature_MiseEnPlan.xlsx"
    wb.SaveAs savePath
    
    ' Nettoyer
    wb.Close
    excelApp.Quit
    MsgBox "Export terminé : " & savePath
End Sub

Personal note:
You'll never learn programming if you ask an AI directly. Keep in mind that it is 10 times more difficult to read a code than to write it.

1 Like
5

Hello Maclane,

It's true that I'm not too used to going on forums.
We are in SW 2023 SP5.0, for excel version 16.0.
Normally the sheet is called PLAN and the BOM is called BOM1
For the location and name of the file, I would like the same name as the sw file and it is saved in the source folder of the drawing.

I tested the proposed macro and I have a debug at this level:

For Each swTableAnnotation In swDraw.GetTableAnnotations

I know that's the problem when I've done some testing.

I understand for AI, I would love to know how to code and learn but I am the only one who wants to automate tasks within the offices via Macro SW or simply by excel formulas. (the others prefer to type and repeat tasks and make mistakes) the person I started with and who showed me the VBA is gone and I'm the only one in the BE,... So I try to do it on my own when I have a little time. And I admit that it's easy as soon as you get stuck on a formula or other... :wink:

6

So much for me, sorry. :upside_down_face:
The GetTableAnnotations method does not exist directly on the " *.swdrum" files, but only on the MEP  sheets.
You must first retrieve each sheet via GetSheets, then on each sheet, use GetTableAnnotations to access the tables
example:
https://help.solidworks.com/2022/english/api/sldworksapi/Get_Table_Annotation_and_Contents_Example_VB.htm
…
But I don't really have time to create the macro... That said, with the examples of @sbadenis and a little practice, it shouldn't be insurmountable.