Copy Assembly mass properties into Excel

[karram]

How to Extract the assembly weight (physical properties mass) into Excel workbook with specific cells address using ilogic rules

[Andrii_Humeniuk · Accepted solution]

Hi @karram . An example of mass recording in Excel:

Dim sPathExcel As String = "C:\Users\AndriiHumeniuk\Documents\YourExcel.xlsx"	
GoExcel.DisplayAlerts = False
GoExcel.Open(sPathExcel, "Sheet1")	
GoExcel.CellValue("A1") = String.Concat("", Round((iProperties.Value("Project", "Mass")/1000),2))	
GoExcel.Save
GoExcel.Close

 Change the placement field and the name of your excel and sheet in it.

[karram]

Hello,

Below code works perfectly. Thanks

One more requirements in same concept.

In main assembly, i had many sub assembly and sub parts, For this case how to Extract the particular sub assembly and sub parts weight (physical properties mass) into Excel workbook with specific cells address using ilogic rules.

Thanks

[Andrii_Humeniuk · Accepted solution]

Hi @karram . You can select components both in the model and in the browser.

Dim oComp As ComponentOccurrence = ThisApplication.CommandManager.Pick(SelectionFilterEnum.kAssemblyOccurrenceFilter, _
		"Select component...(Press ESC to cancel)")
If oComp Is Nothing Then Exit Sub
Dim oDef As ComponentDefinition = oComp.Definition
Dim oDoc As Document = oDef.Document
Dim sPathExcel As String = "C:\Users\AndriiHumeniuk\Documents\YourExcel.xlsx"	
GoExcel.DisplayAlerts = False
GoExcel.Open(sPathExcel, "Sheet1")
Dim oMass As Double = Round(oDoc.PropertySets.Item("Design Tracking Properties").Item("Mass").Value / 1000, 3)
Dim i As Integer = 1
Do While GoExcel.CellValue("A" & i) IsNot Nothing
	i += 1
Loop
GoExcel.CellValue("A" & i) = oMass
GoExcel.Save
GoExcel.Close

[karram]

Hello,

Thanks,

Without selection i need to use file names (Sub-assembly and sup-parts, for Example) means. how to create?

BR

Karthick

[Andrii_Humeniuk · Accepted solution]

Not sure if I understood you correctly. But this code can record the name and mass of the main assembly and those elements that you choose.

Dim sPathExcel As String = "C:\Users\AndriiHumeniuk\Documents\YourExcel.xlsx"	
GoExcel.DisplayAlerts = False
GoExcel.Open(sPathExcel, "Sheet1")
GoExcel.CellValue("A1") = ThisDoc.FileName(False)
GoExcel.CellValue("B1") = String.Concat("", Round((iProperties.Value("Project", "Mass") / 1000), 2))
Dim i As Integer = 2
Do
	Dim oComp As ComponentOccurrence = ThisApplication.CommandManager.Pick(SelectionFilterEnum.kAssemblyOccurrenceFilter, _
			"Select component...(Press ESC to cancel)")
	If oComp Is Nothing Then GoTo SaveExcel
	Dim oDef As ComponentDefinition = oComp.Definition
	Dim oDoc As Document = oDef.Document
	Dim oMass As Double = Round(oDoc.PropertySets.Item("Design Tracking Properties").Item("Mass").Value / 1000, 3)
	GoExcel.CellValue("A" & i) = oDoc.DisplayName
	GoExcel.CellValue("B" & i) = oMass
	i += 1
Loop
SaveExcel :
GoExcel.Save
GoExcel.Close

[karram]

Hello,

What i means i need mass for particular sub assembly and sub parts into Excel in Main assembly.

For Example as per below image, In main assembly - Always i need Mass for Assy_A, Assy_B, Part_B, Part_D into Excel without any select component function. In my project i used same name for sub assembly and parts.

Hope now i give some clear explanation. Thanks

[Andrii_Humeniuk · Accepted solution]

Thank you for your patience and detailed description, it is really important to understand the question. Because there are many variations of solving the problem. Try this code, please don't forget to enter your components details (line 2).

Dim sPathExcel As String = "C:\Users\AndriiHumeniuk\Documents\YourExcel.xlsx"	
Dim sListName As New List(Of String) From {"Assy_A", "Assy_B", "Part_B", "Part_D"}
Dim sWriteComp As New List(Of String)
GoExcel.DisplayAlerts = False
GoExcel.Open(sPathExcel, "Sheet1")
GoExcel.CellValue("A1") = ThisDoc.FileName(False)
GoExcel.CellValue("B1") = String.Concat("", Round((iProperties.Value("Project", "Mass") / 1000), 2))
Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
Dim oDef As AssemblyComponentDefinition = oDoc.ComponentDefinition
Dim oOccDef As ComponentDefinition
Dim oOccDoc As Document
Dim oMass As Double
Dim i As Integer = 2
Dim sOccName As String
For Each oOcc As ComponentOccurrence In oDef.Occurrences
	sOccName = oOcc.Name.Substring(0, oOcc.Name.IndexOf(":"))
	If Not sWriteComp.Contains(sOccName) Then
		If sListName.Contains(sOccName) Then
			sWriteComp.Add(sOccName)
			oOccDef = oOcc.Definition
			oOccDoc = oOccDef.Document
			oMass = Round(oOccDoc.PropertySets.Item("Design Tracking Properties").Item("Mass").Value / 1000, 3)
			GoExcel.CellValue("A" & i) = sOccName
			GoExcel.CellValue("B" & i) = oMass
			i += 1
		End If
	End If
Next
GoExcel.Save
GoExcel.Close

[karram]

Hello,

Below Code works perfect.

But my requirements is each part and sub assembly paste in Excel with specific cell, we need to define the cell name.

For Example Assy_A as M23 cell, Assy_B as M24 cell, Part_B as C16 cell, Part_D as E16 cell like that. User will define the cell name.

Thanks for your support.

[Andrii_Humeniuk · Accepted solution]

Try this code. Replace the name of your excel - line 2, the names of your components and write their properties in excel. If you don't want information about the main assembly, then comment out lines 8 and 9.

Sub main
	Dim sPathExcel As String = "C:\Users\AndriiHumeniuk\Documents\YourExcel.xlsx"	
	Dim sListName As New List(Of String) From {"Assy_A=L5+M5", "Assy_B=L6+M6", "Part_B=L8+M8", _
												"Part_D=L10+M10", "Part_C=L1+M1"}
	Dim sWriteComp As New List(Of String)
	GoExcel.DisplayAlerts = False
	GoExcel.Open(sPathExcel, "Sheet1")
	GoExcel.CellValue("A1") = ThisDoc.FileName(False)
	GoExcel.CellValue("B1") = String.Concat("", Round((iProperties.Value("Project", "Mass") / 1000), 2))
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
	Dim oDef As AssemblyComponentDefinition = oDoc.ComponentDefinition
	Dim oOccDef As ComponentDefinition
	Dim oOccDoc As Document
	Dim oMass As Double
	Dim i As Integer = 2
	Dim sOccName, pathName, pathDisc, pathMass, pathMid As String
	For Each oOcc As ComponentOccurrence In oDef.Occurrences
		sOccName = oOcc.Name.Substring(0, oOcc.Name.IndexOf(":"))
		If Not sWriteComp.Contains(sOccName) Then
			pathName = GetFullNameAndPath(sListName, sOccName)
			If String.IsNullOrEmpty(pathName) Then Continue For
			pathMid = pathName.Remove(0, pathName.IndexOf("=") + 1)
			pathDisc = pathMid.Substring(0, pathMid.IndexOf("+"))
			pathMass = pathName.Remove(0, pathName.IndexOf("+") + 1)
			sWriteComp.Add(sOccName)
			oOccDef = oOcc.Definition
			oOccDoc = oOccDef.Document
			oMass = Round(oOccDoc.PropertySets.Item("Design Tracking Properties").Item("Mass").Value / 1000, 3)
			GoExcel.CellValue(pathDisc) = sOccName
			GoExcel.CellValue(pathMass) = oMass
			i += 1
		End If
	Next
	GoExcel.Save
	GoExcel.Close
End Sub

Private Function GetFullNameAndPath(sListName As List(Of String), sOccName As String) As String
	For i As Integer = 1 To sListName.Count
		If sListName.Item(i - 1).Contains(sOccName) Then Return sListName.Item(i - 1)
	Next
	Return Nothing
End Function

[karram]

Perfect thanks