Editing a BOM Macro

Out of category
1

Hello

I'm interested in this macro that allows you to create an excel BOM from an assembly in one click, but I'd like to modify it so that it uses an excel template in which I have set up some things (macro that allows you to modify the properties in SW, list of drop-down choices)

Here is the code for the original macro:

Option Explicit

Dim swApp                   As SldWorks.SldWorks
Dim swModel                 As SldWorks.AssemblyDoc
Dim swModelDocExt           As SldWorks.ModelDocExtension
Dim swBOMAnnotation         As SldWorks.BomTableAnnotation
Dim swBOMFeature            As SldWorks.BomFeature
Dim BomType                 As Long
Dim Configuration           As String
Dim TemplateName            As String
Dim swConfig                As SldWorks.Configuration
Dim xlApp                   As Excel.Application
Dim wbk                     As Excel.Workbook
Dim sht                     As Excel.Worksheet
Dim NumCol                  As Long
Dim NumRow                  As Long
Dim I                       As Long
Dim J                       As Long
Dim chemin                  As String

Sub xls()
    ' On ouvre un nouvel objet Excel
    Set xlApp = New Excel.Application
    
    ' On rend Excel visible avec un nouveau classeur
    With xlApp
        .Visible = True
        Set wbk = .Workbooks.Add
        Set sht = wbk.ActiveSheet
    End With
    
    ' On se raccroche à l'application SW
    Set swApp = Application.SldWorks

    ' On se raccroche au document SW ouvert
    Set swModel = swApp.ActiveDoc
    Set swModelDocExt = swModel.Extension
    
    ' On définit le modèle de nomenclature
    TemplateName = "Z:\BIBLIO\BIBLIO_LOGICIEL\BIBLIO_SOLIDWORKS\SOLIDWORKS_FORMALISME_CLEO\Modèle de nomenclature\9999_Nomenclature_SW.sldbomtbt"
    BomType = swBomType_Indented
    
    ' On récupère la configuration active
    Set swConfig = swModel.GetActiveConfiguration
    Configuration = swConfig.Name
    
    ' On insère la nomenclature dans le 3D
    Set swBOMAnnotation = swModelDocExt.InsertBomTable3(TemplateName, 0, 1, BomType, Configuration, False, swNumberingType_Detailed, True)
    Set swBOMFeature = swBOMAnnotation.BomFeature
    
    ' On reconstruit le 3D
    swModel.ForceRebuild3 True
    
    ' On remplit les cellules de Excel à partir des valeurs de la nomenclature
    NumCol = swBOMAnnotation.ColumnCount
    NumRow = swBOMAnnotation.RowCount
    For I = 0 To NumRow
        For J = 0 To NumCol
            sht.Cells(I + 1, J + 1).Value = swBOMAnnotation.Text(I, J)
        Next J
    Next I
    
    ' On définit le nom complet du fichier Excel
    chemin = Environ("USERPROFILE") & "\Desktop\" & "export" & ".xls"
    
    ' On sauvegarde le fichier Excel puis on ferme Excel
    With xlApp
        wbk.SaveAs chemin
        wbk.Close
        .Quit
    End With
 
End Sub

 

I imagine that the section to be modified is the following,

Dim wbk                     As Excel.Workbook

 

or

 

' On ouvre un nouvel objet Excel
    Set xlApp = New Excel.Application
    
    ' On rend Excel visible avec un nouveau classeur
    With xlApp
        .Visible = True
        Set wbk = .Workbooks.Add
        Set sht = wbk.ActiveSheet
    End With

 

but I don't know how to indicate the path of my XLS model

Thank you for your help

 

 

 

 

2

In a second step, I would also like to make other changes, such as

  1. - Record the BOM in the same place as the assembly from which it is derived
  2. - write the nomenclature two times: in the work tab, but also in a second tab, as a backup
  3. - include a thumbnail of the part in front of each line (possible from 2019 only? I'm working in 2018 at the moment) (I'll open a specific position later, I already have a codebase that I can't get to work)
  4. export all the properties of all configurations automatically (post dedicated to this question here)
  5. - also export in this excel table the SW property which manages the exclusion mode promotion of components... (I'll open a specific position later)

As far as possible, of course...

Thank you in advance for your suggestions

1 Like
3

Hello

To the question "how do I indicate the path of my xls model" you have to replace the line

Set wbk = . Workbooks.Add

by

Set wbk = . Workbooks.Open("C:\..........\my modèle.xls")

To the question "save the bill of materials in the same place as the assembly from which it comes" we must replace the line

path = About("USERPROFILE") & "\Desktop\" & "export" & ".xls"

by

Dim ExcelName As String
ExcelName = swModel.GetPathName()
ExcelName = Left(ExcelName, InStrRev(ExcelName, "\"))
path = ExcelName & "export" & ".xls"

To the question "write the nomenclature two times: in the work tab, but also in a second tab, as a backup" you can for example add a line

Dim shtSave As Excel.Worksheet

After the line

Dim sht As Excel.Worksheet

then add the line

Set shtSave = wbk. Worksheets(2)

After the line

Set sht = wbk. ActiveSheet

and finally add the line

shtSave.Cells(I + 1, J + 1). Value = swBOMAnnotation.Text(I, J)

After the line

Sht. Cells(I + 1, J + 1). Value = swBOMAnnotation.Text(I, J)

For questions 3, 4 and 5: subsequent or dedicated discussion thread ...

Kind regards

 

2 Likes