CN109582942B - Borrowing piece automatic generation system based on VBA language - Google Patents
Borrowing piece automatic generation system based on VBA language Download PDFInfo
- Publication number
- CN109582942B CN109582942B CN201811450005.9A CN201811450005A CN109582942B CN 109582942 B CN109582942 B CN 109582942B CN 201811450005 A CN201811450005 A CN 201811450005A CN 109582942 B CN109582942 B CN 109582942B
- Authority
- CN
- China
- Prior art keywords
- borrowed
- file
- automatically
- characters
- opened
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/186—Templates
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Health & Medical Sciences (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Computational Linguistics (AREA)
- General Health & Medical Sciences (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Stored Programmes (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses an automatic borrowed part generation system based on VBA language, which is characterized in that a plurality of detail lists which are derived from a design drawing and contain all details are renamed and then are placed in a folder of the system, then all the lists are sequentially opened through a VBA program and borrowed parts are automatically judged, and finally the files are closed after formats are automatically arranged until all the detail lists in the folder are arranged into borrowed part detail lists which only contain the details of the borrowed parts and have standard formats.
Description
Technical Field
The invention belongs to the technical field of borrowed article management, and particularly relates to a borrowed article automatic generation system based on VBA language.
Background
When the process is used for compiling a process file, the parts borrowed by the part are usually independently arranged, so that the arrangement and production of a production department are facilitated. The conventional arrangement of borrowed parts is generally to identify which borrowed parts are manually identified, then the borrowed parts are picked out and placed on a table, the borrowed parts are printed out in an arranged format and sent together with a process file, the borrowed parts of one part can only be arranged every time, the format needs to be rearranged every time, errors are easy to occur, the working efficiency is low, parts are easily missed to be produced, and the delivery cycle is influenced. The formats of the existing borrowing item lists are all mastered by individuals, so that after the items are finally gathered together, the formats are very different and are not uniform, and a lot of troubles are increased for the arrangement and production of subsequent copying plans.
Disclosure of Invention
In order to overcome the defects of non-uniform format, low efficiency and easy error in the conventional borrowed part sorting technology, the invention provides the automatic borrowed part generation system based on the VBA language, which can improve the working efficiency and the standardization degree of process personnel and shorten the processing period of a product.
The technical scheme adopted by the invention for realizing the purpose is as follows:
a system for automatically generating borrowers based on VBA language comprises the following steps: firstly, renaming all detail lists which are derived from a design drawing and contain all details, moving the lists to a folder where the system is located after the naming is finished, then creating an operation interface in an EXCEL worksheet, sequentially opening all the lists and automatically judging borrowed parts through a VBA program by the operation interface, and finally automatically arranging formats according to the standard format of the borrowed part detail lists and then closing the tables until all the detail lists in the folder are arranged into the borrowed part detail lists which only contain the standard format of the borrowed part details.
Further, the step of the operation interface opening each table in sequence through the VBA program and automatically judging the borrowed article includes a main program for making contact with the operation interface and displaying the running state of the program and calling the subprogram in a label of the system operation interface, a subprogram for automatically judging the borrowed article and saving and closing the worksheet by opening the worksheet in sequence, and a subprogram automatically arranged into a standard format. In the invention, the VBA program and the efficiency of program operation are controlled in the operation interface, so the program is divided into three parts, namely a main program and two subprograms.
Further, the main program includes the steps of: firstly clicking a button for automatically generating borrowed part details on an operation interface, displaying whether a program is successfully started and is in operation in an information cell of the operation interface, if the program is in operation, displaying' spelling loading.
Further, the sub-program steps of opening the worksheet in sequence to automatically judge borrowed articles and saving and closing the worksheet are as follows: determining a file path to be opened and sorted, determining a specific file to be opened according to a file name, judging whether the file is edited and sorted according to file conditions which do not need sorting after the file is opened, directly closing the file if the file does not need sorting, recycling the file to the specific file to be opened, judging a file name, opening another file, judging whether the file needs sorting until the file which needs editing and sorting is opened, screening whether the file which needs editing and sorting is a borrowed piece according to borrowed piece judgment conditions, automatically judging a next line until the next line is finished if the line is judged not to be the borrowed piece according to borrowed piece judgment conditions, automatically selecting the content of the line and copying after the line is judged to be the borrowed piece, simultaneously opening a template file 'borrowed piece list', pasting copied content at a corresponding position, continuously judging, copying and pasting the next line, repeating the actions until the judgment is finished, jumping out a subprogram, and saving and closing a work list after the screening is finished.
Furthermore, the opened file and the operation interface file with the same name do not need to be opened, otherwise, the opened file is opened.
Furthermore, after the file needing to be edited and arranged is opened, the 'material' column which is useless for the borrowed part needs to be deleted, and then the file name is extracted.
Further, the file condition not requiring finishing is: if the files which do not need to be sorted have the characters of the sequence numbers in the cells at the intersections of the 1 st column and the 2 nd row of the opened files, sorting is not needed.
Further, the borrowing piece judgment condition needs to satisfy the following conditions at the same time: a. after the file name needing to be edited and sorted is extracted, seven characters are intercepted from the right side of the name, the characters left after interception are used for comparing with the characters 'the numbers of all parts or standard parts' in the cells of the picture number column of the opened file, if the characters of the cells are different from the characters left after interception, the parts are borrowed, and if the characters are the same, the parts are directly-belonging parts of the parts, namely the parts are not borrowed; b. if the first two characters in the drawing number row unit cells are 'GB', the drawing number row unit cells are standard elements, namely, the drawing number row unit cells are not borrowed elements, and otherwise, the drawing number row unit cells are judged according to the condition a; c. if the first two characters in the cells of the figure number column are 'code numbers', the item column is not a borrowed item, otherwise, the item column is judged according to the condition a; d. and if the first two characters in the drawing number row unit cells are empty characters, the drawing number row unit cells are not used by the article, otherwise, the drawing number row unit cells are judged according to the condition a.
Further, the subprogram which is automatically arranged into the standard format writes the attributes of font size, line spacing and the like into the program according to the borrower standard format in the borrower automatic generation system based on the VBA language. The subprogram is mainly used for realizing standardization and facilitating the connection of work of all departments.
Preferably, the operation interface is composed of an 'automatic borrowing item detail generation' button and an information cell which are created in an EXCEL worksheet. In order to make the operation convenient and the operation process can be expressed visually, an operation interface automatically generated by a borrowed part is firstly established in an EXCEL worksheet, the operation interface is simple, only one 'automatically generated borrowed part detail' button and an information cell form are formed, the button is used for controlling whether a VBA program starts, and the content displayed by the information cell form is the program running state.
The invention firstly renames a plurality of detail lists which are derived from a design drawing and contain all details, then puts the detail lists into a folder of the system, then sequentially opens each form through a VBA program and automatically judges borrowed parts, and finally closes after automatically arranging formats until all the detail lists in the folder are arranged into borrowed part detail lists which only contain the details of the borrowed parts and have standard formats, thereby greatly improving the working efficiency of technologists, shortening the time of product design, improving the standardization degree, reducing the processing period of products, creating greater intangible value for companies and improving the competitiveness of the companies.
Drawings
FIG. 1 is a schematic view of an operator interface;
FIG. 2 is a detailed illustration of the borrowed article.
Detailed Description
The invention is further described below with reference to the accompanying drawings.
As shown in fig. 1 to 2, the system for automatically generating a borrowed item based on VBA language of the present embodiment includes the following steps: firstly, renaming all detail lists which are derived from a design drawing and contain all details, moving the lists to a folder where the system is located after the naming is finished, then creating an operation interface in an EXCEL worksheet, sequentially opening all the lists and automatically judging borrowed parts through a VBA program by the operation interface, and finally automatically arranging formats according to the standard format of the borrowed part detail lists and then closing the tables until all the detail lists in the folder are arranged into the borrowed part detail lists which only contain the standard format of the borrowed part details.
Further, the step of the operation interface opening each table in sequence through the VBA program and automatically judging the borrowed article includes a main program for making contact with the operation interface and displaying the running state of the program and calling the subprogram in a label of the system operation interface, a subprogram for automatically judging the borrowed article and saving and closing the worksheet by opening the worksheet in sequence, and a subprogram automatically arranged into a standard format. In the invention, the VBA program and the efficiency of program operation are controlled in the operation interface, so the program is divided into three parts, namely a main program and two subprograms.
Further, the main program includes the steps of: firstly clicking a button for automatically generating borrowed part details on an operation interface, displaying whether a program is successfully started and is in operation in an information cell of the operation interface, if the program is in operation, displaying' spelling loading.
Further, the sub-program steps of opening the worksheet in sequence to automatically judge borrowed articles and saving and closing the worksheet are as follows: the method comprises the steps of firstly determining a file path needing to be opened and arranged, determining a specific file needing to be opened according to a file name, judging whether the file needs to be edited and arranged or not according to file conditions which do not need to be arranged after the file is opened, directly closing the file if the file does not need to be arranged, determining a file name, opening another file, judging whether the file needs to be arranged or not until the file needing to be edited and arranged is opened, screening whether a borrowed part is in the file needing to be edited and arranged or not according to the borrowed part judgment condition, automatically judging the next line until the line is finished if the line is judged not to be the borrowed part according to the borrowed part judgment condition, automatically selecting the content of the line and then copying when the line is judged to be the borrowed part, simultaneously opening a template file 'borrowed part detail table', pasting the copied content at a corresponding position, continuously judging, copying and pasting the next line, repeating the actions until the judgment is finished, jumping out of subprograms, and saving and closing a work table after the screening is finished.
Furthermore, the opened file and the operation interface file with the same name do not need to be opened, otherwise, the opened file is opened.
Furthermore, after the file needing to be edited and sorted is opened, the 'material' column which is useless for the borrower is deleted, and then the file name is extracted.
As shown in fig. 2, the file condition that does not need to be sorted is: the files which do not need to be sorted are the files with the characters of the sequence numbers in the cells at the intersection of the 1 st column and the 2 nd line of the opened files, so that the files do not need to be sorted. The specific cell of the file which does not need to be sorted has a specific character, the specific character 'serial number' is determined in the A4 cell, and if the condition is met, the file does not need to be sorted and is directly closed.
Further, the borrowing piece judgment condition needs to satisfy the following conditions at the same time: a. after the file names which need to be edited and arranged are extracted, seven characters are intercepted from the right side of the names, the characters which are remained after the interception are compared with the characters 'the numbers of all parts or standard parts' in the cells of the B column of the opened file, if the characters of the cells are different from the characters which are remained after the interception, the borrowed parts are used, and if the characters are the same, the directly-belonging parts of the parts are not used; b. if the first two characters in the cell in the column B are 'GB', the cell is a standard cell, namely the cell is not a borrowed cell, otherwise, the cell is judged according to the condition a; c. if the first two characters in the cell in the column B are 'code numbers', the item column is not a borrowed item, otherwise, the item column is judged according to the condition a; d. and if the first two characters in the B-column unit cell are null characters, the cell is not a borrowed cell, otherwise, the cell is judged according to the condition a.
Further, the subprogram which is automatically arranged into the standard format writes the attributes of font size, line spacing and the like into the program according to the borrower standard format in the borrower automatic generation system based on the VBA language. The subprogram is mainly used for realizing standardization and facilitating the connection of work of all departments.
Preferably, the operation interface is composed of an 'automatic borrowing item detail generation' button and an information cell which are created in an EXCEL worksheet. In order to make the operation convenient and the operation process can be expressed visually, an operation interface automatically generated by a borrowed part is firstly established in an EXCEL worksheet, the operation interface is simple, only one 'automatically generated borrowed part detail' button and an information cell form are formed, the button is used for controlling whether a VBA program starts, and the content displayed by the information cell form is the program running state.
Claims (10)
1. An automatic borrowed piece generation system based on VBA language is characterized by comprising the following steps: firstly, renaming all detail lists which are derived from a design drawing and contain all details, moving the lists to a folder where the system is located after the naming is finished, then creating an operation interface in an EXCEL worksheet, sequentially opening all the lists and automatically judging borrowed parts through a VBA program by the operation interface, and finally automatically arranging formats according to the standard format of the borrowed part detail lists and then closing the tables until all the detail lists in the folder are arranged into the borrowed part detail lists which only contain the standard format of the borrowed part details.
2. The system for automatically generating borrowed articles based on VBA language according to claim 1, wherein the step of the operation interface opening each table in turn and automatically judging borrowed articles by VBA program comprises a main program for displaying the running state of the program and calling the subprogram in the label of the operation interface of the system, a subprogram for opening the worksheet in turn to automatically judge borrowed articles and saving and closing the worksheet, and a subprogram automatically arranged into a standard format.
3. The automated borrower generation system based on VBA language of claim 2, wherein the main program comprises the following steps: firstly clicking a button for automatically generating borrowed part details on an operation interface, displaying whether a program is successfully started and is in operation in an information cell of the operation interface, if the program is in operation, displaying' spelling loading.
4. The system for automatically generating borrowed articles based on VBA language according to any one of claims 2 to 3, wherein the subroutine steps of sequentially opening worksheets for automatically judging borrowed articles and saving and closing worksheets are as follows: the method comprises the steps of firstly determining a file path needing to be opened and arranged, determining a specific file needing to be opened according to a file name, judging whether the file needs to be edited and arranged or not according to file conditions which do not need to be arranged after the file is opened, directly closing the file if the file does not need to be arranged, determining a file name, opening another file, judging whether the file needs to be arranged or not until the file needing to be edited and arranged is opened, screening whether a borrowed part is in the file needing to be edited and arranged or not according to the borrowed part judgment condition, automatically judging the next line until the line is finished if the line is judged not to be the borrowed part according to the borrowed part judgment condition, automatically selecting the content of the line and then copying when the line is judged to be the borrowed part, simultaneously opening a template file 'borrowed part detail table', pasting the copied content at a corresponding position, continuously judging, copying and pasting the next line, repeating the actions until the judgment is finished, jumping out of subprograms, and saving and closing a work table after the screening is finished.
5. The system for automatically generating a borrower from a VBA language according to claim 4, wherein the opened file has the same name as the operation interface file and does not need to be opened, otherwise, the opened file is opened.
6. The system for automatically generating borrowers from VBA language according to claim 4, wherein said opening of the file to be edited requires deleting the "material" column that is not useful for borrowers and then extracting the file name.
7. The automated borrower generation system according to claim 4, wherein said file condition not requiring collation is: the files which do not need to be sorted are the files with the characters of the sequence numbers in the cells at the intersection of the 1 st column and the 2 nd line of the opened files, so that the files do not need to be sorted.
8. The system for automatically generating a borrowed article based on VBA language according to claim 4, wherein the borrowed article judgment condition simultaneously satisfies the following conditions: a. after the file name needing to be edited and sorted is extracted, seven characters are intercepted from the right side of the name, the characters left after interception are used for comparing with the characters 'the numbers of all parts or standard parts' in the cells of the picture number column of the opened file, if the characters of the cells are different from the characters left after interception, the parts are borrowed, and if the characters are the same, the parts are directly-belonging parts of the parts, namely the parts are not borrowed; b. if the first two characters in the drawing number row unit cells are 'GB', the drawing number row unit cells are standard elements, namely, the drawing number row unit cells are not borrowed elements, and otherwise, the drawing number row unit cells are judged according to the condition a; c. if the first two characters in the figure number row unit cells are 'codes', the item row is not a borrowed item, otherwise, the item row is judged according to the condition a; d. and if the first two characters in the figure number row unit cells are empty characters, the drawing is not a borrowed article, otherwise, the drawing is judged according to the condition a.
9. The system for automatically generating borrowers from VBA language according to any of claims 2 to 3, wherein said sub program which is automatically arranged into standard format writes attributes of font size, line spacing, etc. into the program according to the borrowers standard format in the system for automatically generating borrowers from VBA language.
10. The automated borrower generation system according to any of claims 1-3, wherein the operation interface is comprised of an "automatically generated borrower details" button and an information cell created in an EXCEL worksheet.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811450005.9A CN109582942B (en) | 2018-11-30 | 2018-11-30 | Borrowing piece automatic generation system based on VBA language |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811450005.9A CN109582942B (en) | 2018-11-30 | 2018-11-30 | Borrowing piece automatic generation system based on VBA language |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109582942A CN109582942A (en) | 2019-04-05 |
| CN109582942B true CN109582942B (en) | 2022-11-15 |
Family
ID=65924093
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811450005.9A Active CN109582942B (en) | 2018-11-30 | 2018-11-30 | Borrowing piece automatic generation system based on VBA language |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109582942B (en) |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| GB0028434D0 (en) * | 2000-11-22 | 2001-01-10 | Decision Curve Ltd | Methods and apparatus for automatically producing spreadsheet-based models |
| CN105069222B (en) * | 2015-08-04 | 2018-07-06 | 沈阳机床股份有限公司钣焊分公司 | Sheet metal process automatic creation system |
| CN106020801B (en) * | 2016-05-12 | 2020-11-10 | 祝冠英 | Graphic fourth generation language and application generation system thereof |
-
2018
- 2018-11-30 CN CN201811450005.9A patent/CN109582942B/en active Active
Also Published As
| Publication number | Publication date |
|---|---|
| CN109582942A (en) | 2019-04-05 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20100017698A1 (en) | Flexible Multiple Spreadsheet Data Consolidation System | |
| CN110059121B (en) | Method, system, device and storage medium for rapidly exporting bill of materials | |
| CN102707669B (en) | Method for constructing CAM (computer-aided manufacturing) system for punching for automobile girders | |
| CN103440232A (en) | Automatic sScientific paper standardization automatic detecting and editing method | |
| CN109325730A (en) | A method of it supports that BOM is directly imported production system | |
| CN112597353B (en) | Text information automatic extraction method | |
| CN103440233A (en) | Automatic sScientific paper standardization automatic detecting and editing system | |
| CN111400354B (en) | Machine tool manufacturing BOM (Bill of Material) storage query and tree structure construction method based on MES (manufacturing execution System) | |
| CN110045981A (en) | A method of analytical technology is compared based on source code and establishes demand tracking relationship | |
| CN106408435A (en) | Process compiling system and process compiling method | |
| CN112258061B (en) | Intelligent risk analysis early warning system and early warning method for whole process of project | |
| CN111460401B (en) | Product automatic tracking method combining software product process information and text similarity | |
| CN106845898A (en) | A kind of intelligent label data acquisition and logistics sorting control method | |
| CN109582942B (en) | Borrowing piece automatic generation system based on VBA language | |
| US20040267401A1 (en) | Engineering drawing data extraction software | |
| CN104182858B (en) | A kind of retroactive method of goods and materials Life cycle | |
| CN109165293A (en) | A kind of expert data mask method and program towards fashion world | |
| WO2008023655A1 (en) | Production system creating system and production system creating method | |
| CN107609077A (en) | Wlding approaches to IM | |
| CN111913962A (en) | Multi-dimensional annual detailed fund planning system and method | |
| CN111241176A (en) | Data management system | |
| CN109711799A (en) | Guide the teaching software and its operation method of the standardization office of administration hilllock | |
| WO2007049800A1 (en) | Document creation support device | |
| CN110704625A (en) | CAD (computer-aided design) -based drawing information batch automatic processing method | |
| CN108959588A (en) | Text customer service robot intelligence learning method based on big data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |