solidworks macro export bom to excel

SolidWorks Macros: Export BOM to Excel

By Mark Anderson
AD

Streamlining your design-to-production workflow often involves simplifying data transfer between tools. If you're using SolidWorks for 3D modeling and need to export your Bill of Materials (BOM) to Excel for further analysis or ordering, automating this process can save you significant time and reduce manual errors. macros come in handy, enabling you to perform repetitive tasks with just a few clicks. Let's dive into how to create a SolidWorks macro to export a BOM to Excel.

Before we proceed, ensure you have a basic understanding of macro programming and the Visual Basic for Applications (VBA) editor in SolidWorks. We'll be using VBA to create our macro.

Understanding SolidWorks Macro Basics

Under the hood, SolidWorks macros are simply recorded or written sequences of instructions performed via VBA. Each command you give in SolidWorks, such as "Open a file" or "Export a BOM," is translated into a line of VBA code that can be executed when the macro is run.

AD
a brochure with an image of a snow plow and the words, solid works
a brochure with an image of a snow plow and the words, solid works
Common Modeling Shortcut Commands in SOLIDWORKS
Common Modeling Shortcut Commands in SOLIDWORKS
SolidWorks
SolidWorks

A macro can simplify and automate complex tasks, but it's essential to understand that macros are tool-specific. A SolidWorks macro won't work in other software like Excel. That's why we'll focus on exporting data from SolidWorks to Excel using macros.

Setting Up VBA in SolidWorks

Before creating your first macro, you need to enable and access the VBA editor in SolidWorks. From the Tools menu, select "Macro" and then "Record macro" to start recording a new macro or "Play" to run an existing one. To access the VBA editor, choose "Visual Basic" from the same menu. Here, you can view, modify, or create new macros.

Porting equations from Excel to SolidWorks
Porting equations from Excel to SolidWorks
The SOLIDWORKS Time Saver I Wish More Teams Used - Agribusiness Education and Research International
The SOLIDWORKS Time Saver I Wish More Teams Used - Agribusiness Education and Research International
Beginner's Guide to SOLIDWORKS 2025
Beginner's Guide to SOLIDWORKS 2025

Once in the VBA editor, you'll see a new window filled with code. Don't let it intimidate you; most of the time, you won't need to edit this code directly. We'll mainly be using the macro recorder to create our BOM export macro.

Recording the BOM Export Macro

Now let's create the macro to export a BOM to Excel. Start by opening the SolidWorks file containing the parts and assemblies whose BOM you want to export. Then, follow these steps while recording the macro:

AD
  1. Select the "Insert" tab on the SolidWorks ribbon.
  2. Click on "Bill of Materials" and choose "custom" or "attern."
  3. Adjust the settings in the BOM PropertiesManager. You can choose whether to include sub-assemblies, show/grey out components, and more.
  4. Once satisfied with the settings, click "OK" to open the BOM. The macro will record the specific selection, options, and display.
  5. Select the BOM in the graphics area, then click on "Export" in the "Bill of Materials" panel.
  6. In the "Export" dialog box, choose the destination for your Excel file and click "Save."

After completing these steps, stop recording the macro in the VBA editor. You've just created a macro that exports a BOM to Excel with the chosen settings.

Solidworks Automatic Sheet Metal DXF/DWG Export Tool, Drawing/Part/Assembly Conversions
Solidworks Automatic Sheet Metal DXF/DWG Export Tool, Drawing/Part/Assembly Conversions
⚡ I Tried SolidWorks Flex Feature—Here's What Happened
⚡ I Tried SolidWorks Flex Feature—Here's What Happened
How to correctly automate your Excel reports
How to correctly automate your Excel reports
Solidworks tutorial | sketch Gate Valve in Solidworks | Solidworks - YouTube Solidworks Crane Tutorial, How To Make A Tractor In Solidworks, Solidworks Tractor Tutorial, How To Make A Gate Valve In Solidworks, Solid Works Design, Solidworks Projects, How To Design A Valve In Solidworks, Gate Valve Solidworks, Solidworks Hydraulic Cylinder Tutorial
Solidworks tutorial | sketch Gate Valve in Solidworks | Solidworks - YouTube Solidworks Crane Tutorial, How To Make A Tractor In Solidworks, Solidworks Tractor Tutorial, How To Make A Gate Valve In Solidworks, Solid Works Design, Solidworks Projects, How To Design A Valve In Solidworks, Gate Valve Solidworks, Solidworks Hydraulic Cylinder Tutorial
5 macros I use to automate my Excel workbook
5 macros I use to automate my Excel workbook
The BEST Way to Transition Shapes in #SolidWorks
The BEST Way to Transition Shapes in #SolidWorks

Customizing the BOM Export Macro

Now that you have a basic BOM export macro, let's customize it to suit your needs. Open the VBA editor and find the macro you just created. You can modify the recorded code to change the BOM's settings, add delay times for slow computer environments, or include additional functionality like saving the Export dialog box details as variables.

Modifying BOM Properties

To modify the BOM properties, you can edit the recorded code to change the "Type" or "Status" of BOM items. For example, to include sub-assemblies, find the line of code that sets the BOM's properties and change `iseerd=False` to `ised=True`.

AD

Adding Delays

If your computer is slow, or if you want to ensure the BOM is fully loaded before exporting, you can add a delay to your macro. Using the `Application.Wait` command, you can pause the macro for a specified amount of time (in seconds). For example, `Application.Wait Now + TimeValue("00:00:01")` will pause the macro for one second.

SolidWorks Tutorial Design of tank
SolidWorks Tutorial Design of tank

Using the BOM Export Macro

With your customized macro ready, you can now use it to quickly export BOMs from SolidWorks to Excel. Open the VBA editor, select your macro, and click "Play" to run it. The macro will perform the export tasks you've recorded and customized, saving you time and reducing manual errors.

Remember to backup your SolidWorks files and VBA projects regularly to prevent data loss. Also, keep your SolidWorks software and VBA editor updated to ensure optimal performance and access to the latest features.

AD

Troubleshooting Common Issues

If your macro isn't working as expected, first ensure that you've saved the changes you made in the VBA editor. Then, check for any error messages that may appear in the Immediate window at the bottom of the VBA editor. Error messages will help you pinpoint the issue and troubleshoot effectively.

If you're still having trouble, consider seeking help from SolidWorks forums, official documentation, or professional SolidWorks trainers. Many common issues have been addressed and resolved by the user community, and others can learn from your experience as well.

In today's fast-paced design and manufacturing environments, automating repetitive tasks is essential for maintaining productivity and minimizing errors. Utilizing SolidWorks macros can help you streamline your workflows, freeing up more time to focus on innovative designs and problem-solving. So, start exploring and mastering VBA macros today, and watch your productivity soar!

AD