Data replacement between Excel documents---the computer version of the app to automatically manipulate the magician

Http://www.won-soft.com/macro/solution/excel-data-replace.htm Introduction:

In our daily work, it may often be necessary to use a variety of data forms to deal with. such as the financial work of various data forms, production management of various production data tables, the former Human resources department of the payroll and so on. In the process of processing these data, we may often encounter to make a table, but this table of data needs to be taken from another table, there are some associated rules between each data, for example: The end of the year will be a payroll statistics, the total number of all employees all year-round bonuses, wages, real wages, Debit and other items. The data may be scattered in several tables, each with the name of the employee and the bank account number, and we need to take the numbers from each table according to the employee's name or bank account, and then fill in the statistics sheet. In general, we are looking through the table, and then using Copy/paste method to complete this kind of data substitution, if more data, the intensity of this work is conceivable. Here we take a look at the Automatic Wizard can help us reduce or even replace us with this kind of data replacement work.

Analysis:

Let's first comb the basic steps of data substitution between tables, here we assume that there are two tables, a and B,a table in the name of the person, the bank account number, the total annual wage, bonus total, where the bonus total this column needs to be from the B table, B has the name and bonus total:

1. First define the variables that need to be used;
2. First we open two sheets;
3. Take a person's name from a table.
   Note: If there is no person's name, exit directly, otherwise it is not finished.
4. Check the bonus totals from the B list by name.
5. If found, paste the bonus number into the bonus Total column in Table A.
6. Add the line number 1, and skip to step 3rd to continue with the next row of data substitution.

Automatic wizard-enhanced version supports Microsoft Excel document processing commands that allow you to read or write to a cell, and also support finding cell data. At the same time, automatic wizards support loops and jump commands, and it can be seen that we can use these commands to simulate the process of data substitution between tables. Now let's try to build a script to do this based on the above steps.

Setup steps: First step: Create a blank magic

First, open the Magic Manager , click the menu " Magic ", select " Create a blank magic ", such as:

After clicking on the menu item, the Magic " Properties " dialog will pop up to let us specify some of the properties of the new magic, we give the Magic a name " data substitution between Tables ", and then click " OK " to build the magic directly. Such as:

In this case, the wizard will pop up a dialog asking if you want to edit the magic, and we choose " Yes " to go to the editor.

Once the Magic Editor is open, we'll step through the required commands as per the steps listed earlier.

Step Two: Set the variable

First, you need to define multiple variables to specify the number of rows to read, save the read content, and so on. Click on the menu " variables " of the editor, then select " Add a variable ", add the variable "v_ ", "v_ name ", "v_ the line number found ", " V_ End ","v_ bonus ".

After the variable is added, we also need to set the initial value for "v_ ", click the menu " insert ", select " variable Class ", select " Set Variable Value ", set the value of the variable to 2 ( Our data starts from the second line in table A.

Step three: Open two sheets

Click on the Editor's menu " insert ", select " document Class ", and finally select " Open Document ", such as:

In the Command Options window, enter the path to the a table, such as: A.xls, the document ID name we enter: "a Table ", the document identification name is used to represent the document, in the subsequent operation of table A, we need to specify that the name is to process a table. When the settings are complete, click OK, such as:

Now we also need to open the B table. Click the Insert menu again to add the " Open document " command, and use "B table " as the document ID name.

Fourth step: Take a person's name from a table

First, we insert a label here (the label is used in conjunction with the jump command, to specify where to jump to), the label will be used when the last jump.

Click on the Editor's menu " insert ", select " Process Class ", then " label ", and set a name, such as:

Click the Editor's menu " insert ", select " document Class ", select " read Excel document Cell ", set the relevant parameters, such as:

Because there is more than one data to replace, the cell row here uses the variable "v_ " instead of a specific number. We put the read content in the variable "v_ name ".

Fifth step: Determine if you have finished reading all the data

Click on the Editor's menu " insert ", select " Process Class " and select " conditional judgment (based on variable value)" to determine whether the variable "v_ end " is 1. Such as:

And in the conditional judgment, add " End " (Click the menu " insert ", select " Play Control class " inside the " end ". Such as:

The sixth step: in the B table, according to the name search bonus Total

Click the Editor's menu " insert ", select " document Class ", select " find Excel Document Cell ", set the relevant parameters, such as:

Here we will use "v_ name " Instead of what we are looking for, and return the found line number to the variable "line number found in V_ ".

Seventh step: read out the bonus data

Click " Insert " in the Editor's menu, select " document Class ", select " read Excel document cell " and read column C in table B to "v_ bonus ".

Eighth step: Put the bonus sum in table B in Table A

Click on the Editor's menu " insert ", select " document Class ", select " write Excel Document cell " and read the number of people bonuses just found, such as:

Nineth step: Jump to start

Command execution here, we have only completed a row of data substitution, so we need to do some additional work to continue to complete all the data replacement. Click on the Editor's menu " insert ", select " variable Class ", select " Set Variable Value ", and the variable "v_ line number " +1, so as to replace the next row of data. Such as:

We then add a jump command to the " read people name " section. Such as:

End:

Here, our data replacement magic has been established.

Data replacement between Excel documents---the computer version of the app to automatically manipulate the magician