How to count the number of same part numbers in an assembly

[Anonymous]

Hi all, 

I am trying to read part numbers from an assembly and write the part number and quantity into excel. currently I have this code:

Excel_file = "R:\Customer Satisfaction\Department Access\Bus Tech Info\Cody Zorn\Mapics Automation\group_test.xlsx"

part_number = iProperties.Value("Project", "Part Number")
part_type = parameter("PartType")

GoExcel.Open(Excel_file, "sheet1")	
RowStart = 2
RowEnd = 100
For count = RowStart To RowEnd
	If String.IsNullOrEmpty(GoExcel.CellValue("B" & count)) Then 
		i = i + 1
	End If
Next
EmptyRow = RowEnd - i + 1	

GoExcel.CellValue(Excel_file, "sheet1", "B" & EmptyRow) = part_number
GoExcel.CellValue(Excel_file, "sheet1", "C" & EmptyRow) = part_type
GoExcel.Save

This will correctly write a part number to excel. However, I am struggling to figure out how to add the quantity of each part in the assembly.

For example i want to write the following into excel:

Part A qty. 4

Part B qty. 5

Part C qty. 2

...

 I also have the following rule to iterate through every part of the assembly and run the code above:

Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
For Each oRefDoc As Document In oADoc.AllReferencedDocuments
	If oRefDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
		Dim oPDoc As PartDocument = oRefDoc
		Dim oArgs As NameValueMap = ThisApplication.TransientObjects.CreateNameValueMap
		oArgs.Add("TargetDoc", oPDoc.FullDocumentName)
		iLogicVb.Automation.RunExternalRuleWithArguments(oPDoc, "Group", oArgs)
	End If
Next

Any ideas on how to do this?

[A.Acheson]

The best way would be to leverage the BOM. Either export it line by line and column by column, here is an example

https://forums.autodesk.com/t5/inventor-ilogic-api-vba-forum/ilogic-code-to-export-bom-to-excel-file...

or bulk export it similar to the manual export tool one example of many

https://forums.autodesk.com/t5/inventor-ilogic-api-vba-forum/easy-export-bom-to-excel-with-template-...

To help you to pick the best method it would be necessary to ask what further workflow you have planned with the information?  

[Anonymous]

I am trying to gather all parameter information from these parts in inventor, write it all to a excel sheet and use that excel sheet to enter parts into our database system. My iLogic rule already reads every part number and writes it to an excel sheet. There is no way to easily count the quantity of similar part numbers and return that value for each unique part?

[A.Acheson]

When you say parameter do you mean iproperties or the part parameters(like dimensions etc) ? If this is for an inventor export and data system import through excel I would think the BOM route would  be the easiest. It has the built in qty already and you can change the output with relative ease. You can export BOM using structured or parts only views and use the part number roll up to group all part numbers together. 

Pulling this information from the assembly by iterating through the documents would be a much longer route and more complex. But that would be your decision. 

[Anonymous]

Yes, the part number i am looking at comes from the iproperties, that was my mistake. I am not familiar with how to use the BOM function in an iLogic rule?  I was thinking about iterating through the assembly but if a bill of materials approach is easier I would love to hear about your idea!

[A.Acheson]

From the post shown above here it is again

https://forums.autodesk.com/t5/inventor-ilogic-api-vba-forum/ilogic-code-to-export-bom-to-excel-file...

Message 11  has this rules that loops through the BOm and returns a few columns the user specifies. You need to create an excel file in this location this is simply a place holder to dump the data and then it copies to another excel file. 

xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Test.xlsx")

Dim oDoc As AssemblyDocument
oDoc = ThisApplication.ActiveDocument

Dim oBOM As BOM
oBOM = oDoc.ComponentDefinition.BOM

oBOM.PartsOnlyViewEnabled = True

Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Parts Only")

xlApp = CreateObject("Excel.Application")

'comment out or change to false 
'in order to not show Excel
xlApp.Visible = True 
xlWorkbook = xlApp.Workbooks.Open("C:\Temp\Test.xlsx")

xlWorksheet = xlWorkbook.Worksheets.Item("Sheet1")

Dim row As Integer
row = 5

xlWorksheet.Range("B4").Value = "ITEM"
xlWorksheet.Range("C4").Value = "QTY"
xlWorksheet.Range("D4").Value = "DESC"
xlWorksheet.Range("E4").Value = "Part Number"

'Dim bRow As bomRow
bRows = oBOMView.BOMRows
For Each bRow In bRows

	Dim rDoc As Document
	rDoc = bRow.ComponentDefinitions.Item(1).Document
	
	Dim docPropertySet As PropertySet
	docPropertySet = rDoc.PropertySets.Item("Design Tracking Properties")
	
	xlWorksheet.Range("B" & row).Value = bRow.ItemNumber
	xlWorksheet.Range("C" & row).Value = bRow.ItemQuantity
	xlWorksheet.Range("D" & row).Value = docPropertySet.Item("Description").Value
	xlWorksheet.Range("E" & row).Value = docPropertySet.Item("Part Number").Value
	
	row = row + 1
Next

oNow = DateString & "_" & TimeString
oNow = oNow.Replace("/","_")
oNow = oNow.Replace(":","_")
xlWorkBook.SaveAs(Filename:="C:\Temp\SampleNew_" & oNow & ".xlsx")

xlWorkbook.Close (False)
xlApp.Quit

Message 16 has a different approach to export the complete BOM. This has no Column sorting and will simply dump all of the BOM to the excel file. You can add in some column customization with export options, there will be many more samples available on how to do this. I run a similar one to this with all BOM sorting and splitting of welding vs assembly parts in excel through excel macros. 

Create the excel File at this location

ExportBOMToExistingExcel("C:\Temp\Export All.xlsx", "BOM")

Create the Temporary excel File at this location

oTemporaryExcelFile = "C:\Temp\Test_ExportAll.xlsx"

Sub Main()
    
    ExportBOMToExistingExcel("C:\Temp\Export All.xlsx", "BOM")

End Sub

Sub ExportBOMToExistingExcel(oxlName As String, oxlSheet As String)
    'ExportBOMToExistingExcel - by MechMachineMan on Inventor Customization Forums
    'Purpose: To export a BOM to an existing excel file as a workaround to inventor's native behaviour of
    '          wiping the destination file of the export.
    'Method:  This sub will simply create a temporary excel file, and then copy the sheet to the destination document.
  
    'Change this temp file to something that works for you better, if desired. 
    'Is currently a Generic location To make it portable.
    oTemporaryExcelFile = "C:\Temp\Test_ExportAll.xlsx"
    
    Dim xlApp As Object
    Dim workingwb As Object
    
    Try    
        ExportBOMToTemp(oTemporaryExcelFile)
    
        xlApp = CreateObject("Excel.Application")
    
        workingwb = xlApp.Workbooks.Open(oxlName)
    
        sourcewb = xlApp.Workbooks.Open(oTemporaryExcelFile)
        sourcews = sourcewb.Sheets(1)
    
        sourcews.Copy(After:=workingwb.Sheets(workingwb.Sheets.Count))
        workingws = workingwb.Sheets(workingwb.Sheets.Count)
        workingws.Name = oxlSheet   
        
        sourcewb.Close()
    Catch
        MsgBox("Error During Export Encountered!")
    
    End Try
    
    Try
        xlApp.Visible = True    
        workingwb = Nothing
        xlApp = Nothing
    Catch
    End Try
    
    releaseObject(workingws)
    releaseObject(sourcewb)
    releaseObject(workingwb)
    releaseObject(xlApp)
    
End Sub

Sub ExportBOMToTemp(oTempFile As String)
    'Do not mod where tempfile appears; only mod to get parts only if desired.
    Dim oDoc As AssemblyDocument
    oDoc = ThisDoc.Document

    Dim oBOM As BOM
    oBOM = oDoc.ComponentDefinition.BOM
    
    oBOM.StructuredViewFirstLevelOnly = False
    oBOM.StructuredViewEnabled = True

    Dim oStructuredBOMView As BOMView
    oStructuredBOMView = oBOM.BOMViews.Item("Structured")
    
    oStructuredBOMView.Export(oTempFile, kMicrosoftExcelFormat)
End Sub

Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

[Anonymous]

After further reviewing, I believe that iterating through the assembly and coming up with a quantity for each part is the option that will work best for me. Do you have any ideas on how I could do this?

[JhoelForshav · Accepted solution]

Hi @Anonymous 

If you just want to get the part numbers and their quantity in excel you can run a rule like this in your assembly 🙂

Dim oExcel As String = "R:\Customer Satisfaction\Department Access\Bus Tech Info\Cody Zorn\Mapics Automation\group_test.xlsx"
GoExcel.Open(oExcel, "sheet1")
Dim oRow As Integer = 2
Dim oAsm As AssemblyDocument = ThisDoc.Document
For Each refDoc As Document In oAsm.AllReferencedDocuments
	If refDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject
		Dim oCount As Integer = oAsm.ComponentDefinition.Occurrences.AllReferencedOccurrences(refDoc).Count
		If oCount > 0
			Dim oPartNum As String = refDoc.PropertySets("{32853F0F-3444-11D1-9E93-0060B03C1CA6}")("Part Number").Value
			GoExcel.CellValue(oExcel, "sheet1", "A" & oRow) = oPartNum
			GoExcel.CellValue(oExcel, "sheet1", "B" & oRow) = "qty: " & oCount
			oRow += 1
		End If
	End If
Next
GoExcel.Save

 I originally posted a code to put the parts displaynames. It has now been changed to use their part numbers instead 🙂