How to Export Bill of Materials to Excel with Thumbnails Using a SOLIDWORKS Macro

Export Bill of Materials to Excel with thumbnails

If you’re using SOLIDWORKS 2018 or older, automating the export of your Bill of Materials (BOM) to Excel with thumbnails can be incredibly beneficial. This process not only saves time but also provides visual representation, improving clarity and communication with other team members. In this article, we’ll walk you through how to use a macro to automate the export of BOMs, including thumbnails, directly into Excel.

This tutorial will show you how to set up, run, and use the macro to export parts-only BOMs with thumbnail previews. Keep in mind that this macro is designed for smaller assemblies; for larger ones, consider alternatives like xlBOM with thumbnails or PDM2Excel, which can handle more complex assemblies.

Why Use a Macro for BOM Export in SOLIDWORKS?

Manually exporting a BOM and adding thumbnails in Excel can be tedious, especially in older versions of SOLIDWORKS. By using a SOLIDWORKS macro, you can streamline the process and avoid repetitive tasks, allowing you to:

  • Quickly export BOMs with thumbnails.
  • Automate Excel integration for better BOM management.
  • Improve team communication with visual components in BOMs.

Requirements:

  • SOLIDWORKS 2018 or older.
  • Microsoft Excel must be installed to run the macro successfully.
  • This macro works only for parts-only BOMs.

Step-by-Step Guide to Using the Macro

Follow these steps to set up and run the macro in SOLIDWORKS:

1. Open SOLIDWORKS and Load the Drawing

Ensure you have a drawing with a Bill of Materials (BOM) loaded and active in SOLIDWORKS. The macro will work only if a BOM feature is present in the drawing.

2. Create a New Macro

Go to Tools > Macro > New… and save the macro in a location of your choice.

3. Insert the VBA Code

After saving the macro, the Visual Basic for Applications (VBE) window will open. Here, clear the default text and copy-paste the VBA code provided below:

' All rights reserved to Blue Byte Systems Inc.
' Blue Byte Systems Inc. does not provide any warranties for macros.
' Pre-conditions: BOM pre-selected.
' Results: BOM created in Excel with thumbnail preview.
' Define the width and height of the thumbnail (in pixels)
Dim Width As Long ' in pixels
Dim Height As Long ' in pixels
Dim swApp As Object
Dim swModel As Object
Dim swTableAnnotation As Object
Dim exApp As Object
Dim exWorkbook As Object
Dim exWorkSheet As Object
Dim swSelectionManager As Object
' Enums for SolidWorks document types, Excel alignment, and table header positions
Public Enum swDocumentTypes_e
swDocDRAWING = 3
End Enum
Public Enum xlTextAlignment
xlCenter = -4108
End Enum
Public Enum swTableHeaderPosition_e
swTableHeader_Top = 1
swTableHeader_Bottom = 2
swTableHeader_None = 0
End Enum
Public Enum swSelectType_e
swSelBOMS = 97
End Enum
Sub Main()
' Set the thumbnail dimensions
Width = 21
Height = 60
' Get a pointer to the SolidWorks application
Set swApp = Application.SldWorks
' Get the active document
Set swModel = swApp.ActiveDoc
If swModel Is Nothing Then
swApp.SendMsgToUser "There is no active document"
End
End If
' Get the selection manager
Set swSelectionManager = swModel.SelectionManager
' Get the count of selected objects
Dim Count As Long
Count = swSelectionManager.GetSelectedObjectCount2(-1)
' If no BOM is selected, exit the macro
If Count = 0 Then
swApp.SendMsgToUser "You have not selected any bill of materials!"
Exit Sub
End If
' Traverse the selection and process all selected bill of materials
For i = 1 To Count
If swSelectionManager.GetSelectedObjectType3(i, -1) = SwConst.swSelectType_e.swSelANNOTATIONTABLES Then
Set swTableAnnotation = swSelectionManager.GetSelectedObject6(i, -1)
Dim Ret As String
Ret = SaveBOMInExcelWithThumbNail(swTableAnnotation)
If Ret = "" Then
Debug.Print "Success: " & swTableAnnotation.GetAnnotation.GetName
swApp.SendMsgToUser "The selected BOM has been exported with thumbnail preview to Excel."
Else
swApp.SendMsgToUser "Macro failed to export!"
End If
End If
Next i
End Sub
' Function to save BOM to Excel with thumbnail preview
Public Function SaveBOMInExcelWithThumbNail(ByRef swTableAnnotation As Object) As String
' Initialize Excel application
Set exApp = CreateObject("Excel.Application")
If exApp Is Nothing Then
SaveBOMInExcelWithThumbNail = "Unable to initialize the Excel application"
Exit Function
End If
exApp.Visible = True
' Create a new workbook and worksheet
Set exWorkbook = exApp.Workbooks.Add
Set exWorkSheet = exWorkbook.ActiveSheet
If exWorkSheet Is Nothing Then
SaveBOMInExcelWithThumbNail = "Unable to get the active sheet"
Exit Function
End If
' If the BOM has no rows, return an error
If swTableAnnotation.RowCount = 0 Then
SaveBOMInExcelWithThumbNail = "BOM has no rows!"
Exit Function
End If
Dim swBOMTableAnnotation As BomTableAnnotation
Set swBOMTableAnnotation = swTableAnnotation
' Set the column width
exWorkSheet.Columns(1).ColumnWidth = Width
' Set the header row index based on the BOM header position
Dim HeaderRowIndex As Long
Dim swHeaderIndex As Integer
swHeaderTable = swTableAnnotation.GetHeaderStyle
If swHeaderTable = swTableHeaderPosition_e.swTableHeader_Bottom Then
swHeaderIndex = swTableAnnotation.RowCount
Else
swHeaderIndex = 1
End If
' Traverse through each row in the BOM table
Skipper:
For i = 0 To swTableAnnotation.RowCount - 1
' Skip hidden rows
If swTableAnnotation.RowHidden(i) Then GoTo Skipper
' Add preview image
Dim swComponents As Variant
swComponents = swBOMTableAnnotation.GetComponents(i)
If Not IsEmpty(swComponents) Then
Dim swComponent As Object
Set swComponent2 = swComponents(0)
Dim swComponentModel As Object
Set swComponentModel = swComponent2.GetModelDoc2
If Not swComponentModel Is Nothing Then
swComponentModel.Visible = True
Dim imagePath As String
imagePath = Environ("TEMP") + "\tempBitmap.jpg"
swComponentModel.ViewZoomtofit2
Dim saveRet As Boolean
Dim er As Long
Dim wr As Long
saveRet = swComponentModel.Extension.SaveAs(imagePath, 0, 0, Nothing, er, wr)
If er + wr > 0 Then
SaveBOMInExcelWithThumbNail = "An error has occurred while trying to save the thumbnail of " & swModel.GetTitle & " to the local temp folder. The macro will exit now."
Exit Function
End If
swComponentModel.Visible = False
exWorkSheet.Rows(i + 1).RowHeight = Height
InsertPictureInRange exWorkSheet, imagePath, exWorkSheet.Range("A" & i + 1 & ":A" & i + 1)
End If
End If
' Populate Excel sheet with BOM table data
For j = 0 To swTableAnnotation.ColumnCount - 1
If swTableAnnotation.ColumnHidden(j) Then GoTo Skipper
exWorkSheet.Cells(i + 1, j + 2).Value = swTableAnnotation.DisplayedText(i, j)
Next j
Next i
' Bold the header row
For j = 2 To swTableAnnotation.ColumnCount + 1
exWorkSheet.Cells(swHeaderIndex, j).Font.Bold = True
Next j
' Auto-fit the columns and center align the content
Dim r As Object
Set r = exWorkSheet.Range(exWorkSheet.Cells(1, 2), exWorkSheet.Cells(swTableAnnotation.RowCount + 1, swTableAnnotation.ColumnCount + 1))
r.Columns.AutoFit
r.HorizontalAlignment = xlTextAlignment.xlCenter
r.VerticalAlignment = xlTextAlignment.xlCenter
End Function
' Subroutine to insert a picture in a specific range in Excel
Sub InsertPictureInRange(ActiveSheet As Object, PictureFileName As String, TargetCells As Object)
' Inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) > "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' Import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' Determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' Position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

4. Select the Bill of Materials in SOLIDWORKS

Make sure the Bill of Materials feature is selected in your SOLIDWORKS drawing and that nothing else is selected. This step is essential for the macro to run correctly.

5. Run the Macro

  • Click the green play button in the macro toolbar.
  • If a dialog box appears asking which subroutine to run, choose main and click Run.

The macro will now start running and export the BOM to Excel, complete with thumbnail previews for each visible component.

How the Macro Works

  • Exports BOM to Excel: Once the macro runs, it opens a new Excel session and copies the BOM data there.
  • Thumbnails for Each Component: For each visible component in the BOM, a thumbnail preview is generated in Excel, providing a visual reference.
  • Parts-Only BOM: This macro only works for parts-only BOMs and will not work with assemblies or sub-assemblies.

Limitations of the Macro

While this macro can significantly speed up the process of exporting BOMs with thumbnails, it has some limitations:

  • Cannot process large assemblies: If you’re working with large assemblies, this macro may not be able to handle the workload efficiently.
  • Compatibility: This macro is primarily designed for SOLIDWORKS 2018 and older versions. If you’re using a more recent version, consider other tools or solutions for better functionality.

For more complex assemblies or additional features, consider using advanced tools like xlBOM. This tool can handle larger assemblies and provides more robust export capabilities.

Why Choose Automation for BOM Export?

Automating the BOM export process in SOLIDWORKS VBA saves you time and effort, especially when dealing with repetitive tasks. By integrating thumbnail previews in Excel, you make it easier for stakeholders to visualize the components, which is critical in manufacturing and design reviews.


Need Help with SOLIDWORKS Automation?

At Blue Byte Systems, we specialize in automating SOLIDWORKS and PDM processes. If you need help with automating tasks, optimizing workflows, or building custom solutions for your company, contact us today. We can help streamline your design process and enhance productivity.

Reach out to Blue Byte Systems for expert assistance in automating your SOLIDWORKS workflows and BOM export processes.

Found an error or have suggestions? Send corrections via the comment section below. 👇

35 Comments

Comments are closed.

📝 Subscribe Now and join our community for goodies and more!