Ilogic and Excel

[pg47328]

I have been trying to make a loop for every part to import the custom excel parameters to iproperties so, for each row with that ID number I want to find the component with that occurrence Name and fill the corresponding iproperties, followed some forum ideas but the same error appear every time: GoExcel: Could not match the column title: "ID". 

Sub Main
    
    Dim AsmDoc As AssemblyDocument = ThisDoc.Document
	Dim FileName As String = "File.xls"
 
   
   TraverseAssembly(AsmDoc.ComponentDefinition.Occurrences,FileName)
	
End Sub

Sub TraverseAssembly(Occurrences As ComponentOccurrences,FileName As String)
                   MessageBox.Show(FileName, "Title")
         
    ' Iteration through all of the occurrence 

    For Each Occ As ComponentOccurrence In Occurrences
		
			If Occ.DefinitionDocumentType = kPartDocumentObject Then
				
				'Get document from Occurrence.
				Dim OccDoc As Document = Occ.Definition.Document
				
				'Avoid read only parts like content center/library parts.
				If Not OccDoc.IsModifiable Then Continue For
				
				'Finding the Row containing the occurrence 
				Dim Row As Integer = GoExcel.FindRow(FileName, "Sheet X", "ID", "=", Occ.Name)
				If Row = -1 Then  Continue For'MessageBox.Show("could not find row", "Title") :

				Dim SummaryPropertySet As PropertySet = OccDoc.PropertySets.Item("Inventor Document Summary Information")
				Dim DesignPropertySet As PropertySet = OccDoc.PropertySets.Item("Design Tracking Properties")
				Dim CustomPropertySet As PropertySet = OccDoc.PropertySets.Item("Inventor User Defined Properties")
		   
				
				'try and set the iproperty
				Try
					Dim CustomProp1 As [Property] = CustomPropertySet.Item("iProperties")
					CustomProp1.Value =  GoExcel.CurrentRowValue("Parameters")
				Catch Ex As Exception 'iproperties not found so add and set
					CustomPropertySet.Add(GoExcel.CurrentRowValue("Parameters"),"iProperties")  
				End Try
				
			End If	
				
	        If Occ.DefinitionDocumentType = kAssemblyDocumentObject Then
	            Call TraverseAssembly(Occ.SubOccurrences,FileName)
	        End If
			
    Next
End Sub

[A.Acheson]

Hi @pg47328 

Is your excel sheet located in the same folder as your assembly? What is the iproperty name your trying to import, is it called "iProperties"? I don't see that in the BOM editor. Is there any gaps in your excel data especially the ID  column? If there is gaps then the look up row function will fail. 

[Cadkunde.nl]

I think the error message comes from:

Dim FileName As String = "File.xls"

This should be c:\...\file.xls(x?) 

[pg47328]

Yes, i have  c:\...\file.xls, it was only a generic sample to show my code

[pg47328]

Thanks for the response,

Yes, the excel sheet is located in the same folder as my assembly. The "iproperty" and "parameter"  are generic names, only to show my code, and they take the name of the parameters as showed in the image. My sheet dont contain gaps. Sorry if you are saying something realy basic and i couldnt understand, i am a new user of this software and im still getting used to the program.

[pg47328]

Thanks for the response,

Yes, the excel sheet is located in the same folder as my assembly. The "iproperty" and "parameter"  are generic names, only to show my code, and they take the name of the parameters as showed in the image. My sheet don't contain gaps. Sorry if you are saying something  basic and i couldn't understand, i am a new user of this software and i'm still getting used to the program.

[A.Acheson]

I tested your code and cannot find any issues, so this would lead me to believe either the column title isn't on row one or that a column title is different or has different formatting. Maybe have a check again. All I changed was the sheet name and added a filepath.

[pg47328]

Yes, part of the problem is solved. Now i have a error when i import more than one propertie for the same ID , maybe i am writing my code wrong. can you give me some advice how to solve it?

[A.Acheson · Accepted solution]

Can you post the code? Having more than 1 iprop is really only a matter of adding more code. If you have more than 1 iproperty it might be easier put the iproperty creation into a function/sub routine to call again. Here is an example of iprop function

[pg47328 · Accepted solution]

my code looks like this

Sub Main
    
    Dim AsmDoc As AssemblyDocument = ThisDoc.Document
	Dim FileName As String = "C:\Excel to Inventor.xlsx"
 
   
   TraverseAssembly(AsmDoc.ComponentDefinition.Occurrences,FileName)
	
End Sub

Sub TraverseAssembly(Occurrences As ComponentOccurrences,FileName As String)
                   MessageBox.Show(FileName, "Title")
         
    ' Iteration through all of the occurrence 

    For Each Occ As ComponentOccurrence In Occurrences
		
			If Occ.DefinitionDocumentType = kPartDocumentObject Then
				
				'Get document from Occurrence.
				Dim OccDoc As Document = Occ.Definition.Document
				
				'Avoid read only parts like content center/library parts.
				If Not OccDoc.IsModifiable Then Continue For
				
				'Finding the Row containing the occurrence 
				Dim Row As Integer = GoExcel.FindRow(FileName, "Sheet 1", "ID", "=", Occ.Name)
				If Row = -1 Then  Continue For'MessageBox.Show("could not find row", "Title") :

				Dim SummaryPropertySet As PropertySet = OccDoc.PropertySets.Item("Inventor Document Summary Information")
				Dim DesignPropertySet As PropertySet = OccDoc.PropertySets.Item("Design Tracking Properties")
				Dim CustomPropertySet As PropertySet = OccDoc.PropertySets.Item("Inventor User Defined Properties")
		   
				
				'try and set the iproperty
				Try
					Dim CustomProp1 As [Property] = CustomPropertySet.Item("Assembly Code")
					CustomProp1.Value =  GoExcel.CurrentRowValue("Assembly Code")
				Catch Ex As Exception 'iproperties not found so add and set
					CustomPropertySet.Add(GoExcel.CurrentRowValue("BP_Assembly Code"),"BP_Assembly Code")  
				End Try
				
				Try
   					Dim CustomProp2 As [Property] = CustomPropertySet.Item("BP_Simple Code")
   					 CustomProp2.Value = GoExcel.CurrentRowValue("BP_Simple Code")
				Catch Ex As Exception 'If the custom property doesn't exist, add it and set its value
   					CustomPropertySet.Add(GoExcel.CurrentRowValue("Simple Code"), "Simple Code")
				End Try
				Try
   					Dim CustomProp3 As [Property] = CustomPropertySet.Item("Lenght (mm)")
   					 CustomProp3.Value = GoExcel.CurrentRowValue("Lenght (mm)")
				Catch Ex As Exception 'If the custom property doesn't exist, add it and set its value
   					CustomPropertySet.Add(GoExcel.CurrentRowValue("Lenght (mm)"), "Lenght (mm)")
				End Try
				Try
   					Dim CustomProp4 As [Property] = CustomPropertySet.Item("Dimensions")
   					 CustomProp4.Value = GoExcel.CurrentRowValue("Dimensions")
				Catch Ex As Exception 'If the custom property doesn't exist, add it and set its value
   					CustomPropertySet.Add(GoExcel.CurrentRowValue("Dimensions"), "Dimensions")
				End Try
				
			End If	
				
	        If Occ.DefinitionDocumentType = kAssemblyDocumentObject Then
	            Call TraverseAssembly(Occ.SubOccurrences,FileName)
	        End If
			
    Next
End Sub

After i run this error apear (Exception from HRESULT: 0x80070057 (E_INVALIDARG)), and my custom properties look like this