catia v5 export tree through VBA macro

Question

I need to export the Catia Spec tree to use as a BoM.

The Export Should:

Go to Excel and will use the WalkDownTree function.
Have the PartNumber, Nomenclature and a User Added Property called "Sinex Ref".
It will also have to make sure that the Exported Tree ignores Parts and Products called "Ref".
Present the Quantity of each item using the PartNumber.
Include the deactivated parts but mention that they are deactivated.

I'm new to Catia and VBA and have come up with the following (I made adjustments to other macros that i have found but noticed that they ignore the children in the Tree). Currently the macro generates the Excel file and in the same cell cycles through all of the parts and children in the spec tree, regardless if they're deactivated or not.

Sub CATMain()


' ********* is the current document a CATIA Product **************

    If CATIA.Documents.Count = 0 Then

        MsgBox "There are no CATIA documents open. Please open a CATIA document and try again.", ,msgboxtext

        Exit Sub
    End If


    If InStr(CATIA.ActiveDocument.Name, ".CATProduct") < 1 Then

        MsgBox "The active document is not a Product. Please open a CATIA Product and try again.", ,msgboxtext

         Exit Sub
    End If


' ************* General declarations for the Active CATIA session *****************

    Dim oProdDoc As ProductDocument
    t = 1
    Set oProdDoc = CATIA.ActiveDocument
    Dim oRootProd As Product
    Set oRootProd = oProdDoc.Product
    Dim par As Parameters
    Set par = oRootProd.UserRefProperties
    Dim SinexRef As String


' *************** begin spec tree scroll ******************

    Call WalkDownTree(oRootProd)
End Sub

Sub WalkDownTree(oInProduct As Product)
        Dim oInstances As Products
    Set oInstances = oInProduct.Products

    On Error Resume Next
    Set Excel = GetObject(, "EXCEL.Application")

    If Err.Number <> 0 Then
        Set Excel = CreateObject("EXCEL.Application")
        Excel.Visible = True
        Excel.Workbooks.Add 
    End If

    If t <> 1 Then
        for i=1 to oInProduct.Count


'**************************** Export title ***************************

            row=2

            col=1
            Excel.Columns.Columns(1).Columnwidth = 5

            Excel.Columns.Columns(2).Columnwidth = 15
            Excel.Cells(row,col+1).Value = "CATProduct:"

            Excel.Cells(row,col+1).Font.Bold = true

            Excel.Cells(row,col+1).HorizontalAlignment = 3
            Excel.Cells(row,col+2).Value = CATIA.ActiveDocument.Name

' **************************** Export column titles ***************

            row=4
            Excel.Cells(row,col+1).Value = "Instance Name"
            Excel.Cells(row,col+1).Font.Bold = true

            Excel.Columns.Columns(2).Columnwidth = 20

            Excel.Cells(row,col+1).borders.LineStyle = 1
            Excel.Cells(row,col+1).HorizontalAlignment = 3
            Excel.Cells(row+2,col+1).Value = oInProduct.ReferenceProduct.PartNumber

            Excel.Cells(row,col+2).Value = "Ref"

            Excel.Cells(row,col+2).Font.Bold = true

            Excel.Columns.Columns(3).Columnwidth = 15
            Excel.Cells(row,col+2).borders.LineStyle = 1

            Excel.Cells(row,col+2).HorizontalAlignment = 3
            Excel.Cells(row+2,col+2).Value = oInProduct.ReferenceProduct.Nomenclature

            Excel.Cells(row,col+3).Value = "Quantity"

            Excel.Cells(row,col+3).Font.Bold = true

            Excel.Columns.Columns(4).Columnwidth = 15
            Excel.Cells(row,col+3).borders.LineStyle = 1
            Excel.Cells(row,col+3).HorizontalAlignment = 3
            Excel.Cells(row+2,col+3).Value = 1 'insert item quantity corresponding to PartNumber

            Excel.Cells(row,col+4).Value = "SinexRef"

            Excel.Cells(row,col+4).Font.Bold = true

            Excel.Columns.Columns(5).Columnwidth = 15
            Excel.Cells(row,col+4).borders.LineStyle = 1

            Excel.Cells(row,col+4).HorizontalAlignment = 3
            Excel.Cells(row+2,col+4).Value = 1 'insert Sinex Ref corresponding to PartNumber

            t = t + 1
        Next
    End If

    Dim k As Integer
    For k = 1 To oInstances.Count
        Dim oInst As Product
        Set oInst = oInstances.Item(k)

        Call WalkDownTree(oInst)
    Next
End Sub

garthhh · Answer 1 · 2017-09-20T20:27:31.707

Assuming by

in the same cell cycles through all of the parts and children in the spec tree

you mean that it is writing/overwriting data from CATIA in the same cell, that is because you aren't incrementing anything regarding Excel rows/columns.

I personally would create headers for things like CATProduct, Instance Name, etc. then put pure data below instead of repeating these identical headers every single time, but your format will work as well, it might just be more difficult to summarize data in Excel.

Anyway, to maintain your existing format, you need to increment your Row at the end of your loop, around where t is incremented.

In the existing loop, it appears that rows 2-6 are used (5 rows total) for the first oInProduct. There is a row = 2 at the beginning of the loop which needs to be put just before the loop, this means it will start from the second row. There is also a row = 4 inside the loop which needs to be changed, we can use row = row + 2 to get the same effect. Then, at the end of the loop, we increment again to reach that total of 5, so use row = row + 3.

row = 2

for i = 1 to oInProduct.Count

    '**************************** Export title ***************************

    col=1
    Excel.Columns.Columns(1).Columnwidth = 5

    ...
    row = row + 2 'previously row = 4
    ...

    Excel.Cells(row,col + 4).HorizontalAlignment = 3
    Excel.Cells(row + 2,col + 4).Value = 1 'insert Sinex Ref corresponding to PartNumber

    t = t + 1
    row = row + 3

Next

Essentially create a row of headers, like Excel.Cells(1,1).Value = "CATProduct", Excel.Cells(1,2).Value = "Instance Name", and so on, so all of those headers are only in one row. You only need to do this once, then create a loop (similar to what you already have) that just inserts the proper information in the same column as the associated header. This will allow you (or others) to more easily summarize/filter/sort data in the Excel sheet. — garthhh, Sep 20 '17 at 21:07
I have made the alterations that you suggested but the result is the same, the same cell is being overwritten with the same values. — Castella, Sep 21 '17 at 10:10

score 0 · Answer 2 · answered Jan 18 '19 at 20:15

0

Try -->Analyze-->Bill of Material-->Define Format (for optional options)-->>Save as-->File format as .xls

Or: -->File--> Save As --> Filetype:txt...Sure it's txt but maybe you can convert to .xls (expecially if you have missing licenses)

answered Jan 18 '19 at 20:15

Julian Krebs

5
2

2

This doesn't answer the question, which was how to do it via macro – Gibolt Jan 18 '19 at 20:36

score 0 · Answer 3 · edited Jan 14 '20 at 15:21

I have used AssemblyConverter object which is available in Catia libraries to extract BOM.I found this when I recorded macro using the steps mentioned in above comment.
Try -->Analyze-->Bill of Material-->Define Format (for optional options)-->>Save as-->File format as .xls
I think this is the simplest and fast. Also, we can change format and location of file too.

Recorded Macro:

Sub CATMain()

    Dim productDocument1 As ProductDocument
    Set productDocument1 = CATIA.ActiveDocument

    Dim product1 As Product
    Set product1 = productDocument1.Product

    Dim assemblyConvertor1 As AssemblyConvertor
    Set assemblyConvertor1 = product1.GetItem("BillOfMaterial")

    Dim arrayOfVariantOfBSTR1(4)
    arrayOfVariantOfBSTR1(0) = "Quantity"
    arrayOfVariantOfBSTR1(1) = "Part Number"
    arrayOfVariantOfBSTR1(2) = "Type"
    arrayOfVariantOfBSTR1(3) = "Nomenclature"
    arrayOfVariantOfBSTR1(4) = "Revision"
    Set assemblyConvertor1Variant = assemblyConvertor1
    assemblyConvertor1Variant.SetCurrentFormat arrayOfVariantOfBSTR1

    Dim arrayOfVariantOfBSTR2(1)
    arrayOfVariantOfBSTR2(0) = "Quantity"
    arrayOfVariantOfBSTR2(1) = "Part Number"
    Set assemblyConvertor1Variant = assemblyConvertor1
    assemblyConvertor1Variant.SetSecondaryFormat arrayOfVariantOfBSTR2

    assemblyConvertor1.[Print] "XLS", "C:\Users\Desktop\BOM.xls", product1

End Sub

catia v5 export tree through VBA macro

3 Answers3