check iproperties via excel

[martinhoos]

Hi there,

i need your help, i am looking for some code that checks our assemblies. I have an assembly with maybe 3 parts (abcd-001, abcd-002 and abcd-003) in. Each of these parts has a part no stored in: 

iProperties.Value("Summary", "Author")

And we have an excelfile, looks like this:

 How to check the assembly?

The code shouldt run to all parts and assemblys that are stored in the main assembly. look through the "part no" (Author) and find the part no in the Excel file. I need an Info via "messege"  if the Signalcode in the Excel file is 200 or 300 

Hmmmm hard to explain.... 

Thanks in advance

Regards Martin

[JaneFan]

Hey Martin,

I am not sure if I get your point correctly. Do you store the part number information in "Summary\Author" property of each occurrence?

If so, please try following code with replacing some path and number:

SyntaxEditor Code Snippet

GoExcel.Open("xxx\Test.xlsx")
Dim doc = ThisDoc.Document
Dim strPN As String 
For Each oDoc In doc.AllReferencedDocuments
'Get Summary->Author information of the document
	strPN = oDoc.PropertySets.Item(1).Item(3).Expression
	'Change 6 to any number which indicates the row numbers in excel file
	For row = 1 To 6
		Dim addr1 As String = "A" & Row
		Dim addr2 As String = "B" & Row
		Dim oa As Object = GoExcel.CellValue(addr1)
		Dim ob As Object = GoExcel.CellValue(addr2)
		If (oa.ToString() = strPN) Then
			If Int(ob) = 200 Or Int(ob) =300 Then
				errMsg = errMsg + String.Format("Large Values: row={0}, Name={1}, Value={2}", Row, oa, ob)
				MsgBox(errMsg)
				Exit For
			End If
		End If 
	Next
Next

[martinhoos]

Hi Jane,

thank you very much for your reply. Your code works exact what i was looking for - thank you!

Is it possible to Change the code a bit? If a part or an assembly is located via the Excel-sheet the color of it shouldt be change to red. I tried but without an result.

Component.Color(???) = "Red"

Thanks again...

Regards Martin

[JaneFan · Accepted solution]

Hey Martin,

ComponentOccurrence name is needed to set the appearance in top assembly:

SyntaxEditor Code Snippet

GoExcel.Open("C:\TEMP\iLogicTest\GA\4\New folder\Test.xlsx")
Dim doc = ThisDoc.Document
Dim strPN As String 
Dim oOcc As ComponentOccurrence 
For Each oDoc As PartDocument In doc.AllReferencedDocuments
	'Get Summary->Author property information in the document
	strPN = oDoc.PropertySets.Item(1).Item(3).Expression
	'Change 6 to any number which indicates the row numbers in excel file
	For row = 1 To 6
		Dim addr1 As String = "A" & Row
		Dim addr2 As String = "B" & Row
		Dim oa As Object = GoExcel.CellValue(addr1)
		Dim ob As Object = GoExcel.CellValue(addr2)
		If (oa.ToString() = strPN) Then
			If Int(ob) = 200 Or Int(ob) = 300 Then
				For Each oOcc In doc.componentdefinition.occurrences
					If oOcc.definition.document Is oDoc Then 
						Component.Color(oOcc.Name) = "red"
					End If 
				Next 
				errMsg = errMsg + String.Format("Large Values: row={0}, Name={1}, Value={2}", Row, oa, ob) + "; "
				MsgBox(errMsg)
				Exit For
			End If
		End If 
	Next
Next

[martinhoos]

Hey Jane,

thanks again for your reply and for your help. Your code works very well - exact what i was looking for - thank you!

Very best Regards from Germany

Martin

[martinhoos]

Hi Jane,

i have found a little mistake ... If i have a part in the assembly, that is not included in the Excel file, i get a message:

May be you can help me again?! I tried  with "try and catch" - but without result.  😞

Regards Martin

[JaneFan]

Hey @martinhoos, 

I guess you are seeing the error might be caused by the row count in the excel is less than the number you set in this line: 
For row = 1 To 6

But Try and catch can avoid the error even though. I pasted part of the code with try catch: 

 Try
 For row = 1 To 7
  Dim addr1 As String = "A" & Row
  Dim addr2 As String = "B" & Row
  Dim oa As Object = GoExcel.CellValue(addr1)
  Dim ob As Object = GoExcel.CellValue(addr2)
  If (oa.ToString() = strPN) Then
   If Int(ob) = 200 Or Int(ob) = 300 Then
    For Each oOcc In doc.componentdefinition.occurrences
     If oOcc.definition.document Is oDoc Then
      Component.Color(oOcc.Name) = "red"
     End If
    Next
    errMsg = errMsg + String.Format("Large Values: row={0}, Name={1}, Value={2}", Row, oa, ob) + "; "
    MsgBox(errMsg)
    Exit For
   End If
  End If
 Next
 Catch
 End Try 
Next

[martinhoos]

Hi Jane,

thank you very much for your reply. This solve my Problem.  😉 

But i have another, hope "little problem", i get this message if i have an assembly shored in.

Hopefully you can help me again....  Thanks in advance.

Very best regards

Martin

[JaneFan]

Hi Martin, 

I see. 🙂 

Please change this line: 

From:

For Each oDoc As PartDocument In doc.AllReferencedDocuments

To: 

For Each oDoc As Document In doc.AllReferencedDocuments

[martinhoos]

Hi Jane,

thank you for your reply, this solves my problem. I changed the code a bit because it runs very slow. If i had about 20 parts and assemblys in, it will need 5:30 min. 

In the excelfile i have 18.500 parts!

Is there a Change to Speed up the code? 

myXLS=("c:\temp\check.xlsx")
GoExcel.Open((myXLS), "Tabelle1")
Dim doc = ThisDoc.Document
Dim strPN As String 
Dim oOcc As ComponentOccurrence 
For Each oDoc As Document In doc.AllReferencedDocuments
	strPN = oDoc.PropertySets.Item(1).Item(3).Expression
	Try
	For row = 1 To 20000
		Dim addr1 As String = "A" & Row
		Dim oa As Object = GoExcel.CellValue(addr1)
		If (oa.ToString() = strPN) Then
				For Each oOcc In doc.componentdefinition.occurrences
					If oOcc.definition.Document Is oDoc Then 
						Component.Color(oOcc.Name) = "Gesperrt_Signalcode"
					End If 
				Next 
				Exit For
		End If 
	Next
	Catch
	End Try
Next
MessageBox.Show("Teile-Check ist fertig, evtl. gesperrte Artikel sind ROT gefärbt, bitte überprüfen.", "Check Signalcode")

Thanks in advance for your help.

Regards Martin

[JaneFan]

Hey Martin, 

There is a way to reduce one round of the loop since you don't need to control and show the message about which referenced documents being changed. Let's go through each component occurrence instead of referenced documents, so that we can use the occurrence name to set color directly in case of it fits the condition. 

For Each oOcc  In  doc.componentdefinition.occurrences
	strPN = oOcc.Definition.Document.PropertySets.Item(1).Item(3).Expression
	......
	.......
	Component.Color(oOcc.Name) = "Pink"
Next

It would probably quicker about 20 times, I guess. 🙂

[martinhoos]

Hi Jane,

thank you very much for your help.... If i changed the line you told me, Inventor runs not so Long but it changed all the parts to pink. May be i did a misstake. Please be so nice and üaste the hole code in.

Thanks a lot.....

Regards Martin

[JaneFan · Accepted solution]

Hello Martin,

How about this: 

GoExcel.Open("xxx\Test.xlsx")
Dim doc = ThisDoc.Document
Dim strPN As String 
Dim oOcc As ComponentOccurrence 
For Each oOcc In doc.componentdefinition.occurrences
	'Get Summary->Author property information in the document
	strPN = oOcc.definition.document.PropertySets.Item(1).Item(3).Expression
	MsgBox(doc.componentdefinition.occurrences.count)
	MsgBox(strPN)
	'Change 6 to any number which indicates the row numbers in excel file
	Try
	For row = 1 To 2000
		Dim addr1 As String = "A" & Row
		Dim addr2 As String = "B" & Row
		Dim oa As Object = GoExcel.CellValue(addr1)
		Dim ob As Object = GoExcel.CellValue(addr2)
		If (oa.ToString() = strPN) Then
			If Int(ob) = 200 Or Int(ob) = 300 Then
				Component.Color(oOcc.Name) = "Pink"
				errMsg = errMsg + String.Format("Large Values: row={0}, Name={1}, Value={2}", Row, oa, ob) + "; "
				MsgBox(errMsg )
			End If
		End If 
	Next
	Catch
	End Try
Next

[martinhoos]

Hi Jane,

thanks for your help... The code runs well.

Regards Martin