Automatic standard part generation method based on VBA language
Through the automatic generation method of standard parts based on VBA language, the cumbersome and errors of standard parts summary and procurement list preparation in marine equipment manufacturing are solved, and the rapid and accurate standard parts summary and procurement list automatic formation is achieved, improving work efficiency and data accuracy.
Patent Information
- Application Number
- CN202411771331.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-04
- Publication Date
- 2025-04-11
AI Technical Summary
In the process of marine equipment manufacturing, there are a wide variety and quantity of standard parts, and the existing technology is difficult to quickly and accurately summarize and convert it into procurement lists, resulting in huge workloads and easy errors, affecting production.
The automatic generation method of standard parts based on VBA language is adopted. By sorting out two-dimensional assembly drawings, standard parts are generated and published, and the VBA program is used to classify and summarize the national standards, specifications, materials, plating and other parameters of standard parts, and automatically form a summary list of standard parts and procurement lists, and verify them to ensure data accuracy.
It realizes the rapid batch summary of standard parts and the automatic formation of procurement lists, reduces the workload of manual comparison and sorting, improves the accuracy and reliability of data, and saves time and energy.
Smart Images
Figure CN119807139A_ABST
Abstract
Description
Technical Field
[0001] The invention belongs to the technical field of standard parts management, and in particular relates to a method for automatically generating standard parts based on VBA language, which is used for summarizing the types and quantities of standard parts in the process of manufacturing marine equipment. Background Art
[0002] Usually, production tasks of a scientific research nature have a short cycle and heavy design work. During the three-dimensional design process of the product, designers usually do not assemble standard parts into the three-dimensional assembly drawing. Standard parts are usually reflected in the two-dimensional assembly drawing.
[0003] In this production context, it is usually impossible to quickly obtain an accurate list of standard parts for a complete set of products, and designers need to manually clean it up later. In the process of cleaning up standard parts, the main work to be done is: first, clean up the standard parts in each assembly drawing according to the detail column of the two-dimensional assembly drawing to form a standard parts matching table; then, the standard parts need to be classified and summarized according to the national standard number, specification, material, coating and other information to form a summary list of standard parts; finally, upload the summary list to the OA system for purchase declaration. At this time, the data format of the purchase list is quite different from that of the summary list, and it is impossible to simply correspond one to one, so a separate purchase list needs to be formed.
[0004] The problems commonly encountered in this process are: there are hundreds or even thousands of types and quantities of standard parts, and the workload of classification and summary is huge, time-consuming, labor-intensive, and error-prone. In addition, the process of converting the summary list into a purchase list takes even more time and energy, and is also error-prone. When the device procurement cycle is long, procurement errors will usually have a greater impact on subsequent production.
[0005] In previous statistical summary work, the tool commonly used is EXCEL. EXCEL itself has the function of pivot table which can meet the needs of classification summary to a certain extent, but the data format it forms is different from the data format required for the summary list and purchase list required for final production, so the data needs to be further sorted out, which has limited effect on simplifying the workload and cannot fully meet the usage requirements. Summary of the invention
[0006] In view of the requirements of the existing design process, such as the preparation of a summary list of standard parts and a purchase list at the early stage of the production phase, and the huge amount of summary work that the OA system has to deal with during the purchase declaration phase, the present invention provides a method for automatically generating standard parts based on the VBA language.
[0007] The technical solution adopted by the present invention to solve the technical problem is: a method for automatically generating standard parts based on VBA language, comprising the following steps: (1) By arranging the drawings, clean up the standard parts in each two-dimensional assembly drawing according to the detail column of the two-dimensional assembly drawing, and obtain the standard parts matching table containing parameter information such as the standard part's drawing number, standard part national standard number, standard part name, standard part specification, standard part material, standard part coating, and standard part quantity; (2) Start summarizing. Open the standard parts table obtained in the previous step and start running the VBA program. The program prompts whether to summarize the current workbook. After confirmation, the program starts running. The VBA program compares each standard part national standard number, standard part specification, standard part material, standard part coating and other parameters in the standard parts table and summarizes the quantity by category to obtain a standard parts summary list. (3) When it is necessary to purchase standard parts, the standard parts summary list is matched with the material library through the VBA program to obtain the draft purchase list in the procurement format; (4) Verify the draft purchase list through the VBA program, manually process the questionable items, mark the questionable items in the draft purchase list, and obtain an accurate final purchase list.
[0008] Furthermore, the assembly drawing arrangement work in step (1) can be completed by an image or text recognition plug-in, which depends on whether the company's actual production has this capability. If so, the whole process does not require too much repetitive work by designers.
[0009] Furthermore, the cleaning process in step (1) is as follows: the CAD drawings are stored in separate folders according to the drawing numbers, and the VBA program reads all CAD files in the folders, and obtains the standard part information in the CAD drawings by reading the CAD drawings.
[0010] Furthermore, in the step (2), when the standard parts allocation table is used to summarize the standard parts, two auxiliary data columns, namely the number of standard parts sets and the total number of standard parts, are automatically added after the standard parts allocation table. The value obtained by multiplying the number of each standard part in each assembly drawing by the number of standard parts sets is the final total number of standard parts in the assembly drawing.
[0011] Furthermore, the inspection process in step (4) is as follows: using a VBA program to compare the national standard number, standard specification, and standard coating of the standard parts in the draft procurement list with the parameter information of the standard parts in the standard parts summary list one by one to see if they match; if any parameter is different during the comparison process, the program automatically displays the detail in yellow.
[0012] Furthermore, the standard parts summary list can change simultaneously with the data changes in the standard parts distribution table, and there is no need to re-use the VBA program for summary. The summary list will be changed in real time according to the changes in the standard parts distribution table.
[0013] The beneficial effects of the present invention are as follows: the present invention can realize rapid batch summary of the types and quantities of standard parts according to the national standard number of standard parts, standard part specifications, standard part materials, standard part coatings and other information, obtain a summary list of standard parts, and quickly and automatically form a final purchase list, while completing the correctness verification. The present invention saves the repetitive comparison and summary work in the summary process. The designer only needs to pay attention to and ensure that the information in the original distribution table is accurate. If there are subsequent design changes, only the distribution table needs to be maintained, and the program can automatically complete the compilation of the summary list and the purchase list; at the same time, by introducing a verification process, human errors are eliminated to ensure that the data summarized by the program is accurate and reliable. BRIEF DESCRIPTION OF THE DRAWINGS
[0014] Figure 1 It is a schematic diagram of the standard parts distribution table of the present invention; Figure 2 It is a schematic diagram of the summary list of standard parts of the present invention; Figure 3 It is a schematic diagram of a purchase list of the present invention; Figure 4 It is the VBA program part code of the present invention; Figure 5 It is a flow chart of the method of the present invention. DETAILED DESCRIPTION
[0015] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments.
[0016] The invention discloses a method for automatically generating standard parts based on VBA language, which comprises the following steps.
[0017] (1) By arranging the assembly drawings, clean up the standard parts in each two-dimensional assembly drawing according to the detail column of the two-dimensional assembly drawing to form a standard parts allocation table. The standard parts allocation table contains parameter information such as the standard part's drawing number, the standard part's national standard number, the standard part's name, the standard part's specification, the standard part's material, the standard part's coating, the standard part's quantity, etc., as shown in the attached Figure 1 shown.
[0018] Conventional methods manually organize assembly drawings, while the assembly drawing organization work in this patent application can be completed by image or text recognition plug-ins, which depends on whether the company's actual production has this capability. If so, the whole process does not require too much repetitive work by designers.
[0019] The cleaning process in this step is: store the CAD drawings in separate folders according to the drawing numbers, use the VBA program to read all the CAD files in the folder, and obtain the standard parts information in the CAD drawings by reading the CAD drawings. The read information includes: the drawing number, the details of the standard parts under the drawing, the details include the serial number of each item of standard parts in the details column, the national standard number, name, specification, material, coating and quantity of the standard parts, etc.
[0020] (2) Start summarizing. Open the standard parts table obtained in the previous step and start running the VBA program. The program prompts whether to summarize the current workbook. After confirmation, the program starts running. The VBA program compares the national standard number, standard part specification, standard part material, standard part coating and other parameters of each standard part in the standard parts table and summarizes the quantity by category to obtain a summary list of standard parts, as shown in the attached figure. Figure 2 shown.
[0021] When using the standard parts distribution table to summarize standard parts, two columns, the number of standard parts sets and the total number of standard parts, will be automatically added as auxiliary data after the standard parts distribution table. The data program for the number of standard parts sets is filled in as "1" by default. The value of the number of each standard part in each assembly drawing multiplied by the number of standard parts sets is the final total number of standard parts in the assembly drawing. The standard parts distribution table and the standard parts summary list can be used as the basis for workshop production.
[0022] (3) When it is necessary to purchase standard parts, the standard parts summary list is matched with the material library through the VBA program to obtain a draft purchase list in the procurement format.
[0023] (4) Use VBA program to verify the draft purchase list, mark the items in question in the draft purchase list, remind personnel to modify and verify, and obtain an accurate final purchase list, as shown in the attached Figure 3 shown.
[0024] The inspection process in this step is: use VBA program to compare the national standard number, standard specification, standard coating of standard parts in the draft of the purchase list with the standard parts summary list. If there is a difference in any parameter during the comparison process, the program will automatically mark the detail in yellow. Part of the code is attached. Figure 4 The method flow chart is shown in the attached Figure 5 shown.
[0025] The above embodiments are only illustrative of the principles and effects of the present invention, as well as some embodiments of its application. For those skilled in the art, several modifications and improvements may be made without departing from the creative concept of the present invention, and all of these belong to the protection scope of the present invention.
Claims
1. A method for automatically generating standard parts based on VBA language, characterized in that: The following steps are involved: (1) Arrange the drawings and clean up the standard parts in each 2D assembly drawing according to the detail column of the 2D assembly drawing to obtain a standard parts matching table containing the standard parts' drawing number, standard parts' national standard number, standard parts' name, standard parts' specification, standard parts' material, standard parts' coating, and standard parts' quantity; (2) Use the VBA program to automatically compare the national standard number, standard part specification, standard part material, and standard part coating of each standard part in the standard parts matching table and classify and summarize the quantity to obtain a standard parts summary list; (3) Use VBA program to match the standard parts summary list with the material library to obtain the draft purchase list; (4) Use VBA program to verify the draft purchase list, mark the items in question, and manually process the questionable items to obtain an accurate final purchase list.
2. The method for automatically generating standard parts based on VBA language according to claim 1, characterized in that: The arrangement of drawings in step (1) is completed by an image or text recognition plug-in.
3. The method for automatically generating standard parts based on VBA language according to claim 2, characterized in that: The cleaning process in step (1) is as follows: the CAD drawings are stored in separate folders according to the drawing numbers, and the VBA program obtains the standard part information in the CAD drawings by reading the CAD drawings.
4. A method for automatically generating standard parts based on VBA language according to claim 1, 2 or 3, characterized in that: When summarizing in step (2), two auxiliary data columns, the number of standard parts sets and the total number of standard parts, are added after the standard parts assembly table. The number of each standard part in each assembly drawing × the number of standard parts sets = the total number of standard parts in the assembly drawing.
5. The method for automatically generating standard parts based on VBA language according to claim 4, characterized in that: The inspection process in step (4) is as follows: using the VBA program to compare the national standard number, standard specification, and standard coating of the standard parts in the draft of the purchase list with the parameter information in the standard parts summary list one by one to see if they match; if any parameter is different during the comparison process, the program automatically displays the detail in yellow.
6. The method for automatically generating standard parts based on VBA language according to claim 5, characterized in that: The summary list of standard parts changes as the data in the standard parts distribution table changes.