Read Excel sheet into iProperties, then BOM

[Anonymous]

Long story shorter, I am not an Inventor user. This is done per the request of a co-worker who uses Inventor (2019). He wants to me write some code to compare an iProperty "Stock Number" to an excel sheet, with a column A "Purchased Item". These two (Part Number and Purchased Item) contain the exact same information. What I need to do is compare both of these values, and if they match, pull from Column B "Internal Part Number" and import it into inventor as a new custom column in the BOM.

What I have so far as far as code, is to pull the values from the Excel sheet, and import them as a new iProperty, but for some reason, it only populates the iProperty for the overall Assembly, not the Part files . I was using this as a starting point, since I was unsure if you could directly affect the BOM. From my limited knowledge of how this software works, I assume the BOM pulls data from each individual part, so I figured if I updated the iProperties of the parts, I could go from there and pull them into the BOM.

Dim oProj As String
Dim oSheet As String

oProj = iProperties.Value("Project", "Stock Number")

GoExcel.Open("C:\Users\PATHHERE","EXCELSHEETNAME")

For rowPR = 2 To 2000
	If (GoExcel.CellValue("A" & rowPR) = oProj) Then
		oSheet = GoExcel.CellValue("B" & rowPR)
		
		iProperties.Value("Custom", "Internal Part Number") = oSheet
		Exit For
	End If
	Next
	GoExcel.Close
		

[Neuzzo]

Change excel sheet name on "BoM" then save.

When run choose in a dialog the excel file you want to use.

Sub Main ()
'Create variables'set a reference to the assembly component definintion
Dim oAssDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim ExcelFullName As String
Dim FileName As String 


Dim oFileDlg As Inventor.FileDialog = Nothing
InventorVb.Application.CreateFileDialog(oFileDlg)
oFileDlg.InitialDirectory = oOrigRefName
oFileDlg.CancelError = True

oFileDlg.ShowOpen()
If Err.Number <> 0 Then
Return
ElseIf oFileDlg.FileName <> "" Then
ExcelFullName = oFileDlg.FileName
End If

'Open Excel database
GoExcel.Open(ExcelFullName,"BoM")

'Iterate through each referenced document'Dim oOcc As ComponentOccurrence
    For Each oDoc As Document In oAssDoc.AllReferencedDocuments
    ErHa = "Start"
    Try
        'Extract Part Number of active occurrence 
        Dim oPropSet As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
        oPartNumber = Left(oDoc.DisplayName, Len(oDoc.DisplayName) - 4) '(Left(oDoc.DisplayName) -4)
                    

    ErHa = "Define custom property collection"
        Parameter.UpdateAfterChange = True       
        i = GoExcel.FindRow(ExcelFullName, "BoM", "Purchased Item", "=", oPartNumber)
		
		If i = "-1" 
			GoTo PROSSIMO
		End If
		
		Dim PurchasedItem As String = GoExcel.CurrentRowValue("Purchased Item")
		Dim InternalPartNumber As String = GoExcel.CurrentRowValue("Internal Part Number")
        If oPartNumber = PurchasedItem Then
        GetProperty(oPropSet, "Stock Number").Value = InternalPartNumber
        Else
        Goto Prossimo
        End If
		

    ErHa = "Update the file"
        iLogicVb.UpdateWhenDone = True
        Catch ex As Exception
        MsgBox("Part: " & oDoc.DisplayName & vbLf & "Code-Part: " & ErHa & vbLf & "Error: " & ex.Message)  
    
    End Try
PROSSIMO:
    Next 
	
'Close Excel database
GoExcel.Close

End Sub

Private ErHa As String = vbNullString

Function GetProperty(oPropset As PropertySet, iProName As String) As Inventor.Property
    ErHa = "GetProperty: " & iProName
    Dim iPro As Inventor.Property
    Try
        'Attempt to get the iProperty from the document
        iPro = oPropset.Item(iProName)
    Catch
        'Assume error means not found, so create it
        iPro = oPropset.Add("", iProName)
    End Try
    Return iPro
End Function

I'm not on inventor now, try it

[Anonymous]

Using your code, it generates the error

Code-Part "Define custom property collection"

Error "Object reference not set to an instance of an object." for all parts.

Im looking through it now, Im wondering if something isn't initialized or along those lines.