Export BOM with ilogic Inventor 2022

[apl.coemter]

Hello All

We have recently upgraded to Inventor 2022, from Inventor 2020 and we have the following issue:
When we export a BOM using iLogic, a column "Quantity" is generated for each Model State in the assembly.
In Inventor 2020, the iLogic rule only generated one column "Quantity"
The iLogic rule is this:

ThisBOM.Export("Estructurado", "C:\TEMPORALS\prova.xlsx", kMicrosoftExcelFormat)

 What do I need to do to get only one column "Quantity" like Inventor 2020?

I attach images of the excel files

Thanks,

[Curtis_Waguespack · Accepted solution]

Hi @apl.coemter 

There might be a better way to do this, but here is an example that exports the BOM, then opens and deletes the extra QTY columns related to Model States, and then renames the "QTY (Master)" column to just QTY.

I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com

'define the file to create/open
oFile = "C:\Temp\MyExcelFile.xlsx"

ThisBOM.Export("Structured", oFile, kMicrosoftExcelFormat)

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run invisibly, change to true if you want to run it visibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

'check for existing file 
If Dir(oFile) <> "" Then
	'workbook exists, open it
	oWB = excelApp.Workbooks.Open(oFile)
	oWS = oWB.Worksheets(1)
Else
	Return 'exit rule
End If

'Loop through the columns
For iCol = 1 To oWS.UsedRange.Columns.Count
	'get value of first cell
	oCellValue = oWS.Cells(1, iCol).Value
	
	If oCellValue = "" Then Continue For
	'get Master QTY cell number	
	If oCellValue.contains("QTY (Master)") Then
		oQTYCol_Number = iCol
	ElseIf oCellValue.contains("QTY ") Then
		'delete column
		oWS.Columns(iCol).EntireColumn.Delete
	End If
Next iCol

'rename master column
oWS.Cells(1, oQTYCol_Number).Value = "QTY"

'set all of the columns to autofit
excelApp.Columns.AutoFit
'save the file
oWB.SaveAs(oFile)

'close the workbook and the Excel Application
oWB.Close
excelApp.Quit
excelApp = Nothing     

[apl.coemter · Accepted solution]

Thank you.

Finally I use your code with some modifications

'define the file to create/open
oFile = "C:\COEMTER CAD\TEMPORALS\prova.xlsx"

ThisBOM.Export("Estructurado", oFile, kMicrosoftExcelFormat)

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run invisibly, change to true if you want to run it visibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

'check for existing file 
If Dir(oFile) <> "" Then
	'workbook exists, open it
	oWB = excelApp.Workbooks.Open(oFile)
	oWS = oWB.Worksheets(1)
Else
	Return 'exit rule
End If

'Loop through the columns
For iCol = 1 To oWS.UsedRange.Columns.Count
	'get value of first cell
	oCellValue = oWS.Cells(1, iCol).Value
	If oCellValue = "" Then Continue For
		'get Master QTY cell number
	If oCellValue.contains("CTDAD ") Then
		If Not oCellValue.contains("CTDAD (Principal)")
			'delete column
			oWS.Columns(iCol).EntireColumn.Delete
			iCol = iCol - 1
		Else
			'rename master column
			oWS.Cells(1, iCol).Value = "CTDAD"
		End If
	End If
Next iCol

'set all of the columns to autofit
excelApp.Columns.AutoFit
'save the file
oWB.SaveAs(oFile)

'close the workbook and the Excel Application
oWB.Close
excelApp.Quit
excelApp = Nothing   

[llorden4]

Are there any new flags introduced that can output a result that MATCHES the manual workflow? 

The only help documentation I've found is here and provides minimal assistance.

[MartinJezek]

Hi @apl.coemter 

For exporting the BOM of your currently Active Model State use the following code:

' 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

I got inspired by the approach of @Curtis_Waguespack who was filtering the columns and used knowledge of @WCrihfield regarding BOMView.ModelStateMemberName from the following post:

Solved: How can I restrict iLogic BoM Export to a particular Model State? Now I get data from all of...

This code is a simplified part of a bigger code as I like to export in batches of various formats in a separate folder for further processing.

Model States are great, have awesome functionalities for generating configurations via spreadsheets and with a bit of coding you can generate all .idws, .pdfs and other formats with just one click of a button when everything is set up nicely.

@llorden4 Please take a look at the code above, it gives the result you would normally get through manual export of BOM.