iLogic to add custom iproperties to assembly components from Excel table

[jishee]

I have written some iLogic code to update custom iproperties in an .ipt file that works fine. See below.

'Open the Excel file
doc = ThisDoc.Document
GoExcel.Open("W:\Initiatives_2019\Inventor_Workflow\iLogic_Tests\Test_Properties.xlsx", "Sheet1")

'Look at the first 1000 rows of the Excel file
'Start at row 2, since row 1 contains headings
For rowCheck = 2 To 1000
	
	'Get the filename without extension
	oFileName = ThisDoc.FileName(False)  
	'Set your delimiter
	Delimiter = InStr (oFileName, "-")
	Description = Left(oFileName, Delimiter -1)
	
	'Compare the part name to the value in column A
        If GoExcel.CellValue("A" & rowCheck) = Description Then
	   'Get the value from column E
           iProperties.Value(doc, "Custom", "Die Number") = GoExcel.CellValue("E" & rowCheck)
           'Get the value from column B
           iProperties.Value(doc, "Custom", "Finish") = GoExcel.CellValue("B" & rowCheck)      
           'Get the value from column C
           iProperties.Value(doc, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
           'Get the value From Column D
           iProperties.Value(doc, "Custom", "Alloy-Temper") = GoExcel.CellValue("D" & rowCheck)
        End If
Next

I am trying to adapt the same code to run in an assembly file and update all of the parts in the assembly from the same Excel table and always get the following error: "Argument 'Length' must be greater or equal to zero."

'Get the active assembly.
Dim oAsmDoc As AssemblyDocument
oAsmDoc = ThisApplication.ActiveDocument
		
'Get all of the referenced documents.
Dim asmDoc As AssemblyDocument
asmDoc = ThisApplication.ActiveDocument

'Iterate through the list of documents.
Dim doc As Document
For Each doc In asmDoc.AllReferencedDocuments
	
	'Check for part documents.
	If doc.DocumentType = kPartDocumentObject Then
 
		'Open the Excel file
		GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic_Tests\Unit_Assembly\Part_iProperties.xlsx", "Aluminum Parts")

		'Look at the first 1000 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 1000
	
			'Get the filename without extension
			oFileName = ThisDoc.FileName(False)  
			'Set your delimiter
			Delimiter = InStr(oFileName, "-")
			Description = Left(oFileName, Delimiter -1)
	
	    		'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description Then
		   			'Get the value from column B
           			iProperties.Value(doc, "Custom", "Finish") = GoExcel.CellValue("B" & rowCheck)      
           			'Get the value from column C
           			iProperties.Value(doc, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
		   		'Get the value From Column D
           			iProperties.Value(doc, "Custom", "Alloy-Temper") = GoExcel.CellValue("D" & rowCheck)
		   		'Get the value from column E
           			iProperties.Value(doc, "Custom", "Die Number") = GoExcel.CellValue("E" & rowCheck)
		   		'Get the value from column F
           			iProperties.Value(doc, "Custom", "Material") = GoExcel.CellValue("F" & rowCheck)
        		End If
		Next
		
	End If	
Next

 Any help is appreciated.

[J-Camper]

I saw a few small things, try this:

Sub Main
	If ThisApplication.ActiveDocumentType <> kAssemblyDocumentObject Then MessageBox.Show("This rule is designed to only work in assembly documents.", "Wrong Document Type") : Exit Sub
	'Get the active assembly.
	Dim oAsmDoc As AssemblyDocument = ThisApplication.ActiveDocument

	'Iterate through the list of documents.
	Dim doc As Document
	For Each doc In oAsmDoc.AllReferencedDocuments
		'Check for part documents.
		If doc.DocumentType = kPartDocumentObject
	 		'Get the filename without extension
			oFileName = doc.FileName(False)  
			'Set your delimiter
			Delimiter = InStr(oFileName, "-")
			Description = Left(oFileName, Delimiter - 1)
			
			'Open the Excel file
			GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic_Tests\Unit_Assembly\Part_iProperties.xlsx", "Aluminum Parts")
						
			'Look at the first 1000 rows of the Excel file
			'Start at row 2, since row 1 contains headings
			For rowCheck = 2 To 1000
	    		'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description
		   			'Get the value from column B
           			iProperties.Value(doc, "Custom", "Finish") = GoExcel.CellValue("B" & rowCheck)      
           			'Get the value from column C
           			iProperties.Value(doc, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
		   			'Get the value From Column D
           			iProperties.Value(doc, "Custom", "Alloy-Temper") = GoExcel.CellValue("D" & rowCheck)
		   			'Get the value from column E
           			iProperties.Value(doc, "Custom", "Die Number") = GoExcel.CellValue("E" & rowCheck)
		   			'Get the value from column F
           			iProperties.Value(doc, "Custom", "Material") = GoExcel.CellValue("F" & rowCheck)
        		End If
			Next
		End If	
	Next
End Sub

You had 2 assembly objects so I took one out, then I moved the filename operation outside of the rowCheck for loop because the document isn't changing between rowchecks, and finally you were referencing "ThisDoc" for your oFileName which would be the assembly document not the referenced document.

I didn't recreate an excel file to test, so let me know if it isn't working.

Just as a note, I like to put Custom iProperty checks under a Try/Catch just in case the iProperty gets deleted, but I didn't change that part of your code.

[jishee]

I now get the following error when trying to run it. "Public member 'FileName' on type 'PartDocument' not found."

[J-Camper]

Replace the oFileName line:

Sub Main
	If ThisApplication.ActiveDocumentType <> kAssemblyDocumentObject Then MessageBox.Show("This rule is designed to only work in assembly documents.", "Wrong Document Type") : Exit Sub
	'Get the active assembly.
	Dim oAsmDoc As AssemblyDocument = ThisApplication.ActiveDocument

	'Iterate through the list of documents.
	Dim doc As Document
	For Each doc In oAsmDoc.AllReferencedDocuments
		'Check for part documents.
		If doc.DocumentType = kPartDocumentObject
	 		'Get the filename without extension
			oFileName = Left(doc.FullFileName.Remove(0, doc.FullFileName.LastIndexOf("\") + 1), (doc.FullFileName.Remove(0, doc.FullFileName.LastIndexOf("\") + 1).Length - 4))  
			'Set your delimiter
			Delimiter = InStr(oFileName, "-")
			Description = Left(oFileName, Delimiter - 1)
			
			'Open the Excel file
			GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic_Tests\Unit_Assembly\Part_iProperties.xlsx", "Aluminum Parts")
						
			'Look at the first 1000 rows of the Excel file
			'Start at row 2, since row 1 contains headings
			For rowCheck = 2 To 1000
	    		'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description
		   			'Get the value from column B
           			iProperties.Value(doc, "Custom", "Finish") = GoExcel.CellValue("B" & rowCheck)      
           			'Get the value from column C
           			iProperties.Value(doc, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
		   			'Get the value From Column D
           			iProperties.Value(doc, "Custom", "Alloy-Temper") = GoExcel.CellValue("D" & rowCheck)
		   			'Get the value from column E
           			iProperties.Value(doc, "Custom", "Die Number") = GoExcel.CellValue("E" & rowCheck)
		   			'Get the value from column F
           			iProperties.Value(doc, "Custom", "Material") = GoExcel.CellValue("F" & rowCheck)
        		End If
			Next
		End If	
	Next
End Sub

It's long-hand but works. 

I also noticed that the Delimiter will throw the "Length..." error you mentioned before for any part filename that doesn't have any "-" in it, so you might want to think about adding a condition to catch that.

[jishee]

@J-Camper thank you for your help. I understand the first set of changes you made to the code. The second change ith the FileName is a bit beyond my level. I made the change though and still get the "Argument 'Length' must be greater or equal to zero" error. All of the part file names have the "-" in them as you mentioned.

[J-Camper]

The oFileName change only looks complicated because I wrote it all as one line of code.  If you break it up like this it may be easier to understand:

oFileName = doc.FullFileName 'reference document file location
oFileName = oFileName.Remove(0,oFileName.LastIndexOf("\") + 1) 'remove all folders from string
oFileName = Left(oFileName, oFileName.Length-4) 'remove file extension [.ipt or .iam]

Just so I don't have to recreate an excel file and parts with custom properties to match yours, can you add this is in a few places to narrow down the issue:

MessageBox.Show("Passes This Point.", "Debug 1")

Change the Debug number for each place you add it, so it's easy to identify.

Unless you have a sample Excel file/assembly with parts you can post [DO NOT POST ANYTHING CONFIDENTIAL] so I can debug from here.

[jishee]

@J-Camper It gets to Debug 3 then errors out.

Sub Main
	If ThisApplication.ActiveDocumentType <> kAssemblyDocumentObject Then MessageBox.Show("This rule is designed to only work in assembly documents.", "Wrong Document Type") : Exit Sub
	'Get the active assembly.
	Dim oAsmDoc As AssemblyDocument = ThisApplication.ActiveDocument

	'Iterate through the list of documents.
	Dim doc As Document
	For Each doc In oAsmDoc.AllReferencedDocuments
		'Check for part documents.
		If doc.DocumentType = kPartDocumentObject
	 		'Get the filename without extension
			oFileName = Left(doc.FullFileName.Remove(0, doc.FullFileName.LastIndexOf("\") + 1), (doc.FullFileName.Remove(0, doc.FullFileName.LastIndexOf("\") + 1).Length - 4))  
			'Set your delimiter
			Delimiter = InStr(oFileName, "-")
			Description = Left(oFileName, Delimiter - 1)
			MessageBox.Show("Passes This Point.", "Debug 1")
			'Open the Excel file
			GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic_Tests\Unit_Assembly\Part_iProperties.xlsx", "Aluminum Parts")
						
			'Look at the first 10 rows of the Excel file
			'Start at row 2, since row 1 contains headings
			For rowCheck = 2 To 10
				MessageBox.Show("Passes This Point.", "Debug 2")
	    		'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description Then
					MessageBox.Show("Passes This Point.", "Debug 3")
					'Get the value from column B
           			iProperties.Value(doc, "Custom", "Finish") = GoExcel.CellValue("B" & rowCheck)      
           			'Get the value from column C
           			iProperties.Value(doc, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
		   			'Get the value From Column D
           			iProperties.Value(doc, "Custom", "Alloy-Temper") = GoExcel.CellValue("D" & rowCheck)
		   			'Get the value from column E
           			iProperties.Value(doc, "Custom", "Die Number") = GoExcel.CellValue("E" & rowCheck)
		   			'Get the value from column F
           			iProperties.Value(doc, "Custom", "Material") = GoExcel.CellValue("F" & rowCheck)
        		End If
			Next
		End If	
	Next
End Sub

 I have attached a zip file with all the parts and the spreadsheet.