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
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.
Wesley Crihfield
(Not an Autodesk Employee)
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.
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
Can't find what you're looking for? Ask the community or share your knowledge.