Hi all
I allow myself to create a topic that seems to be related to the subject: http://catiav5.forumactif.org/t1407-macro-pour-ajout-suppression-propriete
I need to create a small Catia script to update the properties of the parts of a Catia assembly, from a large excel document which is in fact the BOM. So I want to update these properties automatically and not have to open the properties of all the parts one by one..! The idea is to identify the part by its CATIA name and link it to the corresponding line in Excel, and then finally update the properties when this identification is done.
I tried using the code from the topic above but to no avail.
By the way, I found a video that is exactly what I'm looking for (from Excel to CATIA), but unfortunately there is no indication on the script.
http://youtube.com/watch?v=IvG4U_6AcNg
Thank you in advance for your help!
Kevin
Hello
I'm not a "macro" expert, sorry but I watched the video and it's a comfort macro for the user, it creates the Excel table using the "string" type properties of CATIA files such as "reference", "designation", "revision", etc.
It saves the user from selecting the command to create the parameterization table and the manual selection of the 8 properties ("string" parameters) and creates the table
Except if I understand correctly it 's from an Excel (BOM) you want to update any ASS Catia ?
What we see in the video is the link between an Excel parameterization table and its assembly (UNIQUE).
Without using a macro, you can update the properties of an assembly from an Excel file with formatting (BOM). All you have to do is create a spreadsheet in the workbook that contains the collones ("reference", "designation", "revision", etc.), the contents of these collons are extracted from the BOM sheet, and the collonnes are associated with the CATIA parameters (the names of the collones and parameters being the same, it's quite fast).
Hello Franck,
Thank you for this answer.
Indeed, a good part of the video shows the creation of the excel table from the CATIA assembly, but from 2:24 in the video, it uses the opposite, i.e. updating the properties of its parts.
You have indeed understood correctly, I want to update the parts of a catia assembly thanks to my excel file (BOM). I didn't know it was directly possible this way, can you explain to me how to extract the information from my BOM sheet to my CATIA assembly?
Thank you for your help!
Kevin
This is the normal behavior of CATIA: changes to a parameterization table are automatically reflected in the linked CATIA files.
The use of macro makes the process easier (it's useful when you do this every day).
To associate your BOM with CATIA you have to go through an intermediate sheet that associates a column of the BOM with a corresponding column with the CATIA Name of the parameter.
Indeed, it seems to correspond to what I am looking for!
I didn't know about configurable arrays.
So I made a table linked to my excel sheet, I tried on one part only and it works! However, when I try on an assembly, only the properties of the assembly are changed by the first line of my excel table... I actually already have the names of the parts entered into my product, and I can't seem to get this array to link so that it recognizes the names of the parts and then associates the corresponding properties, you know what I mean?
Once again, thank you for your help!!
(Sorry for my posts, I can't use accents..)
Kevin
The easiest way is to generate the array from the CATIA assembly.
That way everything is well associated and then paste the BOM sheet into this Excel workbook and create the Excel formulas.
I look at what help I have on this subject.
Very good idea! Once again, there is only the assembly that is found in the table, do you know if there is an option to check somewhere that also allows you to add the parts of this assembly (as well as the parts of the sub-assemblies)?
Kevin
Look at point N° 3 creation of the parameterization table
http://www.lynkoa.com/tutos/3d/catpart-pilote-est-famille-de-pieces-catiav5
It's the same thing for an ASS same starting filter (String) to filter on the properties "reference", "designation", "revision", etc.
When choosing the orientation of the table I took horizontal chosen:
H the properties will be at the head of the column.
V properties will be at the head of the lines
Choose according to the orientation of your BOM sheet
EDIT:
So, I tried by doing the steps in the video, but I don't have any Settings as you can see in this video since I only want to change the string properties as you noticed.
By trying with a small assembly (there are hundreds of parts in the one whose properties I finally want to parameterize) and I once again have only one row in my table as you can see in the attached image .
Do you know where this can come from?
EDIT: I just saw your edit, I'll try adding all the properties in this case, but given the number of coins I don't know if it will work
capture.png
I've tried, and it's starting to really get closer to what I want!
I have one last small problem, whether it's vertical or horizontal, the properties of the different parts follow one another, like this:
Do you know how to correct this?
Thank you for the time devoted to my little problem!
If you have a lot of coins you can add an additional filter on the name
EDIT: Try to redo the manipulations because normally it gives this.
Indeed, that's what I have too. However, here's what the array I want to get looks like because that's how my BOM is built:
Do you know what I mean? Is it possible?
I just understood.
PB is the sorting of the parameters and the order of the components of the assembly.
If, as in the previous answer, you filter successively by name, you will get a sorted column
Then you have to find the Excel formula that returns the content of the right collone in the selection of sorted line corresponding to the search for the name
I look at what I can do in Excel
That's indeed the problem. Very well, thank you. I'm also going to try on my own.
A solution
Research
=VLOOKUP(STXT(A1; 2; 11); Leaf2! A1:D4; 1; FALSE)
Returns the first 11 characters from the left from the second
=VLOOKUP(STXT(A1; 2; 11); Leaf2! A1:D4; 1; FALSE)
Leaf 2 Capsule Range or Will Be Searching
=VLOOKUP(STXT(A1; 2; 11); Leaf2! A1:D4; 1; FALSE)
1 Column No.
In my example
1 = Leaf Column.2 (Part Number)
2 = Column B sheet.2 (Reference)
FALSE I'm looking for the exact value
EDIT: I have attached the Excel file
formules.xlsx
I don't think I understood your message and the formulas, here is what I have with your excel file:
Is this the result you have?
EDIT: As much as for me I hadn't seen sheet 2, I'm looking at this
I updated the example because copying the formulas down it also incremented the search range
In reality I should have made a copy and paste of the formula and only changed:
=VLOOKUP(STXT(A1; 2; 11); Leaf2! A1:D4; 1; FALSE)
The line number that increments (A.)
The N in collone. (This step must be able to be improved so that you don't have to do it manually).
Edit , decidedly tired at the end of the week, I just looked for the right one
formules.xlsx
Well, it took me a little time but I understood your approach and I like it a lot! It works perfectly well on your excel example.
However, I can't get the VLOOKUP function to work on my excel. I have to use VLOOKUP since this English version, but it doesn't work. Do you see a mistake?
EDIT: That's okay, it works on a small assembly. I'm going to have a little fun with this and I'll get back to you pretty soon.
EDIT 2: For the small evolution of the formula, here's what it looks like:
I sometimes have parts names with more than 11 characters, I have to modify by hand, I'll see if it's not automatable
Nice job Franck, thank you for your help.
Bonus Question:
Is it possible to perform the same procedure but for the masses?