Bill of Materials Excel Export Options

[mat_hijs]

I recently found out that you can export the BOM using a template, I tested this a little and ended up with a template that has some formulas and a certain formatting that I'd like to use. When exporting manually this seems to work fine. I'd like to be able to do this with iLogic so I can do this in batch and don't have to go through the settings every time, but I can't seem to find this option.

Is this not supported by the API (yet)?

[WCrihfield]

Hi @mat_hijs.  I don't recall seeing that option available through the API for an assembly BOMView.Export to Excel.  However, the very similar PartsList.Export does allow a way to do that.  I think most folks have been using the BOM.ExportBOMCustomization & BOM.ImportBOMCustomization to handle some of the customization, then creating and using their own custom export method codes for the main export task.  That way they can use whatever template they want and transfer the data whichever way they want.

[bradeneuropeArthur]

What properties do you prefer to export?
I have a code doing this.
I could change it to your needs!

[mat_hijs]

I would like to export the following properties in this order: Item; Part Number; QTY; Description; Veredeling (Custom); Hegge nr. (Custom); Profiel nr. (Custom); Lengte (Custom); Stuiklengte (Custom); Component Type; Filename

I also attached my Excel-file.

[mat_hijs]

Could you send me the code you were talking about? If it's changed to my needs, that's great. If not, I think I should be able to figure out how to change it myself.

[A.Acheson]

The BOM export method unfortunately does not offer an option to use a template directly. So indirectly you can just do a copy of the data in excel. To get the columns of data you want use the  ImportBomCustomization which will filter the bom export per a saved xml of the bom view. Then do your export to an excel sheet and then simply copy the excel sheet values to your template using either an excel macro or through inventor.  Here is a link discussing a method. 

[bradeneuropeArthur]

Public Sub main
	BomPO
	BOMStr
	End Sub
	
Public Sub BomPO
Dim a As Inventor.AssemblyDocument = ThisDoc.Document
Dim b As Inventor.BOM = a.ComponentDefinition.BOM
Dim d As Inventor.Document = b.BOMViews.Item(1).BOMRows.Item(1).ComponentDefinitions.Item(1).Document'.ReferencedFileDescriptor.FullFileName

oWrite = System.IO.File.CreateText(ThisDoc.PathAndFileName(False)& " Parts Only " &Today.Year & Today.Month & Today.Day & ".csv")
oWrite.WriteLine("Item" & ";" & "Part Number" & ";" & "Qty" & ";" & "Description"  & ";" & "AAA" & ";" & "BBB" & ";" & "Flat Pattern Area" & ";" & "Flat Pattern Length" & ";" & "Flat Pattern Width" & ";"  & "Flat Pattern Thickness" & ";"  & "Be_Weight")


Dim pnr As Inventor.Property
Dim flta As Inventor.Property


For Each b1 As Inventor.BOMRow In b.BOMViews.Item(3).BOMRows
	Dim d1 As Inventor.Document = b1.ComponentDefinitions.Item(1).Document
	Try

		
		oWrite.WriteLine(b1.ItemNumber & " ; " _
		& d1.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value _
		& " ; " & b1.ItemQuantity _
		& " ; " & d1.PropertySets.Item("Design Tracking Properties").Item("Description").Value _
		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("AAA").Value _
		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("BBB").Value _
		& " ; " & replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Area").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Length").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Width").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("User Defined Properties").Item("Mfp_Thickness").Value,".",",") _
		& " ; "& Replace( d1.ComponentDefinition.MassProperties.Mass,".",","))

		Catch
			
			End Try
	
	Next
	
oWrite.Close()

ThisDoc.Launch(ThisDoc.PathAndFileName(False)& " Parts Only " &Today.Year & Today.Month & Today.Day & ".csv")
End Sub

Public Sub BOMStr
Dim a As Inventor.AssemblyDocument = ThisDoc.Document
Dim b As Inventor.BOM = a.ComponentDefinition.BOM
Dim d As Inventor.Document = b.BOMViews.Item(1).BOMRows.Item(1).ComponentDefinitions.Item(1).Document'.ReferencedFileDescriptor.FullFileName

oWrite = System.IO.File.CreateText(ThisDoc.PathAndFileName(False)& " Structured " &Today.Year & Today.Month & Today.Day & ".csv")
oWrite.WriteLine("Item" & ";" & "Part Number" & ";" & "Qty" & ";" & "Description"  & ";" & "AAA" & ";" & "BBB" & ";" & "Flat Pattern Area" & ";" & "Flat Pattern Length" & ";" & "Flat Pattern Width" & ";"  & "Flat Pattern Thickness" & ";"  & "Be_Weight")


Dim pnr As Inventor.Property
Dim flta As Inventor.Property

'Dim x As String = Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Area").Value,".",",")
For Each b1 As Inventor.BOMRow In b.BOMViews.Item(2).BOMRows
	Dim d1 As Inventor.Document = b1.ComponentDefinitions.Item(1).Document
	Try

		
		oWrite.WriteLine(b1.ItemNumber & " ; " _
		& d1.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value _
		& " ; " & b1.ItemQuantity _
		& " ; " & d1.PropertySets.Item("Design Tracking Properties").Item("Description").Value _
		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("AAA").Value _
		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("BBB").Value _
		& " ; " & replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Area").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Length").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Width").Value,".",",") _
		& " ; " & Replace(d1.PropertySets.Item("User Defined Properties").Item("Mfp_Thickness").Value,".",",") _
		& " ; "& Replace( d1.ComponentDefinition.MassProperties.Mass,".",","))

		Catch
			
			End Try
	
	Next
	
oWrite.Close()

ThisDoc.Launch(ThisDoc.PathAndFileName(False)& " Structured " &Today.Year & Today.Month & Today.Day & ".csv")
End Sub

[mat_hijs]

This code doesn't seem to be working like it should, both subs create only a header and then seems to go straight to the catch which is nothing. Aside from that I don't really understand how this would be helpful to me, I was looking for a way to export a BOM to a template so the formulas in that template would be applied to every row. I don't see anything that has to do with a template or formulas in this rule so I don't know what more I would get from using this rule than from simply exporting the BOM straight to an XLSX file?

[bradeneuropeArthur]

Could you send the template which should be used for the output?

We can update this piece of code to do that also to a template via excel.

via csv file was for me the quickest.

Regards,

[mat_hijs]

I already posted the template to this thread.

[bradeneuropeArthur]

This will help you in the right direction I hope:

AddReference "Microsoft.Office.Interop.Excel.dll"
Public Sub Main
	BomPO
	'BOMStr
	End Sub
	
Public Sub BomPO
Dim a As Inventor.AssemblyDocument = ThisDoc.Document
Dim b As Inventor.BOM = a.ComponentDefinition.BOM
Dim d As Inventor.Document = b.BOMViews.Item(1).BOMRows.Item(1).ComponentDefinitions.Item(1).Document'.ReferencedFileDescriptor.FullFileName

Dim ex As New Microsoft.Office.Interop.Excel.ApplicationClass
MsgBox (ex.Name)
Dim wb As Microsoft.Office.Interop.Excel.Workbook = ex.Workbooks._Open("C:.....\BOM Template.xlsx")
Dim ws As Microsoft.Office.Interop.Excel.Worksheet = wb.Sheets.Item("BOM")

Dim pnr As Inventor.Property
Dim flta As Inventor.Property
Dim doc As Inventor.Document
Dim i As Integer = 1

For Each b1 As Inventor.BOMRow In b.BOMViews.Item(3).BOMRows
	
	MsgBox (b1.ComponentDefinitions.Count)
	doc = b1.ComponentDefinitions.Item(1).Document
	MsgBox (doc.DisplayName)
	Try
		 
		i =i+1
		MsgBox (ws.Range("A" & i).Value)
ws.Range("A" & i).Value = b1.ItemNumber
ws.Range("B" & i).Value = doc.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value
ws.Range("C" & i).Value = b1.ItemQuantity 	
ws.Range("D" & i).Value = doc.PropertySets.Item("Design Tracking Properties").Item("Description").Value
's.Range("E" & i).Value =d1.PropertySets.Item("User Defined Properties").Item("Verdeling").Value


		Catch
			
			End Try
	
	Next

wb.Save
wb.close

End Sub

'Public Sub BOMStr
'Dim a As Inventor.AssemblyDocument = ThisDoc.Document
'Dim b As Inventor.BOM = a.ComponentDefinition.BOM
'Dim d As Inventor.Document = b.BOMViews.Item(1).BOMRows.Item(1).ComponentDefinitions.Item(1).Document'.ReferencedFileDescriptor.FullFileName

'oWrite = System.IO.File.CreateText(ThisDoc.PathAndFileName(False)& " Structured " &Today.Year & Today.Month & Today.Day & ".csv")
'oWrite.WriteLine("Item" & ";" & "Part Number" & ";" & "Qty" & ";" & "Description"  & ";" & "AAA" & ";" & "BBB" & ";" & "Flat Pattern Area" & ";" & "Flat Pattern Length" & ";" & "Flat Pattern Width" & ";"  & "Flat Pattern Thickness" & ";"  & "Be_Weight")


'Dim pnr As Inventor.Property
'Dim flta As Inventor.Property

''Dim x As String = Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Area").Value,".",",")
'For Each b1 As Inventor.BOMRow In b.BOMViews.Item(2).BOMRows
'	Dim d1 As Inventor.Document = b1.ComponentDefinitions.Item(1).Document
'	Try

		
'		oWrite.WriteLine(b1.ItemNumber & " ; " _
'		& d1.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value _
'		& " ; " & b1.ItemQuantity _
'		& " ; " & d1.PropertySets.Item("Design Tracking Properties").Item("Description").Value _
'		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("AAA").Value _
'		& " ; " & d1.PropertySets.Item("User Defined Properties").Item("BBB").Value _
'		& " ; " & replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Area").Value,".",",") _
'		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Length").Value,".",",") _
'		& " ; " & Replace(d1.PropertySets.Item("Design Tracking Properties").Item("Flat Pattern Width").Value,".",",") _
'		& " ; " & Replace(d1.PropertySets.Item("User Defined Properties").Item("Mfp_Thickness").Value,".",",") _
'		& " ; "& Replace( d1.ComponentDefinition.MassProperties.Mass,".",","))

'		Catch
			
'			End Try
	
'	Next
	
'oWrite.Close()

'ThisDoc.Launch(ThisDoc.PathAndFileName(False)& " Structured " &Today.Year & Today.Month & Today.Day & ".csv")
'End Sub