Read Excel sheet into iProperties, then populate 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 need it to iterate through all the part files, and compare the two values and then pull from the excel sheet for only those that match. 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. That would be the second question Ill ask later.

Is there an easy way to iterate through the part files and then compare them using some of the code I wrote below? Or is it something else entirely. 

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
		

[j.brodersen]

Hi araisdemlant

I'm still a beginner in programming so I'm not 100 % sure but I think following code could work if it's run as ILogic-rule from the assembly

Dim oProj As String
Dim oSheet As String

InvDoc = ThisDoc.Document

 Dim refDocs As DocumentsEnumerator = InvDoc.AllReferencedDocuments

Dim refDoc As Document

Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = InvDoc.ComponentDefinition
		
Dim oOcc As ComponentOccurrence


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

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


For Each refDoc In refDocs

For rowPR = 2 To 2000
	If (GoExcel.CellValue("A" & rowPR) = oProj) Then
		oSheet = GoExcel.CellValue("B" & rowPR)
	
For Each oOcc In oAsmCompDef.Occurrences
		Try
		
		iProperties.Value(oOcc.Name, "Project", "Part Number") = oSheet
Catch 
Next

		Exit For
	End If
	Next
	GoExcel.Close

At least following code changes the part no if I save the file as a copy with new extension

[...]

 For Each refDoc In refDocs

		If refDoc.DisplayName.EndsWith(".ipt") = True Then
         refDoc.DisplayName = refDoc.DisplayName.substring(0, refDoc.DisplayName.Length - 4)
		 End If

    NewFileName =  refDoc.DisplayName + ext
	

	For Each oOcc In oAsmCompDef.Occurrences
		Try
			iProperties.Value(oOcc.Name, "Project", "Part Number")= NewFileName
			Catch
		End Try
	Next 
[...]

Next

Hope that this will help you or gives you at least help towards the solution

Regards

Johann