Master Essentials to implement Excel dynamic link external database

We sometimes need to fetch data from other databases in Excel, and when we want other database data to change, the data that is fetched in Excel changes dynamically. Here's how to implement a dynamic linked database in Excel by using new database query (Microsoft query).

The first time you use the new database query in Excel to query for data, you are prompted to install if Microsoft Query is not installed on the system.

One, Excel link VFP database

VFP is a commonly used database, first of all to introduce. Assuming that the school's data is taken from the PX.DBF table, the following 5-bit code for the school is known, the data is in the field x16, the data value is 03 rows and 06 rows, and is placed in an Excel single cell.

The steps are as follows:

1, click the menu "Data"-"Import external Data"-"New Database Query", the "Select Data Source" dialog box, select "Visual Foxpro tables*", press "OK". If you are not familiar with SQL statements, use the Default Query Wizard to create them.

(Figure 1)

2, in the "Configure Connection" dialog box, press the "Browse" button to select the path of the table px.dbf, press "OK" key.

(Figure 2)

3. In the Query Wizard-Select Columns dialog box, select the columns in the query results, and the data you want to select is in the "x16" field, so move the x16 column in the available table "PX" to the columns in query results, and press Next.

(Figure 3)

4. In the Query Wizard-Filter Data dialog box, because the conditions set in this example are in the field school code "XXDM" and line number "BH", non field "x16", so Click "Next", temporarily do not filter the data.

(Figure 4)

5, in the Query Wizard-Sort Order dialog box, because this example takes a single data, click Next.

(Figure 5)

6, the Query Wizard-Complete dialog box appears, because you want to filter the data, so select "View data in Microsoft Query or edit the query" and click "Finish".

(Figure 6)

7, go to "Microsoft query", see the query Results x16 list all the rows of data.

Next, you can filter the data you want by setting the criteria.

Click on the menu "condition"-"Add condition (A) ...", field "BH", Operator "equals", specify value of "03", and Press "added".

(Figure 7)

Continue to the Add Criteria dialog box, select or, Field "BH", Operator "equals", specify a value of "06", and press "add".

(Figure 8)

Continue the Add Criteria dialog box, select and, Field "Xxdm", Operator "equals", specify value of "11201", press "Add" and press "close".

(Figure 9)

But to see the qualifying x16 is empty, this is because the five digits of the school code are "11201".

You can modify the conditional statement directly by "Show SQL", change the px.xxdm= "11201" in the SQL statement to right (px.xxdm,5) = "11201" and press "OK".

(Figure 10)

You can also modify it directly in the conditional field.

(Figure 11)

With the cursor in the x16 field, click "Loop Totals" to add two values to the query, and of course it can be modified directly in the SQL statement.

(Figure 12)

8, close "Microsoft Query", pop-up "Import data" dialog box, select "Location of Data", select "$B $", press "OK".

(Figure 13)

9, but we found that the title row, the real data is displayed to the $b$3, when we need to modify the $b$2 cell "data region properties." Click the $b$2 cell and press the "Data area Property" of the menu, eject the External data Range Properties window, remove the check in front of the Include field name in data format and layout, and change the number of rows in the data range to refresh to overwrite the existing cell with the new data. and clear the cell contents that are not in use, press OK. But still has the title, does not have the relation, clicks the $b$2 cell, presses the right key menu "refreshes the data" to be possible.

(Figure 14)

10, if the PX.DBF database has changed, then how to refresh in Excel, you can click the link data in the Cell right menu, "refresh" manually refresh, or in the data region Properties-Refresh control Select the open workbook, automatically refresh (see Figure 14), This refreshes the start automatic refresh button in the Query Refresh prompt box each time you open the workbook (see Figure 15).

(Figure 15)

11, if you need to edit the query, click $b$2 Cell, press the right menu "query edit", if the Query Wizard can not edit this query prompts, just press "OK" to enter "Microsoft query".

12, $B $ cell query conditions and $b$2 is only the school code, you can copy $b$2 cell to $b$3 units, and then use $b$3 "Edit Query" to modify the value of the school code can be modified.

(Figure 16)

Second, Excel linked SQL database

The linked SQL database is roughly the same as the link VPF. Here is a query that joins two tables.

Click on the menu "Data"-"Import external Data"-"New Database Query", the "Select Data Source" dialog box appears, you can directly select the desired link to the SQL database name.

If the SQL database has a password, you will be prompted to enter a password, press OK. If SQL Server Service Manager is not turned on, an error message appears.

If you don't use the Query Wizard, you can go directly to Microsoft query. Add Table news and the typeid of the typeID and type tables in the Table Type,news table. Select the field name in the Criteria field and enter the criteria value in value, where the condition entered is 1 with the typeID field value of the Type table and the topic of the news table containing "pupil". Click to select the name of the field you want to output, where you select the TypeName field in the Type table and the topic field of the news table.

(Figure 17)

Of course, if you are familiar with SQL statements, you can also enter SQL statements directly.

For the next steps, refer to the 8, to 10, of the.

　　Third, with other databases (such as access data, etc.)

　　Here is no longer to repeat, the basic idea is this. Small series by the way recommend you to see a " insert access, SQL database data in Word "