Export BOM virtual components to Excel

[esaldana]

I'm converting all my hardware (bolts, nuts, etc.) into virtual components, once all are installed in the context of the model I would like to be able to export them to Excel for final totals.

Is there any code that I could use to do this task?

basically from the top-level assembly be able to read all the virtual components present in the model and export them to Excel.

Later I would like to incorporate these rows into a shipping document with the right amount of hardware needed.

But for now, only knowing the totals would be really of great help.

Any help would be appreciated.

Thanks,

Eric

[WCrihfield]

Hi @esaldana.  You may have to explain the details of this request with more specific information.  It sounds like you want to export only the virtual components, and not any other types of components.  Is that correct?  If so, then only the virtual components found at the top level of the assembly, or all virtual components from within all levels of the assembly?  What are you expecting as the result of running this rule (in as much detail as possible)?  We have no idea what columns or what information about each virtual component that you need to see in the Excel file.  How should the data be laid out in the spreadsheet.  It might also be helpful if you posted an example of what the result should look like.  I do not believe there is a built-in filter to only export virtual components when exporting the BOMView, so the code would have to be very custom, and may need to write individual pieces of data to the Excel sheet one cell at a time in this code process.

[esaldana]

Thanks for your reply,

You are right, I would like only the virtual components exported, wherever they could be found including top and sub-assemblies, and get their Part No, Description, and Quantity. (and mass if possible)

The output in Excel could be like the one in the attached picture, it would be a tremendous first step for us. (with this Excel list we will be able to see the totals of each virtual component needed)

My optimal solution would be to export these virtual components with a column indicating to which assemblies they belong, with a big final total for all of them. (But this could be for another day 🙂 )

I'm so bad in trying to explain it.  I Hope this helps.

[ryan.rittenhouse]

This should get you in the neighborhood. It's a quick and dirty iLogic rule that will run through an entire assembly, build out a dictionary of the virtual parts it finds, then export everything to an excel sheet. There are better ways to write it, but this seemed like the easiest for you to edit for your needs. Is this what you're looking for?

Sub Main()
	
	'Dictionary of virtual parts w/ the partNumber as the key
	Dim partList As New Dictionary(Of String, Object)
	
	'Dictionary of virtual part info - currently qty and description
	Dim part As Dictionary(Of String, Object)
	
	'Loop through all individual parts
	For Each comp As ComponentOccurrence In ThisDoc.Document.ComponentDefinition.Occurrences.AllLeafOccurrences
		If Not comp.Suppressed AndAlso TypeOf comp.Definition Is VirtualComponentDefinition Then
			Dim partNumber As String = comp.Definition.PropertySets("Design Tracking Properties")("Part Number").Value
			'If we already have the part information, increment the quantity
			If partList.ContainsKey(partNumber) Then	
				partList(partNumber)("qty") += 1
			Else 'Gather and add part information
				part = New Dictionary(Of String, Object)
				part.Add("qty", 1)
				part.Add("description", comp.Definition.PropertySets("Design Tracking Properties")("Description").Value)
				partList.Add(partNumber, part)
			End If
		End If
	Next comp
	
	'Exit If we have no virtual parts	
	If partList.Count < 1 Then
		MessageBox.Show("No virtual parts found in active assembly")
		Exit Sub
	End If
	
	Dim outputFile As String = ThisDoc.Path & "\Virtual Part List.xlsx"
	Dim excelApp As Object = CreateObject("Excel.Application")
	Dim excelWorkbook As Object
	excelApp.Visible = False
	excelApp.DisplayAlerts = False

	If Dir(outputFile) <> "" Then
		excelWorkbook = excelApp.Workbooks.Open(outputFile)
		excelWorkbook.Worksheets(1).activate
	Else
		excelWorkbook = excelApp.Workbooks.Add
	End If
	
	With excelApp
		
		.Range("A1").Value = "Part Number"
		.Range("B1").Value = "Quantity"
		.Range("C1").Value = "Description"
		
		Dim row As Integer = 2		
		For Each virtualPart As KeyValuePair(Of String, Object) In partList
			.Range("A" & row).Value = virtualPart.Key
			.Range("B" & row).Value = virtualPart.Value("qty")
			.Range("C" & row).Value = virtualPart.Value("description")
			row += 1
		Next virtualPart
		
	End With

	excelApp.Columns.AutoFit  
	excelWorkbook.SaveAs(outputFile)
	excelWorkbook.Close
	excelApp.Quit
	excelApp = Nothing	
	
End Sub 'Main