Script to export properties to Excel?

Yes, it can be done.

Bellow there is something similar, I don't have time to modify for Excel. Is also a text file, not Excel, is saving file name and extension in txt title file and inside. Other properties can be saved also. Is a CATScript.

To export spec tree in a text file is enough to save as text file (a product).

Sub CATMain()

Dim partDoc As PartDocument
Set partDoc = CATIA.ActiveDocument

Dim partProd As Product
Set partProd = partDoc.Product

Dim filename As String
filename = CATIA.ActiveDocument.Name
Dim path As String
path = CATIA.ActiveDocument.Path

Set Datos = CATIA.FileSystem.CreateFile(path & "\" & CATIA.ActiveDocument.Name & ".txt" , True)

Set ostream = Datos.OpenAsTextStream("ForAppending")

ostream.Write CATIA.ActiveDocument.Name & Chr(10)
ostream.Close

MsgBox "Check results in folder " & chr(10) & path & "\" & chr(10) & chr(10) &"File:" & chr(10) & partDoc.Name & ".txt" & chr(10)

End Sub

Regards
Fernando

Alright, I figured out how to export properties from a single part into an Excel file. Now I would like to be able to open a product and run a macro which would then basically run this script on every part and export the properties into the same Excel sheet. Any ideas how to add on to my code to accomplish this?

Here's my code to export properties from a part:

Sub CATMain()

'Load the part

Dim myDocuments As Documents
Set myDocuments = CATIA.Documents

Dim myDocument As PartDocument
Set myDocument = CATIA.ActiveDocument

Dim myPart As Part
Set myPart = myDocument.Part

Dim myProduct As Product
Set myProduct = myDocument.GetItem(myPart.Name)

Dim myParameters As Parameters
Set myParameters = myProduct.UserRefProperties

Dim getThickness As String
getThickness = myParameters.Item(myPart.Name & "\Properties\Thickness").ValueAsString

Dim getMaterial As String
getMaterial = myParameters.Item(myPart.Name & "\Properties\Material").ValueAsString

Dim getMass As String
getMass = myParameters.Item(myPart.Name & "\Properties\Mass").ValueAsString

Dim partName As String
partName=myProduct.name

'to excel

Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim Sheets As Object
Dim Sheet As Object
Dim worksheet As Excel.worksheet
Dim myworkbook As Excel.workbook
Dim myworksheet As Excel.worksheet


On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If

Excel.Visible = True

'load a sheet

Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add

'download the properties
'row one
Excel.Cells(1,1)="Part Number"
Excel.Cells(1,2)="Thickness"
Excel.Cells(1,3)="Material"
Excel.Cells(1,4)="Mass"

'row 2
Excel.Cells(2,1)= partName
Excel.Cells(2, 2) = getThickness
Excel.Cells(2, 3) = getMaterial
Excel.Cells(2, 4) = getMass

End Sub

Hi,

I would suggest to use a custom Bill of Materials in Assembly Workbench, something like in attachment (of course it can be improved).

Using BoM is much easier to do what you want.

Regards
Fernando

The BOM helps a lot and works well, thanks! There is one thing I would like to add but I'm not sure if it is even possible. Under each part in my assembly there is an additional body called "Permamnent Fasteners" which contains nuts, bolts, etc. Is it possible to call out each of these fasteners in the BOM as well and if so how would you do it?

If you have symbols for nut, bolts, etc in a body (or Geometrical Set ? ), symbols (wireframe) cannot be inserted in BoM as far as I know (there is a special workbench called Holes and Fasteners which can do this but I don't know if its your case). If they are solids then you can do a work around, meaning you can transform your part in a catproduct and then you can run BoM.

If I remember correctly there is such a macro in my CATIA Portable Script Center.

Anyway, what you have for those fasteners?



Regards
Fernando

My fasteners are dumb solids, each in their own individual body within a part file. I have attached a screenshot of the spec tree to help you understand.

This is what I would do it to have also the fastners in BoM

Convert the CATPart with dummies in a CATProduct with that macro mentioned above, replace this CATPart with the CATProduct, then get the BoM.

Regards
Fernando

That method of converting the part files into products works...but at the same time it doesn't work for my application. Converting each part into a product is not ideal. Do you have any other ideas?

You can batch convert the CATParts in CATProduct and then batch replace the CATParts with corresponding CATProducts. Is not so nice but it can be done. If you find that this method is not good for you, I can understand but unfortunately I don't have any other idea.

If you have a specific application (I mean, if you've done an exe file in vb6 or something else) , you can launch from there other CATScripts which can do the job.

Regards
Fernando

I finally figured it out. I basically have two loops, the first loop goes through each part and the second loop goes through all the bodies of the part. Here's a small piece of the code:

Dim i As Integer
For i=1 to partcount

Dim j As Integer
bodyNumber=part1.Bodies.Count

For j=1 to bodyNumber

Dim body1 As Body
Set body1=part1.Bodies.Item(j)
namebody=body1.name

'row 3
Excel.Cells(RwNum+1,1)= namebody
RwNum = RwNum + 1

Next 'j
Next 'i

Now onto the next issue. There are a few bodies named "FINAL_BODY" which I want to delete. I know in Excel I can run a macro which will delete an entire row in the spreadsheet based upon a given condition. Here's the macro which works in Excel:

Sub Delete_FINALBODY_Rows()

Dim r As Long

For r = Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

If UCase(Left(Cells(r, "A").Text, 10)) = "FINAL_BODY" Then Rows(r).Delete

Next r

End Sub

Is there a way to run this Excel macro directly inside my catscript at the end? So it would export a list to Excel of all of my parts, bodies, and desired properties as described before but would then delete any rows with "FINAL_BODY" in column A. Is it possible?

I figured it out. I simply add a If Then statement. Alright, hopefully this is my last question and then the script will be perfect. Currently, my properties exporter script only works if you've got one product open. But I have situations where I may have a product within a product within a product. How can I change my code to basically go to the last level product and then run what I already have (of running through each part, body, etc.)? Here's my current code:



Sub CATMain()

Dim productDocument1 As Document

Dim product1 As Product

Dim products1 As Products

Set productDocument1 = CATIA.ActiveDocument

Set product1 = productDocument1.Product

Set products1 = product1.Products

partcount=product1.Products.Count

msgbox"number of parts is :"&partcount



'some Excel info then I go into my first loop through the parts:

Dim RwNum as Integer

RwNum = 2

Dim i As Integer

For i=1 to partcount

Dim partDoc1 As PartDocument

Set partDoc1=products1.Item(i)

'and so on...



What's the best way to deal with multiple products or products within products?

My script to export information to Excel has been working well but I've recently run into an issue. Currently, the script loops through every part within a product and then loops through every body within each part. Well, I have a few assemblies I need to run the script on but there are cgrs included within the product and my script is skipping right over them.

For i=1 to partcount

Dim partDoc2 As PartDocument

Set partDoc2 = partdoc1.GetMasterShapeRepresentation(True)

Dim part1 as Part

Set part1 = partDoc2.Part

How do you differientiate a cgr from a part file?