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.