Need Help with ILogic rule for generating custom iPropertis in sheet metal fp parts and expart data to excel.

[mecanicu]

Hello.

First of all, Big Tx to all of you from this forum. 

I'm new to ILogic. but i have to do a automation for a project a have and need you help.

I manage to get bits and pieces from this forum to write a ILogic rule for my need.

Let me describe what i need:

I have an assembly with sheet metal parts. I need to fill a Excel *.xls template with 3 things: Length and With (from sheet metal fp)   + Part Description form all the parts in the assembly. So for this i made :

Sub Main()
    
	
	Dim oAsmDoc As AssemblyDocument 
	oAsmDoc = ThisApplication.ActiveDocument
			
	' Reading current assembly component definition.
Dim oAsmDef As AssemblyComponentDefinition
oAsmDef = oAsmDoc.ComponentDefinition

' Reading Child part(leaf occurrences) of the assembly.
Dim oLeafOccs As ComponentOccurrencesEnumerator
oLeafOccs = oAsmDef.Occurrences.AllLeafOccurrences

' Iterate through all part occurrences in assembly.
For Each oOcc In oLeafOccs
	Dim odoc As PartDocument
	 Try
        odoc=oOcc.definition.document
		Catch
		End Try
  
    Dim oSMDef As SheetMetalComponentDefinition
    oSMDef = odoc.ComponentDefinition
       'Determines if there is a flat pattern
    If odoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
'        If oSMDef.FlatPattern Is Nothing Then
'            'No Flat Pattern Exists
'            iProperties.Value(oOcc.Name,"Custom", "Width") = "N/A"
'			iProperties.Value(oOcc.Name,"Custom", "Length") = "N/A"
'          Else
		    oSMDef.Unfold()
            odoc.Update2(True)
			
	Dim fp As FlatPattern = oSMDef.FlatPattern
	
			Lungime = fp.Length*10

            Latime = fp.Width*10
            

iProperties.Value(oOcc.Name,"Custom", "Lungime")= Lungime
iProperties.Value(oOcc.Name, "Custom", "Latime") = Latime


		  End If

	

Dim sourceFileName As String = ThisDoc.Path + "\2.xls"



If (Not IO.File.Exists(sourceFileName)) Then
  MessageBox.Show("The source file " & sourceFileName & " does not exist", "Parameter Export")
  Return
End If

Dim fileName As String
 Using dialog As New SaveFileDialog()
   dialog.Title = "Export Parameters to Excel"
   dialog.Filter = "Excel Files (*.xls)|*.xls"
   If (dialog.ShowDialog() <> vbOK) Then
     Return ' exit out of the rule
   End If
   fileName = dialog.FileName
End Using

If (String.Equals(sourceFileName, fileName, StringComparison.OrdinalIgnoreCase)) Then
  MessageBox.Show("You cannot overwrite the original file: " & sourceFileName, "Parameter Export")
  Return
End If

IO.File.Copy(sourceFileName, fileName, True)

GoExcel.CellValue(fileName, "Sheet1", "A19") = d0
'GoExcel.CellValue("A19") = iProperties.Value("Project", "Description")
GoExcel.CellValue("B19") = iProperties.Value(oOcc.Name,"Custom", "Lungime")
GoExcel.CellValue("C19") = iProperties.Value(oOcc.Name, "Custom", "Latime")
GoExcel.CellValue("E19") = iProperties.Value(oOcc.Name,"Project", "Part Number")
GoExcel.Save

	next


End Sub

 I have some problems with this code, first i tried this code so many time, my custom ipropertis are a mess, so i would like to be able to delete all custom ipropertis prier to write the new ones.

next problem is this code is generating a new Excel sheet for every part. I would lie to generate only one copy and populate next row for every part.

Sry for my bad English and y tipo...... Tx 

[mecanicu]

no one??!! i'm sure is not a problem for a higher level member........... Pleas help me i need to finish this project asp.

[WCrihfield]

If there are multiple instances of the same part (component) within the assembly, do you only want that part's data written to Excel just once, or are you expecting one Excel entry for each occurrence of it found within all levels of the assembly?

Why is your code finding one existing Excel file, then using a SaveAs dialog to choose another Excel file, then saving the existing Excel file over the chosen Excel file?  I don't understand the purpose of that process.  And that process is located within the loop of each component, so it is being done once for each component occurrence.

I know how to find the last row used, then make new entries write to new rows within the Excel spreadsheet, but I usually prefer to bypass using iLogic's GoExcel techniques, and access the Excel application directly using regular vb.net techniques.  Once I fully understand what you are wanting, I can attempt to fix your code to accomplish your goals.

I see that you are attempting to write the value of a Model Parameter named "d0" to the first column of the Excel sheet.  What is in that parameter, a number?

[mecanicu]

Tx for replay.

- there is only one instance of parts within the assembly and i only want parts data written just once.

- i have a "master" excel file as a template and i want to make a copy  of it were i write the data every time i trigger the rule.

-i found that goExcel is the only thing that works for me, vb.net or vba is not working because i don't have Excel app installed. ( i have to send the xls to my google drive every time i want to very that is working or not, i's sent this excel to third-party)

-regarding Model parameter "d0" i don't have a clue, i thing that is leftover code from were i got it....

Tx

[mecanicu]

.....I tried to repair the code based on your feedback but with no luck (

[mecanicu]

Still w8ing for a solution!

[mecanicu]

I manage to make it working 30% . It generate Iproperties for flat pattern parts but it's only export's first part to Excel. 

I don't know how to iterate rows.....

Sub Main()
    
	
	Dim oAsmDoc As AssemblyDocument 
	oAsmDoc = ThisApplication.ActiveDocument
			
	' Reading current assembly component definition.
Dim oAsmDef As AssemblyComponentDefinition
oAsmDef = oAsmDoc.ComponentDefinition

' Reading Child part(leaf occurrences) of the assembly.
Dim oLeafOccs As ComponentOccurrencesEnumerator
oLeafOccs = oAsmDef.Occurrences.AllLeafOccurrences

' Iterate through all part occurrences in assembly.
For Each oOcc In oLeafOccs
	Dim odoc As PartDocument
	 Try
        odoc=oOcc.definition.document
		Catch
		End Try
  
    Dim oSMDef As SheetMetalComponentDefinition
    oSMDef = odoc.ComponentDefinition
       'Determines if there is a flat pattern
    If odoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
'        If oSMDef.FlatPattern Is Nothing Then
'            'No Flat Pattern Exists
'            iProperties.Value(oOcc.Name,"Custom", "Width") = "N/A"
'			iProperties.Value(oOcc.Name,"Custom", "Length") = "N/A"
'          Else
		    oSMDef.Unfold()
            odoc.Update2(True)
			
	Dim fp As FlatPattern = oSMDef.FlatPattern
	
			Lungime = fp.Length*10

            Latime = fp.Width*10
            

iProperties.Value(oOcc.Name,"Custom", "Lungime")= Lungime
iProperties.Value(oOcc.Name, "Custom", "Latime") = Latime
'iProperties.Value("Project", "Part Number")= odoc.Name



End If




Dim sourceFileName As String = ThisDoc.Path + "\Test.xls"
If (Not IO.File.Exists(sourceFileName)) Then
  MessageBox.Show("The source file " & sourceFileName & " does not exist", "Parameter Export")
  Return
End If


'Dim Data(8) As Integer



'    'start data row

'Data(0) = 1
'Data(1) = Lungime
'Data(2) = Latime
'Data(3) = 1
''Data(4) = iProperties.Value("Project", "Part Number")
'Data(5) = 1
'Data(6) = 1
'Data(7) = 1
'Data(8) = 1

'    'string array for properties
'Dim row As Integer = 19
'GoExcel.Open(sourceFileName, "Sheet1")

'	GoExcel.CellValue("A" & CStr(row)) = Data(0)
'    GoExcel.CellValue("B" & CStr(row)) = Data(1)
'	GoExcel.CellValue("C" & CStr(row)) = Data(2)
'	GoExcel.CellValue("D" & CStr(row)) = Data(3)
'    'GoExcel.CellValue("E" & CStr(row)) = Data(4)
'	GoExcel.CellValue("F" & CStr(row)) = Data(5)
'	GoExcel.CellValue("G" & CStr(row)) = Data(6)
'	GoExcel.CellValue("H" & CStr(row)) = Data(7)
'	GoExcel.CellValue("I" & CStr(row)) = Data(8)
'	GoExcel.CellValues("A" & CStr(row), "I" & CStr(row)) = Data

'    row += 1
	
GoExcel.CellValue(sourceFileName, "Sheet1", "A19") = d0
GoExcel.CellValue("A19") = 1
GoExcel.CellValue("B19") = iProperties.Value(oOcc.Name,"Custom", "Lungime")
GoExcel.CellValue("C19") = iProperties.Value(oOcc.Name, "Custom", "Latime")
GoExcel.CellValue("D19") = 1
GoExcel.CellValue("E19") = iProperties.Value(oOcc.Name, "Project", "Part Number")
GoExcel.CellValue("F19") = 1
GoExcel.CellValue("G19") = ""
GoExcel.CellValue("H19") = 1
GoExcel.CellValue("I19") = ""

GoExcel.Save
Next

End Sub

[WCrihfield · Accepted solution]

Sorry for the delay.  Things got busy at work.  It's a little harder to set-up using just the GoExcel techniques, but I think I may have fixed the code for you.  Read through the code and all of the comment lines, before running this.

In order to set this up so it will write part data to a new row in the spreadsheet, I created an Integer variable just before starting into the loop.  That integer represents what 'Row' to start writing part data on.  That Integer is used within the 'Cell' address when writing the data out.  Then at the end of the loop, it increments by 1, so the next loop will write to the next row.

I changed the loop from going through 'AllLeafOccurrences' to going through 'AllReferencedDocuments', to eliminate the possibility of writing out data for multiple occurrences of the same part.

I included some code to delete all custom iProperties from each part, before attempting to write the two new ones.

Here's the code:

Sub Main()
	If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
		MsgBox("An Assembly Document must be active for this rule to work. Exiting.",vbOKOnly+vbCritical, "WRONG DOCUMENT TYPE")
		Exit Sub
	End If
    Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
	
	'specify Template spreadsheet file path/name (GetDirectoryName returns Path, without file name or extension)
	Dim oSourceFile As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName) + "\2.xls"
	If (Not System.IO.File.Exists(oSourceFile)) Then
		MsgBox("The source file '" & oSourceFile & "' does not exist.",,"Parameter Export")
		Exit Sub 'Return
	End If

	'create a copy of the Template spreadsheet file to work with
	Dim oFileName As String
	Using oSFDialog As New System.Windows.Forms.SaveFileDialog
		oSFDialog.Title = "Export Parameters to Excel"
		oSFDialog.Filter = "Excel Files (*.xls)|*.xls"
		If (oSFDialog.ShowDialog() <> System.Windows.Forms.DialogResult.OK) Then
			Exit Sub 'Return ' exit out of the rule
		End If
		oFileName = oSFDialog.FileName
	End Using
	If oSourceFile = oFileName Then
		MsgBox("You cannot overwrite the original file: " & oSourceFile,, "Parameter Export")
		Exit Sub 'Return
	End If
	System.IO.File.Copy(oSourceFile, oFileName, True)
	
	'Create an Integer (counter) variable outside of the loop to use within the loop
	'for specifying which row number to write to within the spreadsheet document
	'I'm starging with 5, just in case you have some headings and column headers
	'increase this starting number to start on a lower row
	Dim oRow As Integer = 5
	
	'Iterate through all part occurrences in assembly.
	For Each oRefDoc As Document In oADoc.AllReferencedDocuments
		Dim oPDoc As PartDocument
		If oRefDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
			oPDoc = oRefDoc
		Else
			Continue For
		End If
		Dim oSMDef As SheetMetalComponentDefinition
		If oPDoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
			oSMDef = oPDoc.ComponentDefinition
		Else
			Continue For
		End If
		If Not oSMDef.HasFlatPattern Then
			Try
				oSMDef.Unfold()
				oPDoc.Update2(True)
			Catch
				MsgBox("Failed to Unfold the following Sheet Metal Part:" & vbCrLf & _
				oPDoc.FullFileName & vbCrLf & _
				"Data for that part will not be written to the Excel file.", vbOKOnly + vbExclamation, "")
				Continue For
			End Try
		End If
		Dim oFPatt As FlatPattern = oSMDef.FlatPattern
		Dim Lungime As Double = oFPatt.Length*10
		Dim Latime As Double = oFPatt.Width * 10
		
		'delete all existing 'custom' iProperties in target document first
		For Each oCProp As Inventor.Property In oPDoc.PropertySets.Item("Inventor User Defined Properties")
			oCProp.Delete
		Next
		
		'these lines will create these iProperties if needed, or just update them
		iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime") = Lungime 'Length
		iProperties.Value(oPDoc.DisplayName, "Custom", "Latime") = Latime 'Width
		
		'this first iProperty value is from the main assembly (not the individual part) due to how it is being retrieved
		'if you want it to get the value from the part, add the first input variable like the other rows below
		GoExcel.CellValue(oFileName, "Sheet1", "A" & oRow) = iProperties.Value("Project", "Description")
		GoExcel.CellValue("B" & oRow) = iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime")
		GoExcel.CellValue("C" & oRow) = iProperties.Value(oPDoc.DisplayName, "Custom", "Latime")
		GoExcel.CellValue("E" & oRow) = iProperties.Value(oPDoc.DisplayName,"Project", "Part Number")
		
		oRow = oRow + 1
	Next
	GoExcel.Save
End Sub

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[mecanicu]

I modify the rule to match my needs and i thin i brooked it.

Sub Main()
	If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
		MsgBox("An Assembly Document must be active for this rule to work. Exiting.",vbOKOnly+vbCritical, "WRONG DOCUMENT TYPE")
		Exit Sub
	End If
    Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
	
	'specify Template spreadsheet file path/name (GetDirectoryName returns Path, without file name or extension)
	Dim oSourceFile As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName) + "\Test.xls"
	If (Not System.IO.File.Exists(oSourceFile)) Then
		MsgBox("The source file '" & oSourceFile & "' does not exist.",,"Parameter Export")
		Exit Sub 'Return
	End If

	'create a copy of the Template spreadsheet file to work with
	Dim oFileName As String
	Using oSFDialog As New System.Windows.Forms.SaveFileDialog
		oSFDialog.Title = "Export Parameters to Excel"
		oSFDialog.Filter = "Excel Files (*.xls)|*.xls"
		If (oSFDialog.ShowDialog() <> System.Windows.Forms.DialogResult.OK) Then
			Exit Sub 'Return ' exit out of the rule
		End If
		oFileName = oSFDialog.FileName
	End Using
	If oSourceFile = oFileName Then
		MsgBox("You cannot overwrite the original file: " & oSourceFile,, "Parameter Export")
		Exit Sub 'Return
	End If
	System.IO.File.Copy(oSourceFile, oFileName, True)
	
	'Create an Integer (counter) variable outside of the loop to use within the loop
	'for specifying which row number to write to within the spreadsheet document
	'I'm starging with 5, just in case you have some headings and column headers
	'increase this starting number to start on a lower row
	Dim oRow As Integer = 19
	
	'Iterate through all part occurrences in assembly.
	For Each oRefDoc As Document In oADoc.AllReferencedDocuments
		Dim oPDoc As PartDocument
		If oRefDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
			oPDoc = oRefDoc
		Else
			Continue For
		End If
		Dim oSMDef As SheetMetalComponentDefinition
		If oPDoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
			oSMDef = oPDoc.ComponentDefinition
		Else
			Continue For
		End If
		If Not oSMDef.HasFlatPattern Then
			Try
				oSMDef.Unfold()
				oPDoc.Update2(True)
			Catch
				MsgBox("Failed to Unfold the following Sheet Metal Part:" & vbCrLf & _
				oPDoc.FullFileName & vbCrLf & _
				"Data for that part will not be written to the Excel file.", vbOKOnly + vbExclamation, "")
				Continue For
			End Try
		End If
		Dim oFPatt As FlatPattern = oSMDef.FlatPattern
		Dim Lungime As Double = oFPatt.Length*10
		Dim Latime As Double = oFPatt.Width * 10
		
		'delete all existing 'custom' iProperties in target document first
		For Each oCProp As Inventor.Property In oPDoc.PropertySets.Item("Inventor User Defined Properties")
			oCProp.Delete
		Next
		
		'these lines will create these iProperties if needed, or just update them
		iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime") = Lungime 'Length
		iProperties.Value(oPDoc.DisplayName, "Custom", "Latime") = Latime 'Width
		
		'this first iProperty value is from the main assembly (not the individual part) due to how it is being retrieved
		'if you want it to get the value from the part, add the first input variable like the other rows below
		GoExcel.CellValue("A" & oRow) = 1
		GoExcel.CellValue("B" & oRow) = iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime")
		GoExcel.CellValue("C" & oRow) = iProperties.Value(oPDoc.DisplayName, "Custom", "Latime")
		GoExcel.CellValue("D" & oRow) = 1
		GoExcel.CellValue("E" & oRow) = iProperties.Value(oPDoc.DisplayName, "Project", "Part Number")
		GoExcel.CellValue("F" & oRow) = 1
		oRow = oRow + 1
	Next
	GoExcel.Save
End Sub

 I added some "1" to be injected in Colum "A, D, F"  and now it's only sends the first row to excel

Another problem is this Assembly is derived from a multi-body part. don't know why this rule is trying to open the multi-body part  (and i'm getting an error)

[WCrihfield]

Interesting.  I didn't know this was a derived assembly.  Since I'm not that familiar with working with derived documents, and don't have any 'test' documents to test this code on, I'm guessing that maybe the 'AllReferencedDocuments' reference might, at some point, be pointing to the parent multi-body part file, causing the error.  So, I changed the loop back to going through 'AllLeafOccurrences', but I'm not sure if that's what is causing the error.

Another thought:

Writing data to a spreadsheet can be really tricky at times, especially when working with a highly customized template.  I believe it is possible that there might be a data type conflict between what we're trying to write the spreadsheet, and what type of data that cell within the spreadsheet is expecting.  We may have to check the values of those iProperties before attempting to write them to the spreadsheet.  Not all iProperties return String type data, in fact their values are defined as either Object or Variant, depending on which reference you read, then they usually just understood as the closest compatible data type when used.  You may need to convert some iProperty values to either String (CStr()) or Double (CDbl()), or Integer (CInt()) before writing them to the spreadsheet.

Sub Main()
	If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
		MsgBox("An Assembly Document must be active for this rule to work. Exiting.",vbOKOnly+vbCritical, "WRONG DOCUMENT TYPE")
		Exit Sub
	End If
    Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
	
	'specify Template spreadsheet file path/name (GetDirectoryName returns Path, without file name or extension)
	Dim oSourceFile As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName) + "\Test.xls"
	If (Not System.IO.File.Exists(oSourceFile)) Then
		MsgBox("The source file '" & oSourceFile & "' does not exist.",,"Parameter Export")
		Exit Sub 'Return
	End If

	'create a copy of the Template spreadsheet file to work with
	Dim oFileName As String
	Using oSFDialog As New System.Windows.Forms.SaveFileDialog
		oSFDialog.Title = "Export Parameters to Excel"
		oSFDialog.Filter = "Excel Files (*.xls)|*.xls"
		If (oSFDialog.ShowDialog() <> System.Windows.Forms.DialogResult.OK) Then
			Exit Sub
		End If
		oFileName = oSFDialog.FileName
	End Using
	If oSourceFile = oFileName Then
		MsgBox("You cannot overwrite the original file: " & oSourceFile,, "Parameter Export")
		Exit Sub
	End If
	System.IO.File.Copy(oSourceFile, oFileName, True)
	
	'specify first row to start writing data to in the spreadsheet
	Dim oRow As Integer = 19
	
	'Iterate through all part occurrences in assembly.
	For Each oOcc As ComponentOccurrence In oADef.Occurrences.AllLeafOccurrences
		Dim oPDoc As PartDocument
		If oOcc.DefinitionDocumentType = DocumentTypeEnum.kPartDocumentObject Then
			oPDoc = oOcc.Definition.Document
		Else
			Continue For
		End If
		Dim oSMDef As SheetMetalComponentDefinition
		If oPDoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
			oSMDef = oPDoc.ComponentDefinition
		Else
			Continue For
		End If
		If Not oSMDef.HasFlatPattern Then
			Try
				oSMDef.Unfold()
				oPDoc.Update2(True)
			Catch
				MsgBox("Failed to Unfold the following Sheet Metal Part:" & vbCrLf & _
				oPDoc.FullFileName & vbCrLf & _
				"Data for that part will not be written to the Excel file.", vbOKOnly + vbExclamation, "")
				Continue For
			End Try
		End If
		Dim oFPatt As FlatPattern = oSMDef.FlatPattern
		Dim Lungime As Double = oFPatt.Length*10
		Dim Latime As Double = oFPatt.Width * 10
		
		'delete all existing 'custom' iProperties in target document first
		For Each oCProp As Inventor.Property In oPDoc.PropertySets.Item("Inventor User Defined Properties")
			oCProp.Delete
		Next
		
		'these lines will create these iProperties if needed, or just update them
		iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime") = Lungime 'Length
		iProperties.Value(oPDoc.DisplayName, "Custom", "Latime") = Latime 'Width
		
		'write values out to spreadsheet
		GoExcel.CellValue("A" & oRow) = 1
		GoExcel.CellValue("B" & oRow) = CDbl(iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime"))
		GoExcel.CellValue("C" & oRow) = CDbl(iProperties.Value(oPDoc.DisplayName, "Custom", "Latime"))
		GoExcel.CellValue("D" & oRow) = 1
		GoExcel.CellValue("E" & oRow) = CStr(iProperties.Value(oPDoc.DisplayName, "Project", "Part Number"))
		GoExcel.CellValue("F" & oRow) = 1
		oRow = oRow + 1
	Next
	GoExcel.Save
End Sub

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

[mecanicu]

It's not doing anything now. maybe i'l add the "1" columns manually  in excel.

I attached the template xls

[mecanicu]

I manage to find the problem in the rule. if i gray out this line
GoExcel.CellValue(oFileName, "Sheet1", "A" & oRow) = iProperties.Value("Project", "Description")
it's not sending data to excel....

[mecanicu]

I'm stupid😳   That line triggers the spread sheet 

I manage to get it working

Sub Main()
	If ThisApplication.ActiveDocumentType <> DocumentTypeEnum.kAssemblyDocumentObject Then
		MsgBox("An Assembly Document must be active for this rule to work. Exiting.",vbOKOnly+vbCritical, "WRONG DOCUMENT TYPE")
		Exit Sub
	End If
    Dim oADoc As AssemblyDocument = ThisApplication.ActiveDocument
	
	'specify Template spreadsheet file path/name (GetDirectoryName returns Path, without file name or extension)
	Dim oSourceFile As String = System.IO.Path.GetDirectoryName(oADoc.FullFileName) + "\Test.xls"
	If (Not System.IO.File.Exists(oSourceFile)) Then
		MsgBox("The source file '" & oSourceFile & "' does not exist.",,"Parameter Export")
		Exit Sub 'Return
	End If

	'create a copy of the Template spreadsheet file to work with
	Dim oFileName As String
	Using oSFDialog As New System.Windows.Forms.SaveFileDialog
		oSFDialog.Title = "Export Parameters to Excel"
		oSFDialog.Filter = "Excel Files (*.xls)|*.xls"
		If (oSFDialog.ShowDialog() <> System.Windows.Forms.DialogResult.OK) Then
			Exit Sub 'Return ' exit out of the rule
		End If
		oFileName = oSFDialog.FileName
	End Using
	If oSourceFile = oFileName Then
		MsgBox("You cannot overwrite the original file: " & oSourceFile,, "Parameter Export")
		Exit Sub 'Return
	End If
	System.IO.File.Copy(oSourceFile, oFileName, True)
	
	'Create an Integer (counter) variable outside of the loop to use within the loop
	'for specifying which row number to write to within the spreadsheet document
	'I'm starging with 5, just in case you have some headings and column headers
	'increase this starting number to start on a lower row
	Dim oRow As Integer = 19
	
	'Iterate through all part occurrences in assembly.
	For Each oRefDoc As Document In oADoc.AllReferencedDocuments
		Dim oPDoc As PartDocument
		If oRefDoc.DocumentType = DocumentTypeEnum.kPartDocumentObject Then
			oPDoc = oRefDoc
		Else
			Continue For
		End If
		Dim oSMDef As SheetMetalComponentDefinition
		If oPDoc.SubType = "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
			oSMDef = oPDoc.ComponentDefinition
		Else
			Continue For
		End If
		If Not oSMDef.HasFlatPattern Then
			Try
				oSMDef.Unfold()
				oPDoc.Update2(True)
			Catch
				MsgBox("Failed to Unfold the following Sheet Metal Part:" & vbCrLf & _
				oPDoc.FullFileName & vbCrLf & _
				"Data for that part will not be written to the Excel file.", vbOKOnly + vbExclamation, "")
				Continue For
			End Try
		End If
		Dim oFPatt As FlatPattern = oSMDef.FlatPattern
		Dim Lungime As Double = oFPatt.Length*10
		Dim Latime As Double = oFPatt.Width * 10
		
		'delete all existing 'custom' iProperties in target document first
		For Each oCProp As Inventor.Property In oPDoc.PropertySets.Item("Inventor User Defined Properties")
			oCProp.Delete
		Next
		
		'these lines will create these iProperties if needed, or just update them
		iProperties.Value(oPDoc.DisplayName,"Custom", "Lungime") = Lungime 'Length
		iProperties.Value(oPDoc.DisplayName, "Custom", "Latime") = Latime 'Width
		
		'this first iProperty value is from the main assembly (not the individual part) due to how it is being retrieved
		'if you want it to get the value from the part, add the first input variable like the other rows below
		GoExcel.CellValue(oFileName, "Sheet1", "A" & oRow) = iProperties.Value("Project", "Description")
		GoExcel.CellValue("A" & oRow) = 1
		GoExcel.CellValue("B" & oRow) = iProperties.Value(oPDoc.DisplayName, "Custom", "Lungime")
		GoExcel.CellValue("C" & oRow) = iProperties.Value(oPDoc.DisplayName, "Custom", "Latime")
		GoExcel.CellValue("D" & oRow) = 1
		GoExcel.CellValue("E" & oRow) = iProperties.Value(oPDoc.DisplayName, "Project", "Part Number")
		GoExcel.CellValue("F" & oRow) = 1
		GoExcel.CellValue("G" & oRow) = 1
		
		oRow = oRow + 1
	Next
	GoExcel.Save
End Sub

Tell me pleas if it's possible to insert column "G" dynamically ("1"or "2") base on a piece of the "Part Number" say for a part number "S1 Usa" if it contains "Usa" in the name, to populate column "G" with "2" and in not then "1"

Thank you very much!!!!!!

[mecanicu]

Can i limit the part processed by this rule based on sheet metal thickness say 18mm ????

[WCrihfield · Accepted solution]

In response to your first request...If the part's Part Number contains "Usa", then put 2 in column G:

Here is some code for that.

Replace this:

GoExcel.CellValue("G" & oRow) = 1

with this:

If CStr(iProperties.Value(oPDoc.DisplayName, "Project", "Part Number")).Contains("Usa") Then
	GoExcel.CellValue("G" & oRow) = 2
Else
	GoExcel.CellValue("G" & oRow) = 1
End If

In response to your second request...Limit part processed based on Thickness of 18mm:

If you want to only process Sheet Metal parts who's thickness = 18mm then,

Right after the If...Else...End If block of code that checks oPDoc.SubType, and sets oSMDef value...

Use something like this:

'dividing 18 by 10 to convert centimeters (default database distance units) to millimeters
If oSMDef.Thickness.Value = (18 / 10) Then

followed by the rest of your loop code, then End If at the end of the loop (just before Next).

OR...to simplify, and not have to close-out the block of code at the end (my preference),

If oSMDef.Thickness.Value <> (18/10) Then
    Continue For 'skip to next document
End If

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.