Hi @checkcheck_master
The solution to your problem is the code below which I put together inspired by the above suggested solutions of @Curtis_Waguespack and @WCrihfield :
' Define the active document
Dim oDoc As Document
oDoc = ThisApplication.ActiveDocument
' Access the Bill of Materials and tweak ad lib
Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM
oBOM.StructuredViewFirstLevelOnly = True
oBOM.StructuredViewEnabled = True
' Access BOM view structured
Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Structured")
' This gets name of the active model state
' It is for filtering later in the For Loop
Dim oColName As String
oColName = "QTY (" & oBOMView.ModelStateMemberName & ")"
' Export BOM to file
' Modelstate names can have symbols that file names would not allow
' Change to your liking
oBOMView.Export("BOM_XYZ.xls", kMicrosoftExcelFormat)
' This navigates to the new file
Dim oFile As String
oFile = ThisDoc.Path & "\" & "BOM_XYZ.xls"
' Define Excel objects
Dim excelApp As Object = CreateObject("Excel.Application")
excelApp.Visible = False
excelApp.DisplayAlerts = False
' Checks if file exists
If Dir(oFile) <> "" Then
Dim oWB As Object = excelApp.Workbooks.Open(oFile)
Dim oWS As Object = oWB.Worksheets(1)
' Loop through columns in reverse
' That is because the active model state might not be the first
' If you deleted collumns before the active comes up
' The indexing would not work correctly
' This mitigates that problem
For iCol = oWS.UsedRange.Columns.Count To 1 Step -1
Dim oCellValue As String = oWS.Cells(1, iCol).Value
If oCellValue Is Nothing Or oCellValue = "" Then Continue For
' Here we make use of the Active Model state name created earlier
If oCellValue.Contains(oColName) Then
' This gives the collumn of the quantities in the Active model state
' the name you want, in my case QTY
oWS.Cells(1, iCol).Value = "QTY"
' This next segment deletes all the other model state quantities
ElseIf oCellValue.Contains("QTY") Then
oWS.Columns(iCol).EntireColumn.Delete
End If
Next
' Autofit columns and save
oWS.Columns.AutoFit()
oWB.Save()
oWB.Close()
End If
excelApp.Quit()
excelApp = Nothing
Generally, it is a workaround. Normally, I would export directly to .txt file which I need for further processing.
I use the excel just as a step for debugging thanks to its orderliness and ease of editing. But I would prefer implementation of the BOM filtering by Active Model State directly for the export in these formats in ThisBOM.Export(“BOMViewName”, filename, format):
- kMicrosoftAccessFormat = Microsoft Access
- kMicrosoftExcelFormat = Microsoft Excel
- kdBASEIVFormat = dBASE IV
- kdBASEIIIFormat = dBASE III
- kTextFileTabDelimitedFormat = Text File Tab Delimited
- kTextFileCommaDelimitedFormat = Text File Comma Delimited
- kUnicodeTextFileTabDelimitedFormat = Unicode Text File Tab Delimited
- kUnicodeTextFileCommaDelimitedFormat = Unicode Text File Comma Delimited
I am currently in 2023.5.1, is it maybe already implemented or planned to be in the newer releases? @johnsonshiue