Method for rapidly obtaining product BOM table and sorting CATIA structure tree based on VBA
By using VBA-based methods to quickly obtain and organize CATIA structure trees, the problem of cumbersome product BOM management is solved, achieving automated and precise management, improving work efficiency, and simplifying the BOM processing flow for large products.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-26
- Publication Date
- 2026-04-07
AI Technical Summary
In the existing technology, the management and modification process of product BOM is cumbersome, especially when managing large products, it is easy to make mistakes. Manual entry is inefficient and prone to errors.
By using a VBA-based approach, the product BOM (Bill of Materials) table can be quickly obtained and the CATIA structure tree organized. The VBA language is then used to control the CATIA structure tree in Excel, enabling automated export and real-time updates of information, including defining data structure members and exporting and modifying BOM table data.
It enables automated and precise management of product BOM tables, reduces errors from manual operations, improves work efficiency, and supports the import and export of custom information, simplifying the BOM table processing flow for large products.
Smart Images
Figure CN121807853A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the field of CATIA secondary development, and more particularly, to a method for quickly obtaining a product BOM table and arranging a CATIA structure tree based on VBA. BACKGROUND
[0002] A BOM table is a file describing the structure of a product in a data format, recording all parts, subassemblies, raw materials, specifications, quantities and hierarchical relationships of a manufactured product. The BOM table runs through the whole life cycle of a product and is crucial to the design, research and development, production and improvement of a product. However, manual input is particularly tedious in the process of managing and modifying a product BOM table, especially when managing a BOM table of a large product, which is more likely to result in errors (such as incorrect material specifications and quantity deviations).
[0003] Therefore, it is necessary to develop a method for quickly obtaining a product BOM table and arranging a CATIA structure tree based on VBA.
[0004] The information disclosed in the background section of this application is only intended to deepen the understanding of the general background of the application and should not be regarded as acknowledging or implying in any form that this information constitutes prior art known to those skilled in the art. SUMMARY
[0005] The present application provides a method for quickly obtaining a product BOM table and arranging a CATIA structure tree based on VBA, which is based on VBA language and can realize the quick export of CATIA structure tree information to Excel through CATIA secondary development, and real-time update by returning the modified information in Excel to the CATIA structure tree, thereby realizing the automatic and accurate management of the product BOM table.
[0006] The present application provides a method for quickly obtaining a product BOM table and arranging a CATIA structure tree based on VBA, which is based on VBA language and can realize the quick export of CATIA structure tree information to Excel through CATIA secondary development, and real-time update by returning the modified information in Excel to the CATIA structure tree, thereby realizing the automatic and accurate management of the product BOM table. An Excel interface is obtained and an Excel document is created, a BOM table data structure member is defined and a corresponding title bar is created. The Excel document is connected to a product CATIA structure tree, and the CATIA structure tree is controlled through the Excel document based on VBA.
[0007] Preferably, the BOM table data structure member includes general part information and custom part information, wherein the general part information includes part number, instance name, level, weight, material and moment of inertia matrix, and the custom part information is defined according to product or user needs.
[0008] Preferably, controlling the CATIA structure tree by Excel includes exporting BOM from CATIA to Excel and modifying the CATIA structure tree by Excel.
[0009] Preferably, exporting BOM from CATIA to Excel includes: Obtaining information of the root node of the CATIA structure tree, i.e. the product itself; Traversing all components, parts and pieces under the root product and exporting to Excel.
[0010] Preferably, traversing all components, parts and pieces under the root product and exporting to Excel includes: When a piece is encountered, the information of the piece is directly exported, and when a component and an assembly are encountered, the information of the component and the assembly is first exported, and then the information of the pieces in the sub-level is exported by a recursive function.
[0011] Preferably, exporting BOM from CATIA to Excel based on VBA includes: First, a function for exporting information is defined, which determines whether a piece is a piece by whether the current object contains a sub-level, and if it is a piece, the data structure member information is exported, and if it is not a piece, the function is recursively called to traverse all pieces or components thereunder and export the data structure member information; In the process of exporting BOM information, the information of a conventional piece is read by sequentially calling an API function defined in the secondary development of CATIA in cooperation with the recursive function; The custom function traverses all custom piece information in the piece, and if no custom piece information in the data structure member is found, the function automatically creates the attribute in CATIA and assigns it a null value.
[0012] Preferably, it further includes: In the process of exporting piece information, a progress bar is created according to the number of components under the product, and a visual user interface is created and a termination button is set.
[0013] Preferably, modifying the CATIA structure tree by Excel includes: In Excel, each piece of information is modified to the correct content, and a recursive function is called to start from the root product and modify it level by level and piece by piece; For custom piece information, the piece is traversed and matched by a custom function, and if there is no corresponding custom piece information, the piece attribute is automatically created in the piece, and the corresponding information in Excel is written into the piece.
[0014] Preferably, it further includes: The modified parts are saved with the modified file names, and the original parts are deleted.
[0015] Preferably, saving each part with the modified file name comprises: A file management object is created, and all parts in the root product are saved in turn using a recursive function, a dictionary array of part numbers is created during the saving process, and a search is performed in the dictionary array of part numbers, and if the same part number exists, saving is not repeated.
[0016] The method of the present application has other characteristics and advantages that will be apparent from and / or set forth in the accompanying drawings and the detailed description that follows, which together serve to explain certain principles of the present application. BRIEF DESCRIPTION OF DRAWINGS
[0017] The above and other objects, features and advantages of the present application will become more apparent from the following detailed description when taken in conjunction with the accompanying drawings in which like reference characters refer to like parts throughout the several views.
[0018] Figure 1 A flow chart showing the steps of the method for quickly obtaining a product BOM table and organizing a CATIA structure tree based on VBA according to the present application is shown.
[0019] Figure 2 A flow chart showing the steps of the method for quickly obtaining a product BOM table and organizing a CATIA structure tree based on VBA according to an embodiment of the present application is shown. DETAILED DESCRIPTION
[0020] Preferred embodiments of the present application will be described in more detail below. Although the preferred embodiments of the present application are described below, it is understood that the present application can be implemented in various forms and should not be limited by the embodiments set forth herein.
[0021] Figure 1 A flow chart showing the steps of the method for quickly obtaining a product BOM table and organizing a CATIA structure tree based on VBA according to the present application is shown.
[0022] As Figure 1 shown, the method for quickly obtaining a product BOM table and organizing a CATIA structure tree based on VBA comprises: Step 101, an Excel interface is obtained and an Excel document is newly created, members of a BOM table data structure are defined and corresponding title bars are created; Step 102, connecting the Excel document to the product CATIA structure tree, controlling the CATIA structure tree through the Excel document based on VBA.
[0023] In one example, the BOM table data structure member includes general part information and custom part information, wherein the general part information includes part number, instance name, level, weight, material, and inertia matrix, and the custom part information is defined according to product or user needs.
[0024] In one example, controlling the CATIA structure tree through the Excel document includes exporting the BOM from CATIA to Excel and modifying the CATIA structure tree using Excel.
[0025] In one example, exporting the BOM from CATIA to Excel includes: Obtaining information of the root node of the CATIA structure tree, i.e., the product itself; Traversing all components, parts, and parts under the root product and exporting them to Excel.
[0026] In one example, traversing all components, parts, and parts under the root product and exporting them to Excel includes: When a part is encountered, the information of the part is directly exported, and when a component and an assembly are encountered, the information of the component and the assembly is first exported, and then the recursive function is used to export the part information in the child level.
[0027] In one example, exporting the BOM from CATIA to Excel based on VBA includes: First, define an export information function, which determines whether it is a part by whether the current object contains a child level, and if it is a part, exports the data structure member information, and if it is not a part, recursively calls the function to traverse all parts or components under it and exports the data structure member information; During the export of BOM information, the general part information defines a fixed API function in CATIA secondary development, which is used to read information in sequence during the export process in cooperation with the recursive function; The custom function traverses all custom part information in the part, and if no custom part information is found in the data structure member, the function automatically creates the attribute in CATIA and assigns it a null value.
[0028] In one example, it also includes: During the export of part information, a progress bar is created according to the number of components under the product, and a visual user interface is created and a termination button is set.
[0029] In one example, modifying the CATIA structure tree using Excel includes: Modifying each part information to the correct content in Excel, calling a recursive function to modify step by step from the root product; For custom part information, the part is traversed and matched by a custom function. If there is no corresponding custom part information, the part attribute is automatically created in the part, and the corresponding information in Excel is written into the part.
[0030] In one example, it also includes: After modification, save each part with the modified file name, and delete the original part.
[0031] In one example, saving each part with the modified file name includes: Create a file management object, use a recursive function to save all parts in the root product one by one, create a dictionary array of part numbers in the saving process, and search in the dictionary array of part numbers. If there is the same part number, it is not necessary to save repeatedly.
[0032] Figure 2 A flowchart showing the steps of the method for quickly obtaining a product BOM table and organizing a CATIA structure tree based on VBA according to one embodiment of the present application is shown.
[0033] Specifically, as shown in Figure 2 , an Excel interface is obtained and a new Excel document is created, the BOM table data structure members are defined, including regular part information (part number, instance name, level, weight, material, inertia matrix, etc.) and custom part information (usually defined according to product or user needs, generally including version number, source, description, status, change, note, etc. Information). After defining the data structure members, the corresponding title bar is created.
[0034] Connect the product CATIA structure tree, create a CATIA object in VBA, if CATIA is not opened, directly open CATIA, and enter the CATIA current product assembly page. At this time, there are two use scenarios: exporting BOM from CATIA to Excel and modifying CATIA structure tree using Excel: ①A product model is generally composed of parts, components and assemblies, wherein the parts are the smallest structural units such as screws, the components are the assemblies composed of multiple parts such as automobile engines, and the assemblies are the basic units or complete products, which have the same structure in the CATIA structure tree model, wherein the root node of the structure tree is the product itself, which is composed of assemblies, components and parts, the assemblies are composed of components and parts, and the components are composed of parts. In the process of exporting the BOM table information of all parts and components in the assembly structure tree, the information of the root node, i.e. the product itself, needs to be obtained first, then all assemblies, components and parts under the root product are traversed, when a part is encountered, the information of the part is directly exported, when a component or assembly is encountered, the information of the component or assembly is exported first, and then the part information in the child level is exported through a recursive function. The implementation process in VBA is as follows: first, define an export information function, which determines whether the current object is a part by whether it contains a child level, if it is a part, the data structure member information is exported, if it is not a part, the function is recursively called to traverse all parts or components under it and export the data structure member information. In the process of exporting the BOM information, the information of the conventional parts is defined by a fixed API function in the CATIA secondary development, which is called to read the information in sequence in the exporting process with the recursive function, and additional functions need to be defined to obtain some part information such as material and inertia matrix. The self-defined part information is usually created by the user, and the custom function traverses all the self-defined part information in the part, if no self-defined part information is found in the data structure member, the function automatically creates the attribute in CATIA and assigns it a null value. In the process of exporting the part information, a progress bar is created according to the number of parts and components under the product, a visual user interface is created and a termination button is set, if the exporting process takes too long or for other reasons, the exporting process can be exited at any time.
[0035] ②When the CATIA product structure tree is modified in Excel, a recursive function also needs to be called for modification, which generally needs to modify the part number, instance name and user-defined part information of the part. First, each part information needs to be modified to the correct content in Excel, then the recursive function is called to start from the root product and modify each level one by one, and for the user-defined part information, the custom function is used to traverse and match the parts, if there is no corresponding user-defined part information, the part attribute is automatically created in the part, and the corresponding information in Excel is written into the part.
[0036] Only modifying the part number on the CATIA structure tree does not modify the part name in the folder, so each part needs to be saved with the modified file name, and the original part is deleted. First, create a file management object, and then use the recursive function to save all subparts in the root product one by one. A dictionary array of part numbers needs to be created during the saving process. For subparts, the part number is unique, but there may be multiple repeated parts or components in a product, so a search needs to be performed in the dictionary array of part numbers during the saving process. If there is a same part number, it does not need to be saved repeatedly, otherwise CATIA will report an error.
[0037] In order to understand the scheme and effect of the embodiments of the application, a specific application example is given below. Those skilled in the art should understand that the example is only for the purpose of understanding the application, and any specific details are not intended to limit the application in any way.
[0038] Example 1
[0039] Open the CATIA product assembly body that needs to export the BOM table, and run the program. Two choices appear: input 1 exports the BOM of the current product, and input 2 feeds back the part information modified in Excel to the CATIA structure tree.
[0040] When input 1 is input, the program automatically clears all content formats in the table and establishes a title bar, mainly including part number, level, instance name, weight, and centroid. Then the program starts to export part information from the product root node level by level, and the export progress appears on the screen interface. Click End during the export process to end the current export progress and stop running. When the prompt information that the export is completed is jumped out, it proves that the current product has been exported.
[0041] Modify the part information of the exported BOM table in Excel. During the modification process, it is necessary to ensure that the modified part number and instance name are not repeated, otherwise the update will fail. After the modification is completed, run the program again, and input 2 starts to modify in CATIA level by level.
[0042] In the process of formulating, managing, and modifying the BOM table of a large product, the application can realize the docking of the Excel table and the CATIA structure tree with one key, greatly simplifying the processing work of the BOM table, avoiding human errors that are prone to occur in simple repetitive work processes, improving work efficiency, and providing other data such as the centroid and inertia matrix of the part. When other data such as the centroid and inertia matrix of the part is needed, it can be directly exported to Excel through the program for other calculations, and other properties of the CATIA part can also be directly generated in batches through Excel for subsequent model viewing, greatly improving the readability of the product.
[0043] Those skilled in the art will appreciate that the foregoing description of the embodiments of the application is by way of example only, and is not intended to limit the embodiments of the application in any way.
[0044] Embodiments of the application have been described above with the intent to be illustrative rather than limiting. Numerous modifications and enhancements are clearly possible in light of the foregoing disclosure without departing from the scope and spirit of the described embodiments.
Claims
1. A method for quickly obtaining product BOM tables and organizing CATIA structure trees based on VBA, characterized in that, include: Obtain the Excel interface and create a new Excel document, define the BOM table data structure members and create the corresponding title bars; Connect an Excel document to the product's CATIA structure tree, and control the CATIA structure tree through the Excel document using VBA.
2. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 1, wherein, The BOM (Bill of Materials) table data structure includes regular part information and custom part information. Regular part information includes part number, instance name, level, weight, material, and inertia matrix, while custom part information is defined according to product or user needs.
3. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 2, wherein, Controlling the CATIA structure tree through Excel documents includes exporting the BOM from CATIA to Excel and modifying the CATIA structure tree using Excel.
4. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 3, wherein, Exporting a BOM from CATIA to Excel includes: Obtain the root node of the CATIA structure tree, which is the information of the product itself; Iterate through all components, parts, and components under the root product and export them to Excel.
5. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 4, wherein, Iterate through all components, parts, and components under the root product and export them to Excel, including: When a part is encountered, its information is directly exported. When a component or assembly is encountered, its information is exported first, and then the part information in the child level is exported through a recursive function.
6. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 3, wherein, Exporting BOM from CATIA to Excel using VBA includes: First, define an information export function. This function will determine whether the current object is a part by whether it contains children. If it is a part, it will export the data structure member information. If it is not a part, it will recursively call this function to traverse all the parts or components under it and export the data structure member information. During the export of BOM information, the information of regular parts is defined by fixed API functions in CATIA secondary development. During the export process, recursive functions are used to call and read the information sequentially. The custom function iterates through all custom part information in the part. If no custom part information is found in the data structure member, the function will automatically create the attribute in CATIA and assign it an empty value.
7. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 6, wherein, Also includes: During the export of part information, a processing progress bar is created based on the number of components in the product, and a visual user interface is created with a stop button.
8. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 3, wherein, Modifying the CATIA structure tree using Excel includes: In Excel, modify the information of each part to the correct content, and call the recursive function to modify it level by level, starting from the root product; For custom part information, a custom function is used to iterate and match the parts. If there is no corresponding custom part information, the part attribute is automatically created in the part, and the corresponding information in Excel is written into the part.
9. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 8, wherein, Also includes: After making the modifications, save each part with the revised filename and delete the original parts.
10. The method for quickly obtaining the product BOM table and organizing the CATIA structure tree based on VBA according to claim 9, wherein, Save each part with a new filename after renaming it: Create a file management object and use a recursive function to save all components in the root product sequentially. During the saving process, create a dictionary array of part numbers and iterate through the dictionary array of part numbers. If the same part number exists, there is no need to save it again.