Export a CATIA table to Excel with VBA

This simple script copies the content of CATIA table into an Excel worksheet. Before launching it, please ensure that:

Active document is a CATIA drawing.
Active view contains a CATIA table.
If there are more tables in the active view only first one is going to be exported.

How it works

In a main sub Export we declare a 2D variant array with the same size as our CATIA table. To get table dimensions we use properties of DrawingTable object NumberOfRows and NumberOfColumns. In a following loop, we get the content of each cell using GetCellString and assign a cell text into an array item. Finally, we call ArrayToExcel procedure which is responsible for data export.


Option Explicit

Sub Export()
    ' set drawing document
    Dim drwDoc As DrawingDocument
    Set drwDoc = CATIA.ActiveDocument
    
    ' set drawing drwSheet
    Dim drwSheet As DrawingSheet
    Set drwSheet = drwDoc.Sheets.ActiveSheet
    
    ' set drawing drwView
    Dim drwView As DrawingView
    Set drwView = drwSheet.Views.ActiveView
    
    ' set drawing drwTable
    Dim drwTable As DrawingTable
    Set drwTable = drwView.Tables.Item(1)
    
    ' get number of drwTable rows
    Dim rowsNo As Long
    rowsNo = drwTable.NumberOfRows
    
    ' get number of drwTable columns
    Dim colsNo As Long
    colsNo = drwTable.NumberOfColumns
    
    
    Dim i As Long, j As Long
    ' set array size acoording to drwTable size
    ReDim arr(rowsNo - 1, colsNo - 1) As Variant
    
    ' loop through drwTable cells
    For i = 1 To rowsNo
        For j = 1 To colsNo
            ' write cell content to an array item
            arr(i - 1, j - 1) = drwTable.GetCellString(i, j)
        Next
    Next
    
    ArrayToExcel arr
End Sub

Data transfer between CATIA (or any other application) and Excel is an expensive operation that should be kept to a minimum. You can increase the speed of operation by passing arrays of data to the worksheet in a single operation rather than using loops and copy one cell at a time. This way you can copy even a large amount of data with the best performance. I find export to Excel much easier than writing to a text file.

Procedure ArrayToExcel takes a 2D variant array as an argument, creates a new Excel document and assign the content of an array into an Excel range. Up to this point the Excel instance is running hidden in a background, in a last step we show it by setting an Application.Visible property to True.


' export 2D array to Excel
Sub ArrayToExcel(arr2D() As Variant)
    ' create Excel object
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    
    ' add workbook
    Dim wbook As Object
    Set wbook = xlApp.Workbooks.Add
    
    ' set destination range
    Dim destination As Object
    Set destination = wbook.Sheets(1).Range("B2")
    
    ' copy content of 2D array into excel range
    destination.Resize(UBound(arr2D, 1) + 1, UBound(arr2D, 2) + 1).Value = arr2D
    
    ' show excel
    xlApp.Visible = True
End Sub

To test it please copy both procedures into a standard module and launch Export sub.

Export a CATIA table to Excel with VBA

How it works

One Response to “Export a CATIA table to Excel with VBA”

Leave a Reply