Export Bill Of Materials to Excel with thumbnails

Amen Jlili

This macro exports Bill of Materials to Excel with thumbnails. You definitely need this one if you are on SOLIDWORKS 2018 or older. When you run the macro, a new session is Excel is opened, the BOM is copied there and a thumbnail preview is created for each of the visible components. You must have Microsoft Office Excel installed to able to use this macro. Be aware that it only works on parts-only BOMs.

Export Bill of Materials to Excel with thumbnails
Export Bill of Materials to Excel with thumbnails

To use this macro:

  • Open SOLIDWORKS and load a drawing that contains a drawing with a Bill of Materials. Make sure it’s the active document.
  • Go To Tools > Macro > New …
  • Save this macro to a place of your choice.
  • In the VBE, clear up the text area and copy paste the code below.
  • In SOLIDWORKS, make sure the Bill of Materials feature is selected and nothing else.
  • Click on the green play button to start.
  • If a dialog pops up asking you to choose which subroutine to run, select main
  • Click on Run.
  • The macro will get the bill of Materials and copy it to Excel with thumbnails.
'All rights reserved  - amen@bluebyte.biz
'Blue Byte Systems, Inc does not provide any warranties for macros.
'SOLIDWORKS Export bill of materials to Excel
'Pre-conditions: BOM pre-selected.
'Results: BOM created in Excel with thumbnail preview
'define the width and height of the thumbnail
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
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()
Width = 21
Height = 60
'get pointer to the solidworks application
Set swApp = Application.SldWorks
'get active document
Set swModel = swApp.ActiveDoc
If swModel Is Nothing Then
swApp.SendMsgToUser "There is no active document"
End If
Set swSelectionManager = swModel.SelectionManager
'get the count of selected objects
Dim Count As Long
Count = swSelectionManager.GetSelectedObjectCount2(-1)
'if the user has made no selection then exit
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."
swApp.SendMsgToUser "Macro failed to export!"
End If
End If
Next i
End Sub
Public Function SaveBOMInExcelWithThumbNail(ByRef swTableAnnotation As Object) As String
Set exApp = CreateObject("Excel.Application")
If exApp Is Nothing Then
SaveBOMInExcelWithThumbNail = "Unable to initialize the Excel application"
Exit Function
End If
exApp.Visible = True
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 swTableAnnotation.RowCount = 0 Then
SaveBOMInExcelWithThumbNail = "BOM has no rows!"
End If
Dim swBOMTableAnnotation As BomTableAnnotation
Set swBOMTableAnnotation = swTableAnnotation
'set column width
exWorkSheet.Columns(1).ColumnWidth = Width
Dim HeaderRowIndex As Long
Dim swHeaderIndex As Integer
swHeaderTable = swTableAnnotation.GetHeaderStyle
If swHeaderTable = swTableHeaderPosition_e.swTableHeader_Bottom Then
swHeaderIndex = swTableAnnotation.RowCount
swHeaderIndex = 1
End If
For i = 0 To swTableAnnotation.RowCount - 1
If swTableAnnotation.RowHidden(i) Then
GoTo Skipper
End If
'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"
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 occured while trying to save the thumbnail of " & swModel.GetTitle & " to the local temp folder. The macro will exit now."
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
For j = 0 To swTableAnnotation.ColumnCount - 1
If swTableAnnotation.ColumnHidden(j) Then
GoTo Skipper
End If
exWorkSheet.Cells(i + 1, j + 2).Value = swTableAnnotation.DisplayedText(i, j)
Next j
Next i
'add header row to bold
For j = 2 To swTableAnnotation.ColumnCount + 1
exWorkSheet.Cells(swHeaderIndex, j).Font.Bold = True
Next j
Dim r As Object
Set r = exWorkSheet.Range(exWorkSheet.Cells(1, 2), exWorkSheet.Cells(swTableAnnotation.RowCount + 1, swTableAnnotation.ColumnCount + 1))
r.HorizontalAlignment = xlTextAlignment.xlCenter
r.VerticalAlignment = xlTextAlignment.xlCenter
End Function
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

Problems running the macro? Report a bug here

This macro has limitations. It cannot process large assemblies. xlBOM with thumbnails has to the ability to process large assemblies and more.


  1. Ilyas Zorla
    May 8, 2018

    Hello, Mr. Amen,

    first of all I would like to say briefly that I am glad that there are such persons as you. I am very interested in your solution regarding the parts list with preview. If all this works with the BOM I will try your PDM solution. Is it possible to get step by step instructions for the parts list? I always get an error message (You have not selected any bill of materials). I'd love to get it done, but I'm not getting any further. I'm always grateful for help.

    With kind regards

    Ilyas Zorla

    • Amen
      May 14, 2018

      Thanks for your comment Ilyas. I appreciate your interaction.
      If you are getting that message, it means that you have not selected a bill of materials.
      Follow these steps:
      - Open you SOLIDWORKS assembly document
      - Insert a bill of materials
      - Select the bill of materials in the three 3D space
      - Run the macro

      • Ilyas Zorla
        May 30, 2018

        Thank you Amen for the quick help.
        I have tested it and I am excited how easy it is and how well it works.
        Thank you very much for your support. Now you've got one more fan.
        Keep up the good work

        • Amen
          September 20, 2018

          Thanks Ilyas Zorla!

  2. Gordon Evans
    May 22, 2018

    Hello Amen,

    This script works very well and as intended for assemblies with a small number of parts (less than 30 to 40) but for an assembly with larger number of parts I'm having Solid Works crash. I had a hunch and it seems that Solid Works is hitting the GDI limit of 10k. I can up the limit to about 16k but that will only delay the crash. It seems that when opening and closing each part the GDI keeps climbing and is not being let go after each part is closed.

    Any way to make this work on larger assemblies? Ideas?


    Gord Evans

  3. Mucip
    June 1, 2018

    There is error in this line!
    Set swTableAnnotation = swSelectionManager.GetSelectedObject6(i, -1)
    Macro is not working?!


  4. Mari K
    July 16, 2018

    Hi Amen,

    Thanks for your code it works fine and generate image preview in excel sheet but saving the excel sheet i can only see the latest thumbnail image in all the rows, Any suggestions ?

    • Amen
      September 3, 2018

      Can you check the temporary folder and see if the images all get saved there.

      • Ben
        September 17, 2018

        I have the same issue. It only saves the last reverenced thumbnail. I believe that the code overwrites the previous image, and doesn't create a new one. Any suggestions on how to modify the code so that it creates a new unique file name?

        • Amen
          September 20, 2018

          Can you try running your SOLIDWORKS with administrator privileges?

          • BEN DRUSHAL
            September 25, 2018

            Even when I run it as an administrator, it still only remembers the last image. When I look in the temp folder for the image, it only has one file there isn't a temp image with multiple numbers, so when the Excel file is opened after saving it references that single image and only shows that thumbnail.

            When you run the Marco it works and shows all the images, its just when you save it and reopen it, that it shows the last thumbnail.

          • Amen
            September 25, 2018

            I think that is an excel problem. The only way I see this being fixed is through a SOLIDWORKS add-in.

        • Nik
          January 30, 2019

          I have solution for this problem.

        • Genia
          January 3, 2020

          I solved this problem by modifying the code to create a folder for the BOM and a Thumbnails folder inside it. I had it save as Thumbnail (i), where (i) is the index of each thumbnail. Did the trick for me!

          • TK
            March 8, 2020

            Hi Genia,

            Would you share how you fix the problem? Regarding the problem you mentioned in other reply (can't work with a large number of components), I think you can below in-between line 131 and 132

            swApp.CloseDoc swComponentModel.GetTitle

            Your help is much appreciated!


  5. Ben
    July 18, 2018

    I have tried to use this macro. I have run into a few problems. If I run it from the assembly, it exports the BOM but with no thumbnails. If I run it from the drawing of the assembly it only exports a few of the thumbnail.

    Any suggestions would be appreciated.

  6. SAID
    July 30, 2018

    Thank Amen

    whats about assemblies components ?


  7. Miro Wrobel
    August 16, 2018


    you are the best. Thank you very much for this marvellous work. I have learned a lot from your code, also how to handle an Excel document.

    Cheers, Miro

  8. Jeff
    September 28, 2018

    Can you make it close the part file when its finished with the part entry? Mine crashes around 20 parts after solidworks runs out of resources.

    • al swift
      October 12, 2018

      Yep, mine also crashes, it would be a great amendment

  9. shiran
    December 8, 2018

    Dear Amen
    I am using SW 2016 SP5 and excel 2016 when I operate the macro it is not working and it shows a message: "run-time error '424' object required", and in the visual basic for applications line 59 in the code in marked ( If swSelectionManager.GetSelectedObjectType3(i, -1) = swConst.swSelectType_e.swSelANNOTATIONTABLES Then).....
    can you help me fix the problem?
    Thanks in advance,

  10. sergi
    January 21, 2019

    Hi Amen,
    Many thanks for your macro. Unfortunately I´m at the same situation of most of us.
    The solidworks crash after running the macro for the first 20 to 30 parts.

    Can you let us know if there is any solution? or better start to write the macro from the 0.
    Again many thanks for your help.
    Best regards.

  11. Md Sobug Mia
    January 24, 2019

    Thank you sir for this awesome macro...

  12. Ahmed Abdelrazik
    March 10, 2019

    Perfect Thanks Man

  13. Tomáš Adámek
    June 10, 2019

    Greetings Amen,

    First of all I would like to thank you for providing this awesome Macro, it really rocks.
    But unfortunately i am facing the same issue as the others. After aproximetly 30 (sometimes 38) items perfectly linked to excel BOM, my SW always collapses. Is there any possibility, how to avoid this unpleasant situation?

    Thank you for your respond.

    • Amen JLILI
      November 29, 2019

      Please use the add-in posted on the article header.

  14. Wade
    October 25, 2019

    Thanks for the program. Some of the items when exported show as N/A on the excel spreddsheet. Excel 2010

    • Amen JLILI
      October 25, 2019

      What are you using: the add-in or the macro?

  15. Genia
    January 3, 2020

    I solved this problem by modifying the code to (instead of saving in the temp location) create a dedicated folder for the BOM and a Thumbnails folder inside it. I had it save as Thumbnail (i), where (i) is the index of each thumbnail. Did the trick for me!

  16. Genia
    January 3, 2020

    Hi Amen,

    Fabulous work, I must say! I heavily modified it to do a few extra things :-) I am also running into the issue where I can't get it to work with a large number of components... is there a setting I can change somewhere? I'd like to keep it in macro form (unless there is a way to modify the source code in the add-in)...

    Warm regards,


    • Amen JLILI
      January 4, 2020

      Hmm. This is the reason why I developed the add-in. :(

  17. Val
    August 18, 2020

    Anybody else notice that Solidworks won't export a thumbnail if you only have ONE part? Thumbs export correctly to Excel if you have multiple parts or assemblies, but if you have only a simgle part, the BOM exports but the thumb is missing. I was hoping this macro would resolve that, but it doesn't. It's a Solidworks issue. Can anyone confirm or have a workaround? THANKS!

    • Amen Jlili
      August 18, 2020

      Interesting find! I'll look at this later. Meanwhile, check https://bluebyte.biz/product/xlbom-with-thumbnails/ for a better solution.

  18. Jarred Degeyter
    May 21, 2021


    I'm curious if this will export different thumbnails for difference configurations of a part file? For instance, if I have a part that is used twice in an assembly, and each instance of that part is a different configuration, will it export a thumbnail of each configuration?

    • Amen Jlili
      May 21, 2021

      Unfortunately, no but xlbom with thumbnails ( https://bluebyte.biz/product/xlbom-with-thumbnails/ ) will do. If you are interested in a live demo, let me know when and I will send you an email.

Leave a Reply

More great articles

Crack any SOLIDWORKS password-protected VBAmacro with screenshots

Disclaimer: The information shared in this article is intended to show how easy it is to unlock the password protection…

Read Story

SOLIDWORKS PDM Professional: You have chosen to load a .Net Add-in? Well, what do you do next ?

SOLIDWORKS PDM Professional employs a server-client architecture. Your files and metadata is stored on the server (Archive server is where…

Read Story

Top reasons to upgrade your VBA macros in 2020

Converting a SOLIDWORKS VBA macro to an add-in. VBA macros continue to be the choice of the SOLIDWORKS community for…

Read Story