A system and method for automatically generating a BOM using VBA functions

Abstract

The present invention discloses a method for automatically generating a BOM using CAD VBA functions, which relates to the field of computer-aided design. Its technical key points include: S1: Prepare an AutoCAD assembly drawing template, which includes the drawing frame of the assembly drawing, the drawing title template, and the drawing frame and drawing title template of the BOM; S2: Install the VBA module in the AutoCAD software and establish a VBA project; S3: Write a VBA program in the VBA project. The VBA program is written in VB language and is used to process the assembly drawing file. The present invention solves the deficiencies of the existing manual operation methods: large amount of operation, easy to make mistakes, and large workload in the associated implementation during the product engineering change process. Moreover, the existing computer development software needs to use a specified database, resulting in high software procurement costs and enterprise operation costs; although the automated BOM can use a customized template, the depth of customization is insufficient and it is difficult to meet the standardization requirements.

Description

Technical Field

[0001] The present invention relates to the field of computer-aided design, and particularly to a system and method for automatically generating a BOM using VBA functions. Background Art

[0002] Currently, in addition to being used for drawing component drawings, drawing software is also commonly used for drawing assembly drawings to illustrate the assembly relationship and specification requirements of each component. After the design and development personnel complete the drawing of the assembly drawing, they often need to sort out the information of the components associated in the drawing (codes, models, quantities, etc.) to form an assembly list (referred to as BOM) as the basis for material matching implementation.

[0003] According to the traditional background art, the BOM sorting is mainly completed by manual operation of the drafters or automatically sorted by individual design and development software, both of which have obvious deficiencies, mainly as follows:

[0004] a) Deficiencies of the manual operation method: The operation volume is large and it is easy to make mistakes. In the case of highly coupled design and development, material supply, and production processing information, the workload associated with the product engineering change process is large;

[0005] b) Deficiencies of the automatic sorting method: Most design and development software needs to use a specified database, resulting in high software procurement costs and enterprise operation costs; Although the automated BOM can use a customized template, the depth of customization is insufficient and it is difficult to meet the standardization requirements. Summary of the Invention

[0006] The present invention provides a system and method for automatically generating a BOM using VBA functions. The technical problem to be solved is to provide an economical and flexible method on the basis of being compatible with a deeply customized file template, so as to automatically generate a BOM from an assembly drawing without manual intervention and meet the requirements of high efficiency and accuracy.

[0007] The present invention is achieved through the following technical solutions: A system for automatically generating a BOM using VBA functions includes: a graphic and text storage system for storing assembly drawings and the corresponding text information; a graphic and text processing system installed with a VBA module and establishing a VBA project. The VBA module is programmed using VB language. The VBA module is used to classify, sort out, and generate a BOM for the component information inside the assembly drawing. The VBA project provides a working environment for the VBA module. The VBA module establishes a vBOM and a computer-aided design table. The component information inside the assembly drawing is written into the vBOM as a global variable; a new vBOM row is created for the component information of different assembly drawings, and for the component information of the same assembly drawing, the quantity of the component is recorded and incremented by one, and all vBOM rows are combined into a vBOM array, and the information of the vBOM array is written into the computer-aided design table.

[0008] Among them, the stored assembly drawings and the text information corresponding to the assembly drawings include: the drawing frame of the assembly drawing, the drawing title template, and the drawing frame and drawing title template of the BOM. The drawing title template of the assembly drawing and the drawing title template of the BOM are marked to achieve information matching, and the drawing frame of the assembly drawing is different from the drawing frame template of the BOM to facilitate information processing and distinction.

[0009] In this solution, the information of parts and components (codes, models, quantities, etc.) associated within the drawing is sorted out to form an assembly detail; and similar products are automatically classified into the vBOM, reducing the workload.

[0010] Preferably, the graphic and text storage system is also used to store raw material specifications, raw material models, and raw material categories.

[0011] A method for automatically generating a BOM using the VBA function with the above system includes the following steps:

[0012] S1: Prepare an AutoCAD assembly drawing template, which includes the drawing frame of the assembly drawing, the drawing title template, and the drawing frame and drawing title template of the BOM;

[0013] Create blocks in the assembly drawing and the drawing title template and define the attributes of the blocks. The attributes include format information, file name information, and file number.

[0014] Create blocks in the drawing frame and the drawing title template of the BOM and define the attributes of the blocks. The attributes include format information and file number information;

[0015] The drawing frame template of the assembly drawing is different from the drawing frame template of the BOM. Characters for identifying the drawing frame of the assembly drawing, the drawing title template, and the drawing frame and drawing title template of the BOM are provided in the block;

[0016] S2: Install the VBA module in the AutoCAD software and establish a VBA project;

[0017] S3: Write a VBA program in the VBA project. The VBA program is written in VB language and is used for processing assembly drawing files. The specific logical description includes the following steps:

[0018] S31: Obtain the information of each graphic object in the assembly drawing and generate a vBOM;

[0019] S32: Establish a computer-aided design table as the display object for BOM information;

[0020] S33; Write the vBOM information into the computer-aided design table for classifying the same feature information.

[0021] In the above solution, the information of the parts and components associated within the drawing can be unified automatically better, and can be processed automatically by classification. In engineering projects, the construction volume is large and the number of construction drawings is numerous. This solution reduces the problem of excessive manual operation tasks.

[0022] Furthermore, in step S31, it further includes the steps:

[0023] S311: Use the current assembly drawing file to create a Document object;

[0024] S312: Define a global variable for the vBOM array, 1 - dimension: model number column, quantity column; 2 - dimension: data row;

[0025] S313: Define a global variable for the BOM insertion point coordinates;

[0026] S314: Define a global variable for the drawing number;

[0027] S315: Traverse all graphic entity objects within the Document model space;

[0028] S316: If the graphic object is of the block reference type and the Name attribute Like "STD - *" = False, then:

[0029] a) Traverse each row of the model number column in vBOM. If there is a duplicate item, then the quantity + 1;

[0030] b) If there is no duplicate item after traversal, then add a new row to vBOM, write the Name attribute, and the quantity = 1.

[0031] S317: If the graphic object is of the block reference type and the Name attribute Like "STD - *" = True, then:

[0032] a) Calculate the BOM starting coordinates according to the X - coordinate of the insertion point attribute, the width or height of the graphic, and the graphic spacing. When arranged side - by - side horizontally, use X - coordinate + width + spacing for calculation; when arranged side - by - side vertically, use Y - coordinate + height + spacing for calculation;

[0033] b) If there are multiple assembly drawings, calculate the outermost boundary coordinates according to the above method;

[0034] c) Read the drawing number information within the assembly drawing title and write it into the global variable.

[0035] In this solution, due to the high coupling of the actual production, processing, and engineering implementation drawings, the processing of minor differences is classified by adding new rows to vBOM, and further classifying the products with high coupling of processing information, which is convenient for subsequent processing.

[0036] Furthermore, in step S32, it further includes the steps:

[0037] S321: Create a Document object using the current assembly drawing file;

[0038] S322: Traverse all graphic entity objects within the Document's model space;

[0039] S323: If the graphic object is of the block reference type and its Name property Like “STD-BOM-*” = False, then delete it;

[0040] S324: If the graphic object is of the computer-aided design table type, then delete it;

[0041] S325: Insert a drawing frame, a title block at the starting point of the BOM, and write information such as the drawing number;

[0042] S326: Calculate the insertion point coordinates of the BOM table based on the drawing frame margins;

[0043] S327: Insert a table;

[0044] S328: Traverse each column of the table, specifically for the column width, column text alignment, and setting the text style.

[0045] In this solution, the computer-aided design table imports the information of the assembly drawing file, deletes duplicate drawing files, and then aligns the text and arranges it in the table according to the specifications.

[0046] Preferably, in step S33, it further includes the steps:

[0047] S331: Traverse according to the vBOM and retrieve the material type in the database;

[0048] S332: Group and sort each row of the vBOM by type;

[0049] S333: Number each row of the vBOM by type and type code;

[0050] S334: Traverse each row of the vBOM and write the information and codes of each column into the corresponding columns of the computer-aided design table.

[0051] Among them, according to the height of the drawing frame, set the table break height to prevent the table from exceeding the drawing frame range: when reaching the break height, add a new drawing frame side by side and continue the table content.

[0052] In this solution, multiple columns are set to classify different material information and number it, which is convenient for the staff to identify.

[0053] Optionally, in step S1, a database with the raw material specification model as the primary key is established in advance, including the raw material model specification, raw material category, and CAD template.

[0054] Optionally, the computer-aided design table is used as a BOM information display object, which supports functions of manually adding, deleting, and correcting information.

[0055] Compared with the prior art, the present invention has the following advantages and beneficial effects:

[0056] 1. A method for automatically generating a BOM using the VBA function of the present invention sorts out the information (codes, models, quantities, etc.) of the parts and components associated in the drawing to form an assembly details list; and automatically classifies similar products into the vBOM, reducing the workload. Moreover, users of the present invention can use the self-built database as an extended BOM information source such as the CAD model of components. After the engineering project changes, it is more convenient to actually generate automatically.

[0057] 2. A method for automatically generating a BOM using the VBA function of the present invention uses ACadTable as a BOM information display object and supports manual addition, deletion, and modification. Compared with traditional program development software, for some files that require additional special processing, there is no need to write additional programs to implement, reducing the software development and operation costs of enterprises and improving the customization depth of the BOM. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] The drawings described herein are used to provide a further understanding of the embodiments of the present invention, form a part of this application, and do not limit the embodiments of the present invention. In the drawings:

[0059] Figure 1 is a schematic diagram of the overall process of the present invention;

[0060] Figure 2 is a flowchart of the VBA method of the present invention;

[0061] Figure 3 is a flowchart of the working process of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0062] To make the objectives, technical solutions, and advantages of the present invention clearer and more understandable, the present invention will be further described in detail below in conjunction with the embodiments and the drawings. The illustrative embodiments and descriptions thereof of the present invention are only used to explain the present invention and do not limit the present invention.

[0063] In the following description, a large number of specific details are set forth in order to provide a thorough understanding of the present invention. However, it is obvious to those of ordinary skill in the art that: the present invention does not have to adopt these specific details. In other instances, well-known structures, circuits, materials, or methods have not been specifically described to avoid obscuring the present invention.

[0064] Throughout the specification, references to "one embodiment", "an embodiment", "one example" or "an example" mean that a particular feature, structure, or characteristic described in connection with the embodiment or example is included in at least one embodiment of the present invention. Thus, the phrases "one embodiment", "an embodiment", "one example" or "an example" appearing throughout the specification do not necessarily all refer to the same embodiment or example. Additionally, the particular features, structures, or characteristics may be combined in any suitable combination and / or sub-combination in one or more embodiments or examples. Further, those of ordinary skill in the art should understand that the diagrams provided herein are for illustrative purposes only and are not necessarily drawn to scale. The term "and / or" used herein includes any and all combinations of one or more of the associated listed items.

[0065] In the description of the present invention, it should be understood that the orientation or positional relationships indicated by the terms "front", "rear", "left", "right", "upper", "lower", "vertical", "horizontal", "high", "low", "inner", "outer", etc. are based on the orientation or positional relationships shown in the drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and thus should not be construed as limiting the protection scope of the present invention.

[0066] Embodiment

[0067] Taking AutoCAD as the application software as an example;

[0068] Such as Figure 1 And Figure 3 As shown, a system for automatically generating a BOM using the VBA function includes: a graphic and text storage system for storing the assembly drawing and the corresponding text information of the assembly drawing; a graphic and text processing system installed with a VBA module and establishing a VBA project. The VBA module is programmed in VB language, and the VBA module is used to classify, organize, and generate a BOM for the component information inside the assembly drawing. The VBA project provides a working environment for the VBA module, and a method for automatically generating a BOM using the CAD VBA function.

[0069] Before using this method, it should be ensured that the necessary content is included in the AutoCAD assembly drawing template, and the specific form can be deeply customized according to the standardization requirements:

[0070] First, for the drawing frame and title template of the assembly drawing: block + attribute definition, the name starts with a character such as "STD-" for identification, and the attributes include but are not limited to:

[0071] 1) Sheet information, such as: A4 landscape (A4H), A3 portrait (A3S), etc.;

[0072] 2) File name information, such as: ××××× assembly drawing, etc.;

[0073] 3) File number information, such as: ABC.123.4567, etc.

[0074] For the same BOM title block and title template: block + attribute definition, the name starts with a character like "STD-" for identification, and it is different from the assembly drawing title block template. The attributes include but are not limited to:

[0075] 1) Sheet size information, such as: A4 landscape (A4H), A3 portrait (A3S), etc.;

[0076] 2) File number information, left blank for future use.

[0077] As an optional part of the above embodiment, a database with raw material specifications and models as the primary key can be established in advance, including but not limited to the following content:

[0078] 1) Model specifications: Raw material model specifications;

[0079] 2) Categories: Raw material categories, such as: RF connectors, capacitors, etc.;

[0080] 3) CAD template: AutoCAD-compatible graphics.

[0081] In this solution, information such as CAD models and component specifications can be extended through the database according to the user's own needs to optimize the user experience and increase the customization depth of the BOM.

[0082] Then establish a VBA project: The VBA module creates vBOM and ACadTable. The internal component information in the assembly drawing is written into vBOM as a global variable. For different internal component information in the assembly drawing, a new vBOM row is created. For the same internal component information in the assembly drawing, the recorded internal component quantity is incremented by one, and a vBOM array is formed. The vBOM array information is written into ACadTable.

[0083] Where vBOM is a Variant-type multi-dimensional array variable.

[0084] 1) Install the VBA module in the AutoCAD software;

[0085] 2) Establish a VBA project and write a VBA program.

[0086] As an optimization of the above embodiment, as Figure 2 shown, write a program using the VB language, and the logical description is as follows:

[0087] Obtain BOM information and the starting coordinates of the BOM template

[0088] Create a Document object using the current assembly drawing file;

[0089] Define a global variable for the vBOM array, 1D: model number column, quantity column; 2D: data rows;

[0090] Define a global variable for the BOM insertion point coordinates;

[0091] Define a global variable for the drawing number;

[0092] Traverse all ACadEntity objects in the Document's model space;

[0093] If the graphic object is of type AcadBlockReference and the Name property Like "STD-*" = False, then:

[0094] Traverse each row in the model number column of vBOM, if there is a duplicate, increment the quantity by 1;

[0095] After traversing and having no duplicates, add a new row to vBOM, write the Name property, and set the quantity = 1.

[0096] If the graphic object is of type AcadBlockReference and the Name property Like "STD-*" = True, then:

[0097] Calculate the BOM starting coordinates based on the X coordinate of the insertion point property + the width or height of the graphic (block size property) + the graphic spacing;

[0098] If there are multiple assembly drawings, calculate the outermost boundary coordinates according to the above method;

[0099] Read the drawing number information in the assembly drawing title and write it into the global variable.

[0100] Create an AcadTable. The AcadTable used in the method of this patent can also be implemented by using temporary line drawing + (multiple-line) text box method.

[0101] As a preference for the above embodiment, if Figure 2 And Figure 3 As shown, the establishment of the AcadTable is as follows,

[0102] First, create a Document object using the current assembly drawing file;

[0103] Traverse all ACadEntity objects in the Document's model space;

[0104] If the graphic object is of type AcadBlockReference and the Name property Like "STD-BOM-*" = False, then delete it;

[0105] Delete if the graphic object is of the ACadTable type;

[0106] In the method of this patent, the traversal method is used to obtain information, and the BOM information can also be obtained based on events such as ObjectAdded, ObjectErased, and ObjectModified.

[0107] Insert a drawing frame, drawing title at the starting point of the BOM and write information such as drawing number;

[0108] Calculate the insertion point coordinates of the BOM table according to the drawing frame margin. In this patent, the insertion point based on the assembly drawing information technology BOM is used, and the insertion point with preset coordinates can also be used.

[0109] Insert a table;

[0110] Traverse each column of the table: specifically, set the column width, set the column text alignment, and set the column text style.

[0111] In this solution, through the traversal method, similar files are screened and automatically sorted out, and the inconsistent ones are deleted. After automatic screening, the manual workload is reduced.

[0112] Write the vBOM information into the ACadTable;

[0113] Optionally, traverse according to the vBOM and retrieve the material type in the database; Optionally, group and sort each row of the vBOM by type; Optionally, number each row of the vBOM by type and type code;

[0114] In this patent, by combining the information such as the drawing title and drawing frame in the assembly drawing with the drawing title and drawing frame in the assembly details and matching the parts material library, through the above processing, the staff can retrieve the material category attributes according to the specifications to screen the required materials respectively, and the materials can be further processed to generate the corresponding detailed list.

[0115] It should be noted that for the ACadTable in this patent, the temporary line drawing + (multiple lines) text box method can also be used to implement it.

[0116] Traverse by the vBOM row and write the information (model specifications, quantity) and codes of each column into each column of the ACadTable. It should be noted that the AutoCAD VBA method involved in the method of this patent can also be extended to other CAD tools such as SolidWorks that contain VBA functions and require generating a raw material list according to the model and drawing results.

[0117] The specific embodiments described above further elaborate on the object, technical solution, and beneficial effects of the present invention. It should be understood that the above description is only for the specific embodiments of the present invention and is not used to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. A method for automatically generating a BOM using VBA functions, characterized in that, Including: A graphic and text storage system for storing assembly drawings and the corresponding material information of the assembly drawings; The information corresponding to the graphic and text storage system includes: the drawing frame template, the drawing title template of the assembly drawing, and the drawing frame template and the drawing title template of the BOM. Identification marks are provided on the drawing title template of the assembly drawing and the BOM, and the drawing frame template of the assembly drawing is different from the drawing frame template of the BOM; The graphic and text storage system is also used to store raw material specifications, raw material models, raw material categories, and drawing numbers; A graphic and text processing system, which has a VBA project and a VBA module established. The VBA project provides a working environment for the VBA module, and the VBA module is used to classify, organize the internal component information of the assembly drawing and generate a BOM; The VBA module has a vBOM and a computer-aided design table established; Among them, new vBOM rows are created for the material information of different assembly drawings, and all vBOM rows are combined into a vBOM array. The vBOM is used to classify the assembly drawing and the corresponding material information of the assembly drawing and divide them into different vBOM arrays, and the information of the vBOM array is written into the computer-aided design table; S1: Prepare the assembly drawing template, including the drawing frame template, the drawing title template of the assembly drawing, and the drawing frame template and the drawing title template of the BOM; Create blocks in the drawing frame template and the drawing title template of the assembly drawing and the drawing frame template and the drawing title template of the BOM and define the attributes of the blocks. The attributes include format information, file name information, and file number information. Characters for identifying the drawing frame template, the drawing title template of the assembly drawing, and the drawing frame template and the drawing title template of the BOM are provided in the blocks, and the drawing frame template of the assembly drawing is different from the drawing frame template of the BOM; S2: Install the VBA module in the graphic and text processing system and establish a VBA project; S3: Write a VBA program in the VBA project. The VBA program is written in VB language and is used to process the assembly drawing files. The specific logical description includes the following steps: S31: Obtain the information of each graphic object in the assembly drawing and generate a vBOM; S311: Use the assembly drawing and the corresponding material information of the assembly drawing to create a Document object; S312: Take the vBOM array as a global variable and define the global variable of the vBOM array, including: model column, quantity column, and data row; S313: Take the BOM insertion point coordinates as a global variable; S314: Take the drawing number as a global variable; S315: Traverse all graphic entity objects in the model space of the Document; S316: Take an assembly drawing creation object as the block reference type and screen the Document objects similar to this assembly drawing. If the graphic object is of the block reference type and the Name attribute Like "STD-*” = False, then: a) Traverse each row of the model column in the vBOM. If there are duplicate items, the quantity in the model column is accumulated; b) After the traversal ends without duplicate items, a new row is added to the vBOM, and the Name attribute is written, and the quantity = 1; S317: If the graphic object is of the block reference type and the Name attribute Like "STD-*” = True, then: a) Calculate the starting coordinates of the BOM based on the X coordinate of the insertion point attribute, the width or height of the graphic, and the graphic spacing. When arranging side by side horizontally, use the X coordinate + width + spacing to calculate the starting coordinates of the BOM; when arranging side by side vertically, use the Y coordinate + height + spacing to calculate the starting coordinates of the BOM. b) If there are multiple assembly drawings, calculate the outermost boundary coordinates according to the calculation method of the BOM starting coordinates. c) Read the drawing number information in the assembly drawing title and write it into the global variable. S32: Establish a computer-aided design table as the BOM information display object. S33: Write the vBOM information into the computer-aided design table for classifying the same feature information.

2. The method for automatically generating a BOM using VBA functions according to claim 1, characterized in that, In step S32, it also includes the steps: S321: Use the current assembly drawing file to establish a Document object. S322: Traverse all graphic entity objects in the Document model space. S323: If the graphic object is of the block reference type and the name attributes are the same, delete it. S324: If the graphic object is of the computer-aided design table type, delete it. S325: Insert a drawing frame and a drawing title at the BOM starting point and write the drawing number information. S326: Calculate the insertion point coordinates of the BOM table according to the drawing frame margin. S327: Insert a table. S328: Traverse each column of the table.

3. A method for automatically generating a BOM using VBA functions according to claim 1, characterized in that, In step S33, it also includes the steps: S331: Traverse according to the vBOM and retrieve the material type in the database. S332: Group and sort each row of the vBOM by type. S333: Number each row of the vBOM by type and type code. S334: Traverse according to the vBOM row, and write the information and code of each column into each column of the computer-aided design table.

4. A method for automatically generating a BOM using VBA functions according to claim 1, characterized in that, In step S1, a database with the raw material specification model as the primary key is established in advance, including the raw material model specification, raw material category, and template.

5. A method for automatically generating a BOM using VBA functions according to claim 1, characterized in that, The computer-aided design table serves as the BOM information display object, which supports the functions of manually adding, deleting, and correcting information.

Images