Export Excel file with iLogic

[byzkc54]

I want the product codes of the products included in the assembly to be sorted in excel according to the material properties. At the same time, it would be perfect if it also shows how many quantity were used in the assembly. For example, the photo below.

[bradeneuropeArthur]

Is Export the Bill Of Material not what you need here?

[byzkc54]

yes, I forgot, I'm sorry. I just wanted to export materials that are sheet metal.

[bradeneuropeArthur]

In principle you need an filtered (sheetmetal) export of the bill of material, correct?

[byzkc54]

yes, only sheet metal

[Andrii_Humeniuk · Accepted solution]

Hi @byzkc54 . I hope this is exactly what you wanted. Please check if everything works.

Sub main
	Dim oDoc As Document = ThisApplication.ActiveDocument
	Dim oUOM As UnitsOfMeasure = ThisApplication.UnitsOfMeasure
	If TypeOf oDoc Is AssemblyDocument Then
		Dim oADoc As AssemblyDocument = oDoc
		Dim sPath As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
		Dim sName As String = "Sheet Metal List"
		Dim sFullNameExcel As String = sPath & "\" & sName & ".xlsx"
		Dim sListMaterial As New List(Of String)
		sListMaterial.AddRange(GetMaterialFromAssembly(oADoc.AllReferencedDocuments, oUOM))
		If sListMaterial.Count <> 0 Then
			sListMaterial.Sort()
			Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
			Dim oBOMView As BOMView = GetBOM_OnlyParts(oADef.BOM)
			If oBOMView Is Nothing Then Exit Sub
			Dim sNameAndQty As New List(Of String)
			oExcel = CreateObject("Excel.Application")
			If Not System.IO.File.Exists(sFullNameExcel) Then
				oExcel.Visible = False
				excelWorkbook = oExcel.Workbooks.Add
				ExcelSheet = excelWorkbook.Worksheets(1)
				ExcelSheet.Name = "Sheet1"
				excelWorkbook.SaveAs(sFullNameExcel)
				excelWorkbook.Close
				oExcel.Quit
			End If
			excelWorkbook = oExcel.Workbooks.Open(sFullNameExcel)
			oSheet = excelWorkbook.Worksheets(1)
			oSheet.Cells.ClearContents
			For iMater As Integer = 1 To sListMaterial.Count Step 1
				oSheet.Cells(1, iMater).Value = sListMaterial.Item(iMater - 1)
				sNameAndQty.AddRange(GetFullNameAndQty(oBOMView.BOMRows, sListMaterial.Item(iMater-1), oUOM))
				sNameAndQty.Sort()
				If sNameAndQty.Count <> 0 Then
					For iName As Integer = 1 To sNameAndQty.Count Step 1
						oSheet.Cells(iName+1, iMater).Value = sNameAndQty.Item(iName-1)
					Next iName
				End If
				sNameAndQty.Clear()
			Next iMater
			oSheet.Columns.AutoFit()
			excelWorkbook.Save()
			excelWorkbook.Close()
		End If
	Else
		MessageBox.Show("Active document is not AssemblyDocument", "Error!",MessageBoxButtons.OK,MessageBoxIcon.Error)
	End If
End Sub

Private Function GetBOM_OnlyParts(oBOM As BOM) As BOMView
	Dim sLanguageBOM As String
	If Not oBOM.PartsOnlyViewEnabled Then oBOM.PartsOnlyViewEnabled = True
	Select Case ThisApplication.LanguageCode
	Case "en-US"
		sLanguageBOM = "Parts Only"
	End Select
	Return oBOM.BOMViews.Item(sLanguageBOM)
End Function

Private Function GetFullNameAndQty(ByVal oBOMRows As BOMRowsEnumerator, ByVal sMatName As String, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sNameAndQty As New List(Of String)
	For Each oRow As BOMRow In oBOMRows
		If TypeOf oRow.ComponentDefinitions.Item(1) Is SheetMetalComponentDefinition Then
			Dim oSheetDef As SheetMetalComponentDefinition = oRow.ComponentDefinitions.Item(1)
			Dim oPartDoc As PartDocument = oSheetDef.Document
			Dim sPartNumb As String = oPartDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
			Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
			Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
			If sMaterial & " " & dThick & "mm" = sMatName Then
				sNameAndQty.Add("6-01" & sPartNumb & "_" & oRow.ItemQuantity & "Qty")
			End If
		End If
	Next
	Return sNameAndQty
End Function

Private Function GetMaterialFromAssembly(ByVal oRefDocs As DocumentsEnumerator, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sListMaterial, sListFinal As New List(Of String)
	For Each oRefDoc As Document In oRefDocs
		If TypeOf oRefDoc Is PartDocument Then
			Dim oPartDoc As PartDocument = oRefDoc
			If TypeOf oPartDoc.ComponentDefinition Is SheetMetalComponentDefinition Then
				Dim oSheetDef As SheetMetalComponentDefinition = oPartDoc.ComponentDefinition
				Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
				Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
				If Not sListMaterial.Contains(sMaterial & " " & dThick & "mm") Then
					sListMaterial.Add(sMaterial & " " & dThick & "mm")
				End If
			End If
		End If
	Next
	Return sListMaterial
End Function

[byzkc54]

Thank you Mr Andrii.
Your code is working perfectly. But i just have one more question. I wonder if you can make it work when there is a suppress part in the assembly?
Because when I have a suppress part in my assembly, your code does not run.

[Andrii_Humeniuk]

No problem, try this code:

Sub main
	Dim oDoc As Document = ThisApplication.ActiveDocument
	Dim oUOM As UnitsOfMeasure = ThisApplication.UnitsOfMeasure
	If TypeOf oDoc Is AssemblyDocument Then
		Dim oADoc As AssemblyDocument = oDoc
		Dim sPath As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
		Dim sName As String = "Sheet Metal List"
		Dim sFullNameExcel As String = sPath & "\" & sName & ".xlsx"
		Dim sListMaterial As New List(Of String)
		sListMaterial.AddRange(GetMaterialFromAssembly(oADoc.AllReferencedDocuments, oUOM))
		If sListMaterial.Count <> 0 Then
			sListMaterial.Sort()
			Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
			Dim oBOMView As BOMView = GetBOM_OnlyParts(oADef.BOM)
			If oBOMView Is Nothing Then Exit Sub
			Dim sNameAndQty As New List(Of String)
			oExcel = CreateObject("Excel.Application")
			If Not System.IO.File.Exists(sFullNameExcel) Then
				oExcel.Visible = False
				excelWorkbook = oExcel.Workbooks.Add
				ExcelSheet = excelWorkbook.Worksheets(1)
				ExcelSheet.Name = "Sheet1"
				excelWorkbook.SaveAs(sFullNameExcel)
				excelWorkbook.Close
				oExcel.Quit
			End If
			excelWorkbook = oExcel.Workbooks.Open(sFullNameExcel)
			oSheet = excelWorkbook.Worksheets(1)
			oSheet.Cells.ClearContents
			For iMater As Integer = 1 To sListMaterial.Count Step 1
				oSheet.Cells(1, iMater).Value = sListMaterial.Item(iMater - 1)
				sNameAndQty.AddRange(GetFullNameAndQty(oBOMView.BOMRows, sListMaterial.Item(iMater-1), oUOM))
				sNameAndQty.Sort()
				If sNameAndQty.Count <> 0 Then
					For iName As Integer = 1 To sNameAndQty.Count Step 1
						oSheet.Cells(iName+1, iMater).Value = sNameAndQty.Item(iName-1)
					Next iName
				End If
				sNameAndQty.Clear()
			Next iMater
			oSheet.Columns.AutoFit()
			excelWorkbook.Save()
			excelWorkbook.Close()
		End If
	Else
		MessageBox.Show("Active document is not AssemblyDocument", "Error!",MessageBoxButtons.OK,MessageBoxIcon.Error)
	End If
End Sub

Private Function GetBOM_OnlyParts(oBOM As BOM) As BOMView
	Dim sLanguageBOM As String
	If Not oBOM.PartsOnlyViewEnabled Then oBOM.PartsOnlyViewEnabled = True
	Select Case ThisApplication.LanguageCode
	Case "en-US"
		sLanguageBOM = "Parts Only"
	End Select
	Return oBOM.BOMViews.Item(sLanguageBOM)
End Function

Private Function GetFullNameAndQty(ByVal oBOMRows As BOMRowsEnumerator, ByVal sMatName As String, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sNameAndQty As New List(Of String)
	For Each oRow As BOMRow In oBOMRows
		If oRow.ItemQuantity <> 0 Then
			If TypeOf oRow.ComponentDefinitions.Item(1) Is SheetMetalComponentDefinition Then
				Dim oSheetDef As SheetMetalComponentDefinition = oRow.ComponentDefinitions.Item(1)
				Dim oPartDoc As PartDocument = oSheetDef.Document
				Dim sPartNumb As String = oPartDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
				Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
				Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
				If sMaterial & " " & dThick & "mm" = sMatName Then
					sNameAndQty.Add("6-01" & sPartNumb & "_" & oRow.ItemQuantity & "Qty")
				End If
			End If
		End if
	Next
	Return sNameAndQty
End Function

Private Function GetMaterialFromAssembly(ByVal oRefDocs As DocumentsEnumerator, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sListMaterial, sListFinal As New List(Of String)
	For Each oRefDoc As Document In oRefDocs
		If TypeOf oRefDoc Is PartDocument Then
			Dim oPartDoc As PartDocument = oRefDoc
			If TypeOf oPartDoc.ComponentDefinition Is SheetMetalComponentDefinition Then
				Dim oSheetDef As SheetMetalComponentDefinition = oPartDoc.ComponentDefinition
				Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
				Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
				If Not sListMaterial.Contains(sMaterial & " " & dThick & "mm") Then
					sListMaterial.Add(sMaterial & " " & dThick & "mm")
				End If
			End If
		End If
	Next
	Return sListMaterial
End Function

[byzkc54]

:(( 

[Andrii_Humeniuk · Accepted solution]

It looks like you are using an older version of 2022 Inventor. I hope the new changes will help you.

Sub main
	Dim oDoc As Document = ThisApplication.ActiveDocument
	Dim oUOM As UnitsOfMeasure = ThisApplication.UnitsOfMeasure
	If TypeOf oDoc Is AssemblyDocument Then
		Dim oADoc As AssemblyDocument = oDoc
		Dim sPath As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName)
		Dim sName As String = "Sheet Metal List"
		Dim sFullNameExcel As String = sPath & "\" & sName & ".xlsx"
		Dim sListMaterial As New List(Of String)
		Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
		Dim oBOMView As BOMView = GetBOM_OnlyParts(oADef.BOM)
		If oBOMView Is Nothing Then Exit Sub
		sListMaterial.AddRange(GetMaterialFromAssembly(oBOMView.BOMRows, oUOM))
		If sListMaterial.Count <> 0 Then
			sListMaterial.Sort()
			Dim sNameAndQty As New List(Of String)
			oExcel = CreateObject("Excel.Application")
			If Not System.IO.File.Exists(sFullNameExcel) Then
				oExcel.Visible = False
				excelWorkbook = oExcel.Workbooks.Add
				ExcelSheet = excelWorkbook.Worksheets(1)
				ExcelSheet.Name = "Sheet1"
				excelWorkbook.SaveAs(sFullNameExcel)
				excelWorkbook.Close
				oExcel.Quit
			End If
			excelWorkbook = oExcel.Workbooks.Open(sFullNameExcel)
			oSheet = excelWorkbook.Worksheets(1)
			oSheet.Cells.ClearContents
			For iMater As Integer = 1 To sListMaterial.Count Step 1
				oSheet.Cells(1, iMater).Value = sListMaterial.Item(iMater - 1)
				sNameAndQty.AddRange(GetFullNameAndQty(oBOMView.BOMRows, sListMaterial.Item(iMater-1), oUOM))
				sNameAndQty.Sort()
				If sNameAndQty.Count <> 0 Then
					For iName As Integer = 1 To sNameAndQty.Count Step 1
						oSheet.Cells(iName+1, iMater).Value = sNameAndQty.Item(iName-1)
					Next iName
				End If
				sNameAndQty.Clear()
			Next iMater
			oSheet.Columns.AutoFit()
			excelWorkbook.Save()
			excelWorkbook.Close()
		End If
	Else
		MessageBox.Show("Active document is not AssemblyDocument", "Error!",MessageBoxButtons.OK,MessageBoxIcon.Error)
	End If
End Sub

Private Function GetBOM_OnlyParts(oBOM As BOM) As BOMView
	Dim sLanguageBOM As String
	If Not oBOM.PartsOnlyViewEnabled Then oBOM.PartsOnlyViewEnabled = True
	Select Case ThisApplication.LanguageCode
	Case "en-US"
		sLanguageBOM = "Parts Only"
	End Select
	Return oBOM.BOMViews.Item(sLanguageBOM)
End Function

Private Function GetFullNameAndQty(ByVal oBOMRows As BOMRowsEnumerator, ByVal sMatName As String, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sNameAndQty As New List(Of String)
	For Each oRow As BOMRow In oBOMRows
		If oRow.ItemQuantity <> 0 Then
			If TypeOf oRow.ComponentDefinitions.Item(1) Is SheetMetalComponentDefinition Then
				Dim oSheetDef As SheetMetalComponentDefinition = oRow.ComponentDefinitions.Item(1)
				Dim oPartDoc As PartDocument = oSheetDef.Document
				Dim sPartNumb As String = oPartDoc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
				Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
				Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
				If sMaterial & " " & dThick & "mm" = sMatName Then
					sNameAndQty.Add("6-01" & sPartNumb & "_" & oRow.ItemQuantity & "Qty")
				End If
			End If
		End if
	Next
	Return sNameAndQty
End Function

Private Function GetMaterialFromAssembly(ByVal oBOMRows As BOMRowsEnumerator, ByVal oUOM As UnitsOfMeasure) As List(Of String)
	Dim sListMaterial, sListFinal As New List(Of String)
	For Each oRow As BOMRow In oBOMRows
		If oRow.ItemQuantity <> 0 Then
			If TypeOf oRow.ComponentDefinitions.Item(1) Is SheetMetalComponentDefinition Then
				Dim oSheetDef As SheetMetalComponentDefinition = oRow.ComponentDefinitions.Item(1)
				Dim oPartDoc As PartDocument = oSheetDef.Document
				Dim sMaterial As String = oPartDoc.ActiveMaterial.DisplayName
				Dim dThick As Double = oUOM.ConvertUnits(oSheetDef.Thickness.Value, "cm", "mm")
				If Not sListMaterial.Contains(sMaterial & " " & dThick & "mm") Then
					sListMaterial.Add(sMaterial & " " & dThick & "mm")
				End If
			End If
		End If
	Next
	Return sListMaterial
End Function