iLogic add iproperties from Excel

[jishee]

I have a rule that adds custom iProperties to parts in an assembly from an Excel file. The rule runs as expected the first time it is run, however if I make a change to the Excel file and run the rule again to update the iProperties, I get an error. The rule is below. I am not advanced enough yet (but learning lots) to determine why it runs the first time but not again after changes. Thank you for any help.

oDocFile = ThisDoc.Document
Dim oAsmCompDef As AssemblyComponentDefinition
oAsmCompDef = ThisApplication.ActiveDocument.ComponentDefinition
Dim oOcc As ComponentOccurrence
Dim oDocFileName As String
Dim oFileNamePos As Long

For Each oOcc In oAsmCompDef.Occurrences
	
If Not TypeOf oOcc.Definition Is VirtualComponentDefinition Then
	
	oDocFile = oOcc.Definition.Document
	'Find position in full filename path string where component filename begins
	oFileNamePos = InStrRev(oDocFile.FullFileName, "\", - 1)
	'Strip off filename path string and set oDocFileName to just the component's file name
	oDocFileName = Mid(oDocFile.FullFileName, oFileNamePos + 1, Len(oDocFile.FullFileName) - oFileNamePos)
			'Set your delimiter
			Delimiter = InStr(oDocFileName, "-")
			Description = Left(oDocFileName, Delimiter - 1)
			
			'Open the Excel file
			GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Parts")
						
			'Look at the first 15 rows of the Excel file
			'Start at row 2, since row 1 contains headings
			For rowCheck = 2 To 15
				'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description Then
           			iProperties.Value(oDocFileName, "Custom", "Extrusion") = GoExcel.CellValue("B" & rowCheck)      
           			iProperties.Value(oDocFileName, "Custom", "Alloy-Temper") = GoExcel.CellValue("C" & rowCheck)
           			iProperties.Value(oDocFileName, "Custom", "Material") = GoExcel.CellValue("D" & rowCheck)
           			iProperties.Value(oDocFileName, "Custom", "Finish") = GoExcel.CellValue("E" & rowCheck)
           			iProperties.Value(oDocFileName, "Custom", "Color") = GoExcel.CellValue("F" & rowCheck)
           			iProperties.Value(oDocFileName, "Custom", "Hydro Code") = GoExcel.CellValue("G" & rowCheck)
           			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("H" & rowCheck)
           			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("I" & rowCheck)
				End If
			Next

			'Open the Excel file
			GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Infills")

			'Look at the first 15 rows of the Excel file
			'Start at row 2, since row 1 contains headings
			For rowCheck = 2 To 15
				'Compare the part name to the value in column A
        		If GoExcel.CellValue("A" & rowCheck) = Description Then
	       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("B" & rowCheck)      
	       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
	       			iProperties.Value(oDocFileName, "Custom", "Make-up") = GoExcel.CellValue("D" & rowCheck)
				End If
			Next
End If
Next oOcc

[marcin_otręba]

can you show what error you get?

[jishee]

It shows up on the "Description" line between the message boxes.

[marcin_otręba]

maybe it does not find “-“ in docname? in first message show delimiter and see its value. 

[jishee]

It gives Delimiter = 0.

[marcin_otręba]

i am not at computer right now but first line is confounding,please delete it. also instead of delimiter show docfile fullfilename, than you will know what happened.

[Sergio.D.Suárez · Accepted solution]

Hi, why don't you try to put the instruction on error resume next to see if it skips the error and continues with the execution.
I made some changes to your rule and remove what I thought was not necessary, try the code and see if it does what you need.

Dim AssyDoc As AssemblyDocument = ThisDoc.Document
Dim oAsmCompDef As AssemblyComponentDefinition = AssyDoc.ComponentDefinition

Dim oDocFile As Document, oDocFileName As String

For Each oOcc  As ComponentOccurrence In oAsmCompDef.Occurrences
	If Not TypeOf oOcc.Definition Is VirtualComponentDefinition Then
		On Error Resume Next
		oDocFile = oOcc.Definition.Document
		oDocFileName = oDocFile.displayname
		'Set your delimiter
		Delimiter = InStr(oDocFileName, "-")
		Description = Left(oDocFileName, Delimiter - 1)
				
		'Open the Excel file
		GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Parts")
					
		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Extrusion") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Custom", "Alloy-Temper") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Material") = GoExcel.CellValue("D" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Finish") = GoExcel.CellValue("E" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Color") = GoExcel.CellValue("F" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Hydro Code") = GoExcel.CellValue("G" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("H" & rowCheck)
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("I" & rowCheck)
			End If
		Next

		'Open the Excel file
		GoExcel.Open("C:\Users\jishee\Documents\Work\Inventor_modeling\iLogic\Unit_Drawing_Tests\Part_iProperties.xlsx", "Infills")

		'Look at the first 15 rows of the Excel file
		'Start at row 2, since row 1 contains headings
		For rowCheck = 2 To 15
			'Compare the part name to the value in column A
    		If GoExcel.CellValue("A" & rowCheck) = Description Then
       			iProperties.Value(oDocFileName, "Custom", "Routing") = GoExcel.CellValue("B" & rowCheck)      
       			iProperties.Value(oDocFileName, "Project", "Description") = GoExcel.CellValue("C" & rowCheck)
       			iProperties.Value(oDocFileName, "Custom", "Make-up") = GoExcel.CellValue("D" & rowCheck)
			End If
		Next
	End If
Next oOcc

If you find an error you can alternately insert a message box inside your rule to search for the source of the error.

I hope some of this can be useful to you. Cheers

[Darkforce_the_ilogic_guy · Accepted solution]

the reason that you can run it one time and not the second is that you forgot to close the excel file 

need to  add 

GoExcel.close 

after you are done using it ..  go into joblist and find and force close all excel program and see it work again. 

remember to add the GoExcel.Close code if you want to use it more then one time 

[jishee]

Thank you @Sergio.D.Suárez  and @Darkforce_the_ilogic_guy . It is working fine now.