iLogic to export to excel

[sstembridgeCNX3B]

Hello all,

 I have no clue how to write an iLogic rule. So i came here looking for some help. I was wondering if there was a way to get quantities of .ipt, .iam in all levels of a main assembly and have it exported to a excel sheet. We use parts in multiple assemblies and weldments in the main assembly ( top level).  Parts are used in multiple projects also. I just looking to pull quantities from the top level for all levels. 

[WCrihfield]

Hi @sstembridgeCNX3B.  I am not sure you need any code for this.  It sounds like you could just open the BOM of your main assembly, go to the Structured tab and make sure that view of the BOM is enabled. Then edit the properties of that view to show all levels.  Then click the export button.

 

[WCrihfield]

@sstembridgeCNX3B But just incase you already know how to do it manually, but still want to do it by code, there are a couple ways to do it.  There is an iLogic shortcut snippet that could be used, then there is the normal Inventor API way.  The iLogic shortcut snippet route requires less overall code, but gives you less control and fewer options.  For example, it does not make sure we are working with an assembly, so may throw an error if it is not an assembly.  It may not ensure that the structured view has been enabled or that the 'all levels' setting has been set to True yet.

Dim sFileName As String = ThisDoc.PathAndFileName(False) & ".xlsx"
ThisBOM.Export("Structured", sFileName, FileFormatEnum.kMicrosoftExcelFormat, "Assembly BOM - Structured All Levels")

 And below is the regular Inventor API way, which includes the extra precautions and options.

Sub Main
	Dim oADoc As AssemblyDocument = TryCast(ThisDoc.Document, Inventor.AssemblyDocument)
	If oADoc Is Nothing Then
		Logger.Debug("iLogic rule '" & iLogicVb.RuleName & "' was aborted - no AssemblyDocument found.")
	 	Return
	End If
	If oADoc.RequiresUpdate Then oADoc.Update2(True)
	Dim oBOM As Inventor.BOM = oADoc.ComponentDefinition.BOM
	oBOM.StructuredViewEnabled = True
	oBOM.StructuredViewFirstLevelOnly = False
	oBOM.HideSuppressedComponentsInBOM = True
	Dim oStrBOMView As BOMView = Nothing
	For Each oBOMView As BOMView In oBOM.BOMViews
		If oBOMView.ViewType = BOMViewTypeEnum.kStructuredBOMViewType Then
			oStrBOMView = oBOMView
			Exit For
		End If
	Next oBOMView
	If oStrBOMView Is Nothing Then Return
	Dim sFileName As String = ThisDoc.PathAndFileName(False) & ".xlsx"
	Dim oOptions As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
	oOptions.Add("Table Name", "Assembly BOM - Structured All Levels")
	oStrBOMView.Export(sFileName, FileFormatEnum.kMicrosoftExcelFormat, oOptions)
End Sub

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[sstembridgeCNX3B]

I tried the API Way and it errors out. 

[nedeljko.sovljanski]

hi @sstembridgeCNX3B 

I don't see need for line 22, also remove oOptions from 23.

[WCrihfield]

Sorry about that.  I just typed up most of that within the forum code window, and forgot that the final, optional input is just a regular String value, not a NameValueMap.

Sub Main
	Dim oADoc As AssemblyDocument = TryCast(ThisDoc.Document, Inventor.AssemblyDocument)
	If oADoc Is Nothing Then
		Logger.Debug("iLogic rule '" & iLogicVb.RuleName & "' was aborted - no AssemblyDocument found.")
	 	Return
	End If
	If oADoc.RequiresUpdate Then oADoc.Update2(True)
	Dim oBOM As Inventor.BOM = oADoc.ComponentDefinition.BOM
	oBOM.StructuredViewEnabled = True
	oBOM.StructuredViewFirstLevelOnly = False
	oBOM.HideSuppressedComponentsInBOM = True
	Dim oStrBOMView As BOMView = Nothing
	For Each oBOMView As BOMView In oBOM.BOMViews
		If oBOMView.ViewType = BOMViewTypeEnum.kStructuredBOMViewType Then
			oStrBOMView = oBOMView
			Exit For
		End If
	Next oBOMView
	If oStrBOMView Is Nothing Then Return
	Dim sFileName As String = ThisDoc.PathAndFileName(False) & ".xlsx"
	Dim sTabName As String = "BOM - Structured All Levels"
	oStrBOMView.Export(sFileName, FileFormatEnum.kMicrosoftExcelFormat, sTabName)
End Sub

[sstembridgeCNX3B]

Would there be a way to get the same parts combine instead of having listed multiple times? 

[WCrihfield]

Hi @sstembridgeCNX3B.  Maybe the 'Parts Only' view of the BOM would work better for this situation.  It contains only the parts (no sub assemblies), but contains all parts from all levels of the assembly.  So, if the same part is found in multiple sub assemblies, they would be bundled together into one line item in the BOM, with total quantity shown for whole assembly.  Below is an edited version of the previous code I posted, where it has been changed to export the Parts Only view of the BOM instead.  There are more things that could be done in this type of scenario too, if needed.  For instance, we could import a BOM customization (sets which columns are showing, and in what order), sort it, renumber the Item Numbers, or something like that before exporting.

Sub Main
	Dim oADoc As AssemblyDocument = TryCast(ThisDoc.Document, Inventor.AssemblyDocument)
	If oADoc Is Nothing Then
		Logger.Debug("iLogic rule '" & iLogicVb.RuleName & "' was aborted - no AssemblyDocument found.")
	 	Return
	End If
	If oADoc.RequiresUpdate Then oADoc.Update2(True)
	Dim oBOM As Inventor.BOM = oADoc.ComponentDefinition.BOM
	oBOM.PartsOnlyViewEnabled = True
	'oBOM.PartsOnlyViewNumberingScheme = NumberingSchemeEnum.kNumericNumbering
	'oBOM.PartsOnlyViewMinimumDigits = 1
	oBOM.HideSuppressedComponentsInBOM = True
	Dim oPartsOnlyBOMView As BOMView = Nothing
	For Each oBOMView As BOMView In oBOM.BOMViews
		If oBOMView.ViewType = BOMViewTypeEnum.kPartsOnlyBOMViewType Then
			oPartsOnlyBOMView = oBOMView
			Exit For
		End If
	Next oBOMView
	If oPartsOnlyBOMView Is Nothing Then Return
	Dim sFileName As String = ThisDoc.PathAndFileName(False) & ".xlsx"
	Dim sTabName As String = "BOM - Parts Only"
	oPartsOnlyBOMView.Export(sFileName, FileFormatEnum.kMicrosoftExcelFormat, sTabName)
End Sub

 BOM 

BOMView 

BOMRow