Inventor VBA get Renumber matching ItemNumber in Excel

[tonythm]

Hello All,

I have  a example:

I have excel file data with renumber and partnumber is correct. Now, I want change Renumber matching ItemNumber in Inventor correct with data excel file.

I use Structure - FirstLevelOnly = True.

Could you please help me write some code?

Thank you.

Sub RenumberExportBOMsample()

    Dim oWkbk As Workbook
    Set oWkbk = ThisWorkbook

    Dim oSheet As Worksheet
    Set oSheet = oWkbk.ActiveSheet

    Dim oInv As Inventor.Application
    Set oInv = GetObject(, "Inventor.Application")

    Dim oDoc As Document
    Set oDoc = oInv.ActiveDocument

    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.
    Dim oAssy As AssemblyDocument
    Set oAssy = oInv.ActiveDocument
    
    Dim oCell As Range
    Dim sParamRange As String
    sParamRange = "A43:A120"
    
    ' Set a reference to the BOM
    Dim oBOM As BOM
    Set oBOM = oDoc.ComponentDefinition.BOM

    oBOM.StructuredViewEnabled = True
    oBOM.StructuredViewFirstLevelOnly = True ' Display First level
    oBOM.StructuredViewMinimumDigits = 3
    
    Dim oBOMView As BOMView
    Set oBOMView = oBOM.BOMViews.Item("Structured")
        
    Dim oRow As BOMRow
    Dim sItemNum() As String
    
    For Each oRow In oBOMView.BOMRows
        If oRow.ComponentDefinitions.Item(1).Type = kComponentDefinitionObject Then
            
    Next
End Sub

[tonythm]

It's sad that no one has responded to this.

[k14348]

Hi,

   I tried my level best still this code can be improved by using sendkeys or other methods. After running this code u have to go manage and bill of materials and select all items numbers then press control v. Hope this will be helpful.

Option Explicit

Sub RenumberExportBOMsample()

    Dim oExcel As Excel.Application
    Set oExcel = GetObject(, "Excel.Application")

    Dim oSheet As WorkSheet
    Set oSheet = oExcel.ActiveWorkbook.ActiveSheet
    Call oSheet.Sort.SortFields.Add(Key:=oSheet.Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal)
    
    With oSheet.Sort
    .Header = xlNo
    .Apply
    End With
    
    oSheet.Range("B1", oSheet.Range("B1").End(xlDown)).Copy
    
    Dim oInv As Inventor.Application
    Set oInv = GetObject(, "Inventor.Application")
    
    Dim oDoc As Document
    Set oDoc = oInv.ActiveDocument

    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.

    Dim oAsmDoc As AssemblyDocument
    Set oAsmDoc = oInv.ActiveDocument
    
    Dim oBom As Bom
    Set oBom = oAsmDoc.ComponentDefinition.Bom
    
    oBom.StructuredViewEnabled = True

    oBom.StructuredViewMinimumDigits = 3
    
    
    Dim oBomView As BOMView
    Set oBomView = oBom.BOMViews.Item("Structured")
    
    
    Call oBomView.Sort("Part Number", True)
    

    
End Sub

levanthong.eng

                   If you could able to assign shortcut for bill of materials command then using send key method just try to pop up bill of material table. now it needs to be done manually.

'Dim oCntDef As ControlDefinition
'Set oCntDef = ThisApplication.CommandManager.ControlDefinitions.Item(564)
create shortcut for this and using send key method try to popup.

Thanks in Advance

-Karth

[tonythm]

Hi @k14348 

Thank you for your interest.

Although manual, it can also be used.
Hope someone makes them run automatically is great.

[tonythm · Accepted solution]

Hi @k14348 

Good news!

I have come up with an automatic way like below.

Sub GetItemBOM()

    Dim oWkbk As Workbook
    Set oWkbk = ThisWorkbook

    Dim oSheet As Worksheet
    Set oSheet = oWkbk.ActiveSheet

    Dim oInv As Inventor.Application
    Set oInv = GetObject(, "Inventor.Application")

    Dim oDoc As Document
    Set oDoc = oInv.ActiveDocument

    ' Set a reference to the assembly document.
    ' This assumes an assembly document is active.
    Dim oAssy As AssemblyDocument
    Set oAssy = oInv.ActiveDocument
    
    Dim sParamRange As String
        sParamRange = "A43:A150"

    ' Set a reference to the BOM
    Dim oBOM As BOM
    Set oBOM = oDoc.ComponentDefinition.BOM

    oBOM.StructuredViewEnabled = True
    oBOM.StructuredViewFirstLevelOnly = True ' Display First level
    oBOM.StructuredViewMinimumDigits = 3
    
    Dim oBOMView As BOMView
    Set oBOMView = oBOM.BOMViews.Item("Structured")
    
    Dim i As Integer
    For i = 1 To oBOMView.BOMRows.Count
        Dim oRow As BOMRow
        Set oRow = oBOMView.BOMRows.Item(i)
    
        Dim oCompDef As ComponentDefinition
        Set oCompDef = oRow.ComponentDefinitions.Item(1)
    
        Dim oPartNumProperty As Property
        Set oPartNumProperty = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number")
        
        Dim oCell As Range
        For Each oCell In oSheet.Range(sParamRange)
            If oCell.Value = oPartNumProperty.Value Then
            oRow.ItemNumber = oCell.Offset(0, 1).Value
            End If
        Next
    Next
    MsgBox ("Done!")
    oWkbk.Save
End Sub