KR20060033383A - Making method for bill of material using visual basic - Google Patents

Abstract

The present invention relates to a method for automatically creating material information using visual basic, which enables the automatic creation of material information using visual basic for the automation of a bill of material (BOM) task.

Automatic material information creation method using Visual Basic according to the present invention, the first step of converting the material information for the electronic product into a computerized material information file format suitable for a predetermined format set by the user; A second step of creating a substitute material list for reference by inserting the substitute material list used in the previous project into the computerized material information of the marine project; Generating a computerized material information by automatically adding the created substitute material list to the computerized material information of the corresponding project; And a fourth step of correcting an error comparing the generated computerized material information with the original computerized material information to complete the final computerized material information.

E-BOM, Visual Basic

Description

Making method for Bill of Material using visual basic}

1 is a flow chart showing a conventional material information creation method.

2 is a flowchart illustrating a method of automatically creating material information using visual basics according to an exemplary embodiment of the present invention.

3 is a flowchart illustrating a method of converting an E-BOM into a practical BOM in FIG. 2 of the present invention.

4 is a flowchart illustrating a method of creating a substitute material list in FIG. 2 of the present invention.

5 is a flowchart illustrating a method of automatically inserting a substitute material into a BOM in FIG. 2 of the present invention.

6 is a flow chart illustrating a comparison of the computerized BOM and the original BOM in Figure 2 of the present invention.

The present invention relates to a method of automatically creating material information using Visual Basic, which enables the automatic creation of material information using Visual Basic for the automation of Bill of Material (BOM) work.

In general, Bill of Material (BOM) contains information about which item a certain product is composed of. In other words, a BOM is data that defines a relationship between items. Most of the products we encounter are all assemblies of different parts. For example, ballpoint pens, telephones, cars, computers, keyboards, audio, video, cameras, washing machines, vacuum cleaners, schematics, etc. are all made through the assembly of many parts. Even a very simple product (for example, a ballpoint pen and a pencil), if you think carefully, you can see that it consists of 5-10 parts.

In other words, the material information refers to the parts scheme of the product for manufacturing the product. To build a schematic tool for a model, you list the parts and raw materials that make up it. The bill of materials can be divided into engineering bill of materials (E-BOM) in engineering and P-BOM in production / material.

The BOM generation process as described above is illustrated in FIG. 1.

Referring to FIG. 1, first, an electronic engineering material information (E-BOM) of an engineer side may be generated in a schematic tool (S11), and the computing material information of a hardware engineer side may be input into a company enterprise resource planning (ERP) system. In order to make or use the actual work to be modified (S12).                         

Then, the BOM is completed to determine whether the substitute material of the product is inserted into the BOM (S13). Here, the method of inserting into the BOM is performed at every event progress.

The bill of materials created above is directly input to the computer by hand (S14), and the person inputs the computer input BOM and the original BOM one by one (S15).

As mentioned above, since a person manually works for a desired file type by using only functions provided by Excel to create a BOM file, it takes a lot of time and has a lack of accuracy in BOM generation and hass to repeat every event. There is a feeling.

In addition, mistakes in creating BOM files can lead to development delays with catastrophic financial losses to the company. In addition, development engineers spend a lot of time on BOM tasks, which means they can't spend a lot of time on other development tasks.

It is a first object of the present invention to provide a method for automatically creating material information using Visual Basic, which enables to automate the automation of electronic product design BOM task using VBA (Visual Basic Application).

A second object of the present invention is to provide a method for automatically creating material information using Visual Basic, which can shorten time through an automated program of an electronic product design BOM task.

A third object of the present invention is to provide a method for automatically creating material information using Visual Basic, which can secure the accuracy of data by preventing mistakes by manual operation through automation.

Automatic material information creation method using Visual Basic according to the present invention for achieving the above object,

In the automatic creation method of computerized material information using Visual Basic,

A first step of converting material information on the electronic product into a computerized material information file format suitable for a schedule format set by a user;

A second step of creating a substitute material list for reference by inserting the substitute material list used in the previous project into the computerized material information of the marine project;

Generating a computerized material information by automatically adding the created substitute material list to the computerized material information of the corresponding project;

And a fourth step of correcting an error comparing the generated computerized material information with the original computerized material information to complete the final computerized material information.

Preferably, in the first step, when the file name of the computerized material information to be converted provided in the schematic tool of the electronic product is inputted, each item is divided into columns, the respective items are rearranged, the unnecessary items are deleted, and values Removing cell without space, removing space between characters, changing font size, cell width, and changing each item name.                     

Preferably, the second step is a step of opening the corresponding computerized material information file when the name of the computerized material information file for reference is input, and calculating the total number of rows; Recalculating the total number of rows after searching for a cell containing a specific letter or number for each row among the calculated total rows, removing the row; If there is a cell that ends with a specific letter or number in each row of the recalculated row, compare the values in two cells of adjacent rows to compare the values in two cells that are equal, end with a specific letter / number, or for a cell that is blank. And removing the corresponding row to create a substitute material list.

Preferably, the third step may include receiving the substitute material list and the computerized material information file requiring replacement and calculating the total number of rows of the substitute material list file; Using the value of the part number column of the computerized material information to which the substitute material is to be inserted, each part number in the substitute material list is compared with the current computerized material information, and if there is no part number, the next row is compared. If the part number, if there is a part number in the substitute material list is the first rank or the part number replacement material, the substitute material of the part number is inserted below the corresponding part number of the computerized material information. step; The substitute material number is added to the calculated value of the number of rows, and the replacement material is automatically inserted into the computer material information while repeating the movement to the information comparison loop.

Preferably, the fourth step is to open the generated computerized material information and the original computerized material information, if the value of each column of the two files match, reading the desired value of the corresponding row of the original file, and the generation Comparing the items of the computerized material information, characterized in that it comprises the step of inputting 1 or 0 for error correction according to whether the corresponding value up to.

Referring to the accompanying drawings, a method for automatically creating material information using visual basic according to an embodiment of the present invention configured as described above is as follows.

First of all, in order to automate the electronics design BOM business, the design of electronic products is essential to the management of the components that enter the circuit.In the case of the schematic tool that draws the current circuit diagram, information on the material is provided as a text (txt) file. Visual Basic Applications (VBA) are used to automate tasks that require repetitive conversion of files to the type of company or engineer desired.

The material information automation method using the visual basic is as shown in FIG.

Referring to FIG. 2, first, the utility material is converted into a practical BOM form of the electronic material information (E-BOM) (S101). In other words, the computational material information generated from the engineer's schematic tool is automatically generated in the form required by the engineer or in the form of material information suitable for the company's computational tool format.

Then, a substitute material list is made (S103). To create such a substitute material list, the substitute material list used in the existing project can be automatically generated directly from the project's computerized BOM.

Subsequently, the substitute material is automatically inserted into the material information (S105). In order to complete the BOM of the project, it is necessary to determine the substitute material and add the substitute material. The substitute material list created above is automatically added to the BOM.

Finally, the error is corrected by comparing the computerized BOM and the original BOM (S107). The final computerized BOM is completed after inputting the BOM generated in step S105 into the company's computer system, and the error can be corrected by comparing the original computerized BOM with the original. In addition, in step S107, changes of a BOM may be displayed for each item for each development event, and any type of file output to Excel may be compared.

Automatic BOM generation process (S101 ~ 107) as described above may be implemented by the engineer step by step, the entire process may be implemented at once in the ERP system. However, in order to make a BOM or a computational BOM that can actually use the E-BOM provided by the schematic tool, a program using a Visual Basic Application is performed as described above and each step as shown in FIGS. 3 to 6. It can be implemented in detail.

3 is a flow chart illustrating in detail the step (S101) of converting to the practical BOM form of the E-BOM in Figure 2 of the present invention. These steps will provide user input to convert the E-BOM file into a practical BOM file of your choice on VBA.

Referring to FIG. 3, first, an input function (InputBox) is used to receive an E-BOM file name to be converted provided by a circuit diagram tool from a user (S111). In other words, VBA provides an input function that allows the user to input the name of an E-BOM file to convert or to select a specific file.

When the text file in the initial E-BOM format is opened in Excel, each item is divided into columns using a delimiter (^) in the open text method (S112). In other words, by inputting file1.txt ^ file2.txt ^ file3.txt etc., each item is arranged in columns (column A, column B, column C, etc.).

Then, each item is rearranged to a desired format in the Excel file (S113). That is, the order of each item is rearranged by using Delete, Insert method, etc. according to the business needs or the format of the company's computer system, and the unnecessary items are deleted.

Then, the garbage value is removed from the cell value (S114). That is, when dividing the column using the delimiter (^) in step S112, the cell having no value (ie, blank) contains garbage value. To delete cells without these values, remove the values you want to delete with spaces using the Replace method. For example, if the value you want to delete is @, use "@" → "" to remove it with a space as a replacement method.

In addition, the specification contents are integrated into one cell (S115). That is, in the case of a string, the space between the characters is attached by using the operator (&). In the case of specification, it is inconvenient to divide it into several columns, so it is integrated into one cell by using the operator (&).                     

In addition, the cell size is optimized and the cell size is changed and input so as to facilitate the user's view (S116). That is, the font size, cell width, and item name for each column of characters are adjusted.

In this way, the text file is optimized as a cell of the Excel file, and converted into a practical BOM.

4 is a flowchart showing in detail the step (S103) of creating a substitute material list of FIG. 2 of the present invention.

Referring to FIG. 4, a computer BOM file name to be referred to is received (S131). For example, you will be asked to enter a computerized BOM file name for reference in VBA, which can be entered in the form filename = InputBox ("filename") or imported directly.

Then, the computerized BOM file to be referenced is opened (Workbooks.open) (S132). It calculates the total number of rows (row = 1,2,3, ...) of the open computerized BOM file for reference. For example, this would be calculated using the number of rows 1 = Selection.Rows.count.

Subsequently, in the loops S134 to S138, the cells are sequentially examined for each row of the row number 1, and then a specific character or the number is compared by comparing whether the cell includes a specific character (S135) or a specific number (S137). The corresponding column of the cell containing the number is removed (S136 and S137). For this purpose, the first row of the number of rows 1 (k = 2) is examined, and k = 1 is usually performed from k = 2 since a title is input.

That is, in step S134, the space before and after the letter is trimmed in a certain cell (eg, number 4) and defined as a random value (a). For example, use a = Application.Trim (Object.Cells (k, 4) .Value). The fourth cell of the second row is called "a" and the spaces between the characters are removed.

In step S135, it is checked whether a specific character ("* Q *") is included in the cell a (S135). Here, if you define a = "* Q *", it checks whether the character "Q" is included in the cell before and after. If the character (a = * Q *) exists, the vertical column (Rows (k)) is checked. It is removed (S136). Where * represents all characters.

In step S137, it is checked whether a is recorded as a specific number. For example, if a = 1, the corresponding row (k = 2) is deleted (S138).

Thereafter, if k = 2 columns are performed, the operation is repeated for the total number of rows 1 in steps S134 to S137 again from k = 3. In this case, for k to 2 rows to the total number of rows 1, after checking whether a specific character or a specific number exists in a specific cell of each row, the corresponding column is deleted when there is the specific character or a specific number.

Since the deleted rows are generated, the total number of rows is recalculated and called row 2. That is, the number of rows 2 is obtained by Selection.Rows.count.

In this case, in the loops S141 to S147, the same cells of the calculated row number 2 and the next row are compared with each other to determine whether the same number exists (S141), the same value (S142), and whether a specific number is included in the two cells. The corresponding line is removed by searching for a space (S144) or the like (S146) (S143, S145, and S147).

That is, when the row number 2 is calculated, the level value of the specific cell of the row number 2 and the level value of the specific cell of the next row are replaced with a and b (S140). Here, a = Application.Trim (Object.Cells (k, 4) .Value) and b = Application.Trim (Object.Cells (k + 1,4) .Value). The a level value is the k th row and the b level value is the k + 1 th row. And 4 means the fourth cell in row a and row b.

Step S141 compares whether a ends in 2 or 3. If a = "* 2" or "* 3" does not end, the count is incremented and the process moves to the loop S140 from the next row. If a = "* 2" or "* 3" ends in step S141, check whether a and b are the same (S142). If the same, delete the corresponding row and make k-k to k-1 (S143). Go to step S140. If a and b are not equal, check whether a = "* 2" and b = "* 3 are satisfied (S144). If the condition is satisfied, the corresponding row (k) is removed, and k is k-1. After moving to the loop (S140) (S145).

In step S146, if the condition S144 is not satisfied, the value of b is checked for a space (b = "") (S146). If the value is blank, the corresponding line (k) is removed, and then k lines are k-1 lines. After that, the process moves to the next row (S147).

By repeating the steps S140 to S147 as described above, if a row ends with a specific number or is blank in the same cell of the adjacent row in row number 2, the corresponding row is removed, leaving only the row with the part number having the replacement material and deleting the remaining rows. The substitute material list is completed (S148).

5 is a detailed description of a process of automatically inserting a substitute material into the BOM of FIG. 2 (S105) as follows.

First, a BOM file and a substitute material list file name for which a replacement is required are input (S151), and each file is opened (S152).

The total number of rows of the substitute material list file is calculated (S153). That is, the number of rows = Selection.Rows.count.

And, if the value of the part number columns of the BOM to insert the replacement material is rng01s (S154), it can be defined as set rng01S = worksheets (). Range (). SpecialCells (xlTextValues).

Subsequently, the loop step S155 to S161 automatically inserts the substitute material into the BOM file, and compares whether the part number is equal to the value of the BOM part number column (part. = Rng01) (S155). If it is not the same, the count value is increased by one (S156), the process proceeds to the next column of the row, and if it is the same, it is checked whether the part number is the first rank (S157).

If the part number is the first priority, the substitute material of the part number is inserted below the corresponding part number of the BOM (S159).

However, if the part number is not the first rank, it is checked whether the part number is a substitute material (S158), and if the part number is not a substitute material, the process proceeds to the next row number of the corresponding column (S159), and if the part number is a substitute material, the part number The related substitute material of is inserted under the corresponding part number of the BOM (S160).

Then, the count value is obtained as "count = count + number of substitute materials" by adding the number of substitute materials inserted below the part number from the original count value (S161). That is, the replacement material list row is skipped by the number of replacement materials.

The loop steps S155 to S161 are repeated repeatedly until the number of rows calculated initially is over, and the number of rows in the next column is repeated after the number of rows in the column is finished, comparing the part number in the replacement material list with the current BOM, If the part number in the table is a first-order or replacement material, it is automatically inserted under the part number in the BOM.

While performing this operation for the total number of rows and columns in turn, the replacement material is automatically inserted into the BOM to complete the BOM file (S162).

Here, the substitute material list is different for each project, so it is better to manage it separately. There is also a default project to reference when creating a replacement list. Therefore, the previous program can create a substitute material list in the parent project, modify the substitute material list according to the project characteristics (or create a common substitute material list) and insert it into the BOM (first project). Chose. Since the replacement material list and the BOM's part no 1 rank may be the same or different, program the replacement material to be inserted accordingly.

FIG. 6 is a process of correcting an error by comparing the computational input BOM of FIG. 2 with the original BOM. A detailed description thereof is as follows.

Referring to FIG. 6, two BOM file names to be compared are input (S171), and two BOM files to be compared are opened (S172).                     

You will receive a column to compare in the two BOMs (S173). That is, the comparison column in the first BOM file is called X, and the column in the second BOM file to be compared is called Y.

Then, values in the column to be compared are defined (S174). That is, the value of column X is called rns01s, and the value of column Y is called rns02s. Defined as Set rns01S = Worksheet (). Range (). SpecialCells (xlTextValue) and defined as Set rns02S = Worksheets (). Range (). SpecialCells (xlTextValues). WorkSheet () indicates a work sheet, range () indicates a comparison column, and SpecialCells (xlTextValue) is the value of a specific cell.

In the loop stages S175 to S177, two loops are repeatedly operated. The inner loop S175 is sequentially increased from rns01 and rns02 in rns02s, and the outer loop stage S175a increases the value of rns01. The next column is compared in step S175 starting from rns02s to rns02.

That is, the first return loop compares each cell in the column of the first BOM file with all the cells in a particular column of the second BOM file in turn to see if they match (rns01 = rns02), and if so, the value of the particular column in the first BOM file. It is equal to the value of the column of the second BOM file (S177). For example, if rns01 = rns02, it reads as rns01.offset (0,6) = rns02.Offset (0,4). Since Offset (0,6) has an offset of 6, the value of the sixth row (cell) of the rns01 column is read as the value of the fourth row (cell) of the rns02 column. After the first feedback loop, it operates as the second feedback loop and performs the repeated operation of moving back to the first feedback loop. Therefore, if the same part number is found in steps S155 to S157, the corresponding location number is read.

In the S178 to S180 loop steps, only Rns01s of the first BOM file is compared. In step S178, if the value of the offset fifth cell and the sixth cell of rns01 are the same, it is defined as 1 in the rns01 seventh result row (S179), or 0 is defined in the seventh result row of the rns01 column, and the row is red. Mark and distinguish by (S180). In other words, in step S178, if the values of rng01.Offset (0,5) and rng01.Offset (0,6) match, the result row is rng01.Offset (0,7), that is, defined as 1 (true) in the 7th row. If it does not match, rng01.Offset (0,7) = 0, that is, it is defined as 0 (false) in the 7th row, and the column is marked as red (Rows (k) .interior: RED).

By correcting the error repeatedly for all the rows of the file 1, the error correction is completed (S181).

In this way, you can use VBA to create an overall project management program. When the development model is in mass production, it is divided into several models by configuration. By implementing such model management as an automated program, engineers can not only manage the model more effectively, but also save time and work load. For example, after mass production, several models are created each month, and according to the model name and configuration, it automatically arranges the models so far and displays new models or changes through colors, so that the developer can compare or manage the files one by one. There is no need to do it.

So far, the present invention has been described with reference to the preferred embodiments, and those skilled in the art to which the present invention pertains to the detailed description of the present invention and other forms of embodiments within the essential technical scope of the present invention. Could be implemented. Here, the essential technical scope of the present invention is shown in the claims, and all differences within the equivalent range will be construed as being included in the present invention.

As described above, according to the method for automatically creating material information using Visual Basic according to the present invention, by automatically generating material information, the developer can develop work by focusing on more important tasks by reducing work time, ensuring accuracy, and automating simple tasks. The quality and work efficiency can be improved, and the uniformity can be secured by forming the form in the division.

Claims (5)

In the automatic creation method of computerized material information using Visual Basic, A first step of converting material information on the electronic product into a computerized material information file format suitable for a schedule format set by a user; A second step of creating a substitute material list for reference by inserting the substitute material list used in the previous project into the computerized material information of the marine project; Generating a computerized material information by automatically adding the created substitute material list to the computerized material information of the corresponding project; And a fourth step of correcting an error comparing the generated computerized material information with the original computerized material information to complete the final computerized material information. The method of claim 1, In the first step, when a file name of computerized material information to be converted provided to a schematic tool of an electronic product is inputted, each item is divided into columns, the respective items are rearranged, unnecessary items are deleted, and cells having no value are provided. And removing the space between characters, changing the font size, cell width, and the name of each item. The method of claim 1, The second step may include opening a corresponding computerized material information file and calculating a total number of rows when a name of the computerized material information file to be referred to is input; Recalculating the total number of rows after searching for a cell containing a specific letter or number for each row among the calculated total rows, removing the row; If there is a cell that ends with a specific letter or number in each row of the recalculated row, compare the values in two cells of adjacent rows to compare the values in two cells that are equal, end with a specific letter / number, or for a cell that is blank. Automatic material creation method using Visual Basic, characterized in that it comprises the step of removing the row and creating a substitute material list. The method of claim 1, The third step may include receiving the replacement material list and the computer material information file requiring replacement, and calculating the total number of rows of the replacement material list file; Using the value of the part number column of the computerized material information to which the substitute material is to be inserted, each part number in the substitute material list is compared with the current computerized material information, and if there is no part number, the next row is compared. If the part number is in the substitute material list, if the part number in the substitute material list is the first rank or the part number replacement material, insert the substitute material of the part number under the corresponding part number of the computer information Doing; And repeating the information comparison loop by adding the substitute material number to the count value of the calculated number of rows, and automatically inserting the substitute material into the computerized material information. The method of claim 1, The fourth step is to open the generated computer information and the original computer material information, if the value of each column of the two files match, reading the desired value of the corresponding row of the original file, and the generated computer material information Comparing the items of, and the step of inputting 1 or 0 for error correction according to whether the corresponding value up to the automatic material information creation method using Visual Basic, characterized in that it comprises.

Images