Using Excel filters on an Excel BOM with thumbnails    

By

SOLIDWORKS 2019 introduced a new option to include a thumbnail of each component when you export a Bill Of Materials to Microsoft Excel. I noticed that a lot of SOLIDWORKS customers are using this Excel BOM to group their components with the filter functionality in Excel. Unfortunately this did not work as expected, because the thumbnails don’t move along with the filter action – they have a fixed position. In this tech blog I want to tell you more about a solution to use the filter functionality together with the new thumbnail option.

Firstly, create an Excel BOM with thumbnails:

  1. Right-click a BOM in SOLIDWORKS and click Save As.
  2. In the dialog box:
    1. For Save as type, select one of the following:
      • Excel 2007 (*.xlsx)
      • Excel (*.xls)
    2. Select Thumbnails.
    3. Browse to a folder, enter a file name, and click Save.

The next step is to create a filter in Microsoft Excel to group components based on part number. In this example this will mimic the difference between purchase parts and make parts. And of course I want to ensure that the thumbnails move along.

Follow the next steps to achieve the desired result:

  1. Open the Excel BOM with Microsoft Excel.
  2. Go to the Review tab in the Ribbon and click Unshare Workbook.
    This makes it possible to edit the thumbnails.
  1. Press F5. This will open the Go To dialog box.
  2. Select Special in the Go To dialog box.
  1. In the Go To Special dialog box, select Objects and click OK.
    This will automatically select all the thumbnails in the Excel BOM.
  1. Right click one of the thumbnails and select Size and Properties.
  2. The Format Picture menu appears. Go to Properties and select Move but don’t size with cells.

    This ensures that the thumbnails will be connected to the part number after applying the filter action.
  1. Select all columns of the Excel BOM (column A, B, C, D and E in the first image).
  2. Go to the Data tab in the Ribbon and select Filter.
  3. Now the Number column can be sorted by selecting Sort A to Z.

 

  1. The thumbnails move along with the sort action.

Conclusion

SOLIDWORKS 2019 introduced a great way to communicate your bill of materials with other people inside or outside your organisation. The visualization with thumbnails introduces more clarity for everyone. But on the other side a lot of users struggle with the filter tool in Microsoft Excel to sort their BOM along with the thumbnails. I hope this tech blog clarified this workflow, so your BOM output is better then before.

 

Author: Martijn Visser, Elite Application Engineer, CAD2M

CAD2M

CAD2M

CAD2M believes in Industrial Equipment companies that want to make an impact. What drives us is to bring the most out of your employees. To help you in the best way possible, our employees have to be on top of their game as well. We live for challenges in process & product development. It is our passion. With our knowledge of processes we help your company work faster and smarter. We provide you with the best PLM, 3D design & engineering plus manufacturing tools. Want to grow together? CAD2M.
CAD2M

@CAD2M_BV

CAD2M