CATIA macro and Excel

June 1, 2020June 1, 2020 Harun 15 Comments catia macros

CATIA macro and Excel

“Your connections to all the things around you literally define who you are” Aron D. O’Connell

Introduction

Excel and CATIA are one of the most used programs in the world, so a combination of these two pushes boundaries even more. CATIA software engineers made a lot of connections with the excel possible. Some examples of these implementations are Part parameterization (excel table is used to store info about part variants), Export stock lists in Excel table, control design, etc.

Absolutely when we talk about CATIA macros and automation, Excel is also very important. Previously we mentioned stock lists. The only logical way to get data from model and store in Excel file in order to fill those stock lists is to use CATIA macro.

Having this in mind we decided to write this post and help you overcome unknown about this subject.

There are a lot of questions about this subject, such as http://www.coe.org/p/fo/et/thread=27860

https://www.eng-tips.com/viewthread.cfm?qid=367762

https://www.eng-tips.com/viewthread.cfm?qid=438183

In addition, we think that you will be able to overcome the problems described in this forum posts, after reading this post.

Examples of CATIA macro and Excel connection

Furthermore there a lot of options when we talk about communication between CATIA macro and Excel. So we will try to show you the most common.

Before that, we need to start an Excel application and set some basic things.

Now we can go furthermore and do some action too. So one of the most common actions is to write in an Excel cell. It’s one very simple line however in general, it will certainly do everything, depending on how you use it. You can put in any kind of loop in addition to get a lot. Of course you just need to specify in which cell you want to write.

Indeed a loop is basic stuff in any kind of code. Let’s make a simple combination of the previous line and for loop to write numbers from 1 to 10.

Also, we can write in both directions using two for loops.

Those were options for writing into an Excel cell. You can also retrieve info from cells. We used a MsgBox command to do it. Of course, you can pass this info anywhere you want.

Almost all infos from CATIA are stored in some sort of table. Here are just basic info about cell width and color, of course, there are a lot of options to set about the cell.

Finally, we will use all of these information mentioned before in order to create a little bit more advanced macro. There a lot of questions about exporting points coordinates from CATIA to Excel. So there are a few steps. Firstly, we need from user to select points. After that use for loop to control data extraction from selection and also to export all info we need in Excel table.

Also if you want to read more about multiple selections please visit this CATIA macro selection

Dim oSel as Selection
Set oSel = CATIA.ActiveDocument.selection

Dim Filter(0)
Filter(0)="Point"

Dim F_Point
F_Point = oSel.SelectElement3(Filter, "Select points for coordianten export", False, CATMultiSelectionMode.CATMultiSelTriggWhenUserValidatesSelection, False)

Dim coords(2) as Variant

For x=1 to oSel.count

set point=oSel.item(x).value
point.getcoordinates(coords)
objsheet1.cells(10+x,1)=oSel.item(x).value.name
objsheet1.cells(10+x,2)=coords(0)
objsheet1.cells(10+x,3)=coords(1)
objsheet1.cells(10+x,4)=coords(2)

Next 'x

Full code

Here is the full code of this macro. Using these simple methods we showed that it can absolutely achieve a lot in automation.

Sub CATMain()

'Excel Connection 
set objexcel=CreateObject("Excel.Application")
objexcel.Visible=True
Set objWorkbook= objexcel.workbooks.Add()
set objsheet1=objWorkbook.sheets.item(1)

'How to write in Excel cell
objsheet1.cells(1,1)="Catiavbmacro.com"

'How to write in an Excel cell with for loop
For i=1 to 20
objsheet1.cells(2,i)=i
Next


'How to write in Excel cell in two directions
For i=1 to 10
 For j=1 to 5
 objsheet1.cells(3+j,i)=j
 Next 'i
Next 'j

'How to read from excel cell
MsgBox objsheet1.cells(1,1)


'Making Excel table 
objsheet1.cells(10,1)="Point Name"
objsheet1.cells(10,2)="X"
objsheet1.cells(10,3)="Y"
objsheet1.cells(10,4)="Y"

'How to change width and color of a cell
objsheet1.Range("A10").ColumnWidth = 15
objsheet1.Range("A10").interior.color = RGB(180, 181, 178)
objsheet1.Range("B10").interior.color = RGB(180, 181, 178)
objsheet1.Range("C10").interior.color = RGB(180, 181, 178)
objsheet1.Range("D10").interior.color = RGB(180, 181, 178)

'Exporting point coordinates to the excel using multiselection and for loop
Dim oSel as Selection
Set oSel = CATIA.ActiveDocument.selection

Dim Filter(0)
Filter(0)="Point"

Dim F_Point
F_Point = oSel.SelectElement3(Filter, "Select points for coordianten export", False, CATMultiSelectionMode.CATMultiSelTriggWhenUserValidatesSelection, False)

Dim coords(2) as Variant

For x=1 to oSel.count

set point=oSel.item(x).value
point.getcoordinates(coords)
objsheet1.cells(10+x,1)=oSel.item(x).value.name
objsheet1.cells(10+x,2)=coords(0)
objsheet1.cells(10+x,3)=coords(1)
objsheet1.cells(10+x,4)=coords(2)

Next 'x
End Sub

15 thoughts on “CATIA macro and Excel”

Alberto
February 10, 2021 at 9:15 am

Good morning
I need to create a macro to assign a value to a specific parameter present in all parts of a product.
There will be many parts in the product
Is it possible to have this service?
I can pay the price of the job
Alberto
- HarunPost author
  February 10, 2021 at 9:36 am
  
  Hello, I sent answer to your mail.
vijay
August 31, 2021 at 4:41 am

CATIA MACRO FOR CLASH REPORT GENERATION IN YOU TUBE. NEED EXACT MACRO FOR EM AND WILLING TO BUY
- HarunPost author
  August 31, 2021 at 8:41 pm
  
  We sent you mail
samira
May 17, 2022 at 10:00 am

Hello, is it possible to read data from bill of meterial in catia, then writes it in excell?
- HarunPost author
  May 17, 2022 at 10:06 am
  
  Hello, yes ofc. You can read data from the properties of the parts, i guess that you have created properties for Bill of material.
Mehraban
June 29, 2022 at 5:44 am

hello ,your Macro isn’t work 🙁
Do I need to do anything to get started? I just opened the Drawing that has the table
youri
August 16, 2022 at 1:15 pm

Hello, i need to export a measure from catia to exel and i don’t know how to get the measure parameter, is it possible ?
Kamal
August 25, 2022 at 8:47 am

Hi,
I want a macro for renaming the bodies in .CATPart.
please let me know about it..
Also let me know is there any trainings provided for CATIA V5 Macros
Thanks & Regards,
Kamal.
mail id : medakamalakar312@gmail.com
Watsapp # +91 9985445511
Büşra
September 23, 2022 at 3:08 pm

Hi Harun,

I use “GSD_PointSplineFromExcel” file in the main Catia folder and add my points to this file. But when I run the macro for create points in Catia, coordinates becomes smaller. For instance, my first point is (0,-90,10) but it reads as (0,-9,1). It seems a wrong decimal. Could you advice anything?
Ash Adul Haque
January 5, 2023 at 6:53 am

Hi,
Can we export stock size as length, width and height of parts according to their part numbers from a product into an excel?
Is it possible?
- HarunPost author
  January 5, 2023 at 9:07 am
  
  Hello,
  Yes it is. You need to loop through the CATIA tree and get it.
  
  Also take in count if you more instances.
Sangeeth
July 6, 2023 at 12:56 pm

Hello,
I need to Exact a point present in the geometrical set and export it to csv file
LA
November 20, 2023 at 10:10 am

Hello,

I have a catia BOM macro and I would like to find the last row of the excel sheet to then fill in a summary.
Is that possible?

Cheers,
L.
- HarunPost author
  November 21, 2023 at 5:17 am
  
  Hello,
  Yes it is, I forgot how to do it.
  But I did it more times, maybe you can track number of rows and then just go to the next one.
  
  Also I think you can make one loop that will go through the cells and check is it empty, and set counter, so when it is that’s the cell and just go to the next xt one. I hope this help.