Live BOM from SolidWorks to Excel — Native Table, Macro, and the Better Middle Path

To export a SolidWorks BOM to Excel, you have three paths — and choosing the wrong one is how you end up maintaining a 200-line macro for two months before admitting it doesn’t quite work.

A post on r/SolidWorks showed exactly that: a macro that exported an assembly BOM to Excel with description, material, length, thickness, quantity, and supplier data. It mostly worked. Six comments later, nobody had a better answer than “looks fine to me.” The approach was fine — the architecture was wrong.

Export SolidWorks BOM to Excel — Three Approaches

Each path makes different assumptions about how the export runs, what data it captures, and how much it scales. The right one depends on your workflow, not on which is most technically impressive.

Path 1: The native BOM table

SolidWorks can save a BOM table directly from a drawing without touching the API. Open a drawing with a BOM annotation, right-click the table, and choose Save as. You get options for .xlsx, .xls, .csv, .txt, or .sldtbt (template). The export includes all visible columns and all rows, with formatting approximately matching the drawing table.

This is the right answer for 80% of teams. The export is repeatable, requires no code, and the resulting file has the right data in it. The limitations are specific:

• No headless operation. You need SolidWorks open with the drawing loaded. You can script it with Task Scheduler, but the assembly + drawing must be open first.
• No live refresh. The exported .xlsx is a snapshot. If the model changes, the file does not update until you re-export.
• Column order is fixed. The export mirrors the visible BOM columns in the drawing. Custom columns require custom properties set up in advance.
• Multi-level BOM requires a multi-level drawing BOM. If you want an indented structure in Excel, you need an indented BOM in the drawing.

For shops that run an export-on-release workflow — models are revised in PDM, released, then exported — the native table is clean and simple. The export is a one-button operation per drawing.

Path 2: VBA BOM Export via ITableAnnotation

A VBA BOM macro is the right tool when the native export is not enough — typically because you need to drive the export from a list of assemblies, inject non-BOM data, or run without a drawing open. This is where most teams reach for the API.

The BOM table in SolidWorks exposes itself as IBomTableAnnotation, which implements ITableAnnotation. The read path is straightforward:

' VBA — extract BOM from an open drawing
Dim swApp As SldWorks.SldWorks
Dim swDraw As SldWorks.DrawingDoc
Dim vAnnotations As Variant
Dim swTableAnnotation As SldWorks.TableAnnotation
Dim i As Integer, row As Integer, col As Integer

Set swApp = Application.SldWorks
Set swDraw = swApp.ActiveDoc

vAnnotations = swDraw.GetTableAnnotations()

For i = 0 To UBound(vAnnotations)
    Set swTableAnnotation = vAnnotations(i)
    If swTableAnnotation.Type = swTableAnnotationType_BOM Then
        Dim totalRows As Integer
        Dim totalCols As Integer
        totalRows = swTableAnnotation.RowCount  ' row 0 is the header
        totalCols = swTableAnnotation.ColumnCount

        ' Dump header row first
        Dim header() As String
        ReDim header(totalCols - 1)
        For col = 0 To totalCols - 1
            header(col) = swTableAnnotation.GetText(0, col)
        Next col

        ' Dump data rows
        For row = 1 To totalRows - 1
            For col = 0 To totalCols - 1
                Debug.Print swTableAnnotation.GetText(row, col)
            Next col
        Next row
    End If
Next i

In C# COM interop, the same logic with explicit casting:

var drawing = (DrawingDoc)swApp.ActiveDoc;
var annotations = (object[])drawing.GetTableAnnotations();

foreach (var ann in annotations)
{
    var table = (ITableAnnotation)ann;
    if (table.Type != (int)swTableAnnotationType_e.swTableAnnotationType_BOM)
        continue;

    int rows = table.RowCount;
    int cols = table.ColumnCount;

    for (int row = 0; row < rows; row++)
    {
        for (int col = 0; col < cols; col++)
        {
            string cell = table.GetText(row, col);
            // write to your Excel interop here
        }
    }
}

The GetTotalRowCount trap

IBomTable has a method GetTotalRowCount() that sounds like what you want. It is not. It returns 0 when the BOM is not fully visible on screen — which happens constantly when a macro runs headless or when you are debugging with the Properties window open. ITableAnnotation.RowCount returns the real count regardless of visibility. Use that one.

Writing to Excel from VBA

Once you have the cell data, writing to Excel is standard COM interop. A minimal working push:

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets(1)

' Write cells — xlSheet.Cells(row+1, col+1) because Excel is 1-indexed
For row = 0 To totalRows - 1
    For col = 0 To totalCols - 1
        xlSheet.Cells(row + 1, col + 1).Value = swTableAnnotation.GetText(row, col)
    Next col
Next row

xlBook.SaveAs "C:\output\bom_export.xlsx"
xlApp.Quit

The common failure point is leaving xlApp.Visible = True during development, then forgetting to set it back. An invisible Excel process accumulates in Task Manager, eventually blocking the next call with “Excel is busy.”

The live-link fallacy

Search long enough on the SolidWorks forums and you will find references to a “live DDE link” between SolidWorks design tables and Excel. This is real but scoped to design tables only — the embedded Excel spreadsheet that drives configurations. It has nothing to do with BOM tables.

There is no production-viable mechanism for a live link between a SolidWorks BOM and an open Excel workbook. Both the SolidWorks COM interop and Excel COM interop are single-threaded. When you have SolidWorks open and a macro calls Excel, the call blocks until SolidWorks releases its COM lock. If Excel is also waiting on user input, you get a deadlock. Teams that have tried this report it works in isolation and fails unpredictably in production. Do not build a workflow around it.

Path 3: PDM-driven export

For teams running SolidWorks PDM Professional, the right answer at scale is not a BOM macro at all — it is a PDM task. The PDM Task Add-in can trigger a SolidWorks session headlessly via Task Scheduler, open the assembly, regenerate the drawing, and run an export macro that lands the BOM in a network share or pushes it directly into ERP.

The advantage is the trigger: PDM fires the task on state transition (e.g., “Released” → “Approved”), not on human memory. The export happens automatically when the release workflow completes. No drawing needs to be open manually.

The disadvantage is setup cost. Writing a PDM task add-in takes longer than a standalone macro, and it requires PDM Professional (not Standard). For a team of 3 running 10 releases a week, the setup time does not pay off.

The rough break-even: if you are manually running the BOM export more than five times a day across multiple engineers, a PDM task add-in is worth the 2-3 days of setup. Below that, the native save-as export is faster.

What the middle path actually looks like

The problem with the 200-line macro approach is not the macro — it is the architecture. A macro that reads the BOM, formats it, and exports it to a specific Excel template is brittle because it couples three independent concerns: BOM structure (determined by the model), export format (determined by whoever uses the Excel file), and the export trigger (determined by the engineer running the macro). Change any one of them and the macro breaks.

The cleaner architecture separates the concerns:

1. BOM definition stays in the model — custom properties, configurations, and BOM structure are engineering data. They live in SolidWorks.
2. Export produces a neutral structured format — not a particular Excel layout, but a stable structured output (CSV, JSON, or a well-defined XML schema) that downstream tools can consume.
3. Downstream tools own the formatting — the ERP system, the Excel template, or the purchasing database each transforms the structured data into its own format.

This is what a batch export tool handles when it embeds metadata into DXF layer names — the CAD data gets baked into the output artifact in a structured way, rather than living in a fragile export script that must be maintained in parallel with the model. The EBOM vs MBOM handoff problem breaks at exactly this layer: when the BOM export format is specific to one consumer, it has to be rebuilt for each new one.

If you are maintaining a macro that exports a BOM to a fixed Excel layout, the next question to ask is whether the Excel layout could be replaced by a structured export that the downstream system ingests directly. Often it can. If it cannot — because the consumer is a human reading the sheet, not a system importing it — the native BOM save-as is the right tool and the macro was always unnecessary complexity.

Summary

The PDM BOM downstream problem does not start in PDM — it starts when the BOM export format is designed for one consumer and then reused for five others without ever being refactored. Pick the path that matches the actual volume and consumer, not the one that was fastest to prototype.

If the BOM accompanies 3D geometry in a supplier package, you will also need to decide which STEP version to include — AP203, AP214, or AP242 — since the AP242 PMI option silently falls back to AP214 format unless explicitly configured.

For teams that also need to automate drawing view creation or regeneration as part of the same release workflow, our guide to automating SolidWorks drawing creation with macros covers the IDrawingDoc API, view placement, and annotation update patterns that pair naturally with the BOM export approaches above.