How can we export a bill of materials to Excel in vba, because I work my material flow on Excel that I export to a flow software.
But I would like to export the bill of materials to an excel file or a csv which would allow me to import my bill of materials directly to my flow software.
Or even better to retrieve the information of the nomenclature without necessarily creating it, i.e. designation, length, width, thickness, number plus one or two properties of a form, to import it into Excel
This post follows episode 1 season 1 below. https://www.lynkoa.com/forum/solidworks/ins%C3%A9rer-mise-en-plan-avec-nomenclature-solidworks
Look especially at the latest posts, it's about creating an automatic function that SW doesn't offer to date, apparently. The ideal for @treza88would be to generate the excel file directly without creating the nomenclature either in the ASM or in the MEP which he does not use at this stage.
Launch a piece of software that will generate a bill of materials in excel without it appearing in the ASM or in the MEP. ;-) ;-)
Hello There are several ways to export the BOM by macros. For the drawing of a part assembly:
Option Explicit
Sub main()
Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2
Dim swDraw As SldWorks.DrawingDoc
Dim swActiveView As SldWorks.View
Dim swBOMTable As SldWorks.BomTableAnnotation
Dim Config As String
Dim TemplateName As String
Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swDraw = swModel
Set swActiveView = swDraw.GetFirstView
Set swActiveView = swActiveView.GetNextView
Config = swActiveView.ReferencedConfiguration
TemplateName = "C:\Program Files\SolidWorks Corp\SolidWorks\lang\english\bom-standard.sldbomtbt"
Set swBOMTable = swActiveView.InsertBomTable2(False, 0, 0.2, swBOMConfigurationAnchorType_e.swBOMConfigurationAnchor_TopLeft, swBomType_e.swBomType_Indented, Config, TemplateName)
WriteBom "C:\Temp\myBom.xls", swBOMTable
' delete table
Dim swBOMFeat As SldWorks.BomFeature
Set swBOMFeat = swBOMTable.BomFeature
Dim swFeat As SldWorks.Feature
Set swFeat = swBOMFeat.GetFeature
swFeat.Select2 False, -1
swModel.EditDelete
End Sub
Sub WriteBom(FilePath As String, table As SldWorks.TableAnnotation)
Dim N As Integer
N = FreeFile
Open FilePath For Output As #N
Dim i As Integer
Dim j As Integer
Dim myArray() As Variant
ReDim myArray(table.ColumnCount - 1)
For i = 0 To table.RowCount - 1
For j = 0 To table.ColumnCount - 1
myArray(j) = table.Text(i, j)
Next
Print #N, Join(myArray, vbTab)
Next
Close #N
End Sub
Option Explicit
Sub main()
Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2
Dim swDraw As SldWorks.DrawingDoc
Dim swActiveView As SldWorks.View
Dim swWeldTable As SldWorks.WeldmentCutListAnnotation
Dim Config As String
Dim TemplateName As String
Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swDraw = swModel
Set swActiveView = swDraw.GetFirstView
Set swActiveView = swActiveView.GetNextView
Config = swActiveView.ReferencedConfiguration
TemplateName = "C:\Program Files\SolidWorks Corp\SolidWorks\lang\english\cut list.sldwldtbt"
Set swWeldTable = swActiveView.InsertWeldmentTable(False, 0, 0.2, swBOMConfigurationAnchorType_e.swBOMConfigurationAnchor_TopLeft, Config, TemplateName)
WriteBom "C:\Temp\myBom.xls", swWeldTable
' delete table
Dim swWeldFeat As SldWorks.WeldmentCutListFeature
Set swWeldFeat = swWeldTable.WeldmentCutListFeature
Dim swFeat As SldWorks.Feature
Set swFeat = swWeldFeat.GetFeature
swFeat.Select2 False, -1
swModel.EditDelete
End Sub
Sub WriteBom(FilePath As String, table As SldWorks.TableAnnotation)
Dim N As Integer
N = FreeFile
Open FilePath For Output As #N
Dim i As Integer
Dim j As Integer
Dim myArray() As Variant
ReDim myArray(table.ColumnCount - 1)
For i = 0 To table.RowCount - 1
For j = 0 To table.ColumnCount - 1
myArray(j) = table.Text(i, j)
Next
Print #N, Join(myArray, vbTab)
Next
Close #N
End Sub
I found a snippet of code that works to create the table [ swtable = swDrawing.InsertTableAnnotation2(False, 0, 0, swBOMConfigurationAnchor_TopLeft, Templatetable, 4, 2)]
on the other hand in my excel file created it retrieves the formula of the property [$PRP:"SW-File Name"[ but not the value while on the table on SW is correct (see print, screen) and I can't modify the code to delete the general table created.
Thank you for your help.
Option Explicit
Sub main()
Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2
Dim swDraw As SldWorks.DrawingDoc
Dim swActiveView As SldWorks.View
Dim swWeldTable As SldWorks.WeldmentCutListAnnotation
Dim Config As String
Dim TemplateName As String
Set swApp = Application.SldWorks
Set swModel = swApp.ActiveDoc
Set swDraw = swModel
Set swActiveView = swDraw.GetFirstView
Set swActiveView = swActiveView.GetNextView
Config = swActiveView.ReferencedConfiguration
Templatetable = "C:\test article.sldtbt"
Set swTable = swDrawing.InsertTableAnnotation2(False, 0, 0, swBOMConfigurationAnchor_TopLeft, Templatetable, 4, 2)
WriteBom "C:\Temp\mytable.xls", swTable
' delete table
Dim swWeldFeat As SldWorks.WeldmentCutListFeature
Set swWeldFeat = swWeldTable.WeldmentCutListFeature
Dim swFeat As SldWorks.Feature
Set swFeat = swWeldFeat.GetFeature
swFeat.Select2 False, -1
swModel.EditDelete
End Sub
Sub WriteBom(FilePath As String, table As SldWorks.TableAnnotation)
Dim N As Integer
N = FreeFile
Open FilePath For Output As #N
Dim i As Integer
Dim j As Integer
Dim myArray() As Variant
ReDim myArray(table.ColumnCount - 1)
For i = 0 To table.RowCount - 1
For j = 0 To table.ColumnCount - 1
myArray(j) = table.Text(i, j)
Next
Print #N, Join(myArray, vbTab)
Next
Close #N
End Sub