External databases

To work with external database tables, choose the Data ^ Import External Data ^ New Database Query command, which executes Microsoft Query and enables you to choose your databases and define queries. The results of a query can be directed back to your worksheet.

AB C D

E 1 F

G

H

_

2 3

Month -rlSalesRep ▼JRegioii ▼

Contacts Sales ▼

Annualized t

Jan Bob

North

58 283,800

3,689,400

5

Jan Frank

North

35 507,200

6,086,400

Jan

Daul

South

25

107,600

1.291,200

10

Jan

3andy

South

47

391,600

4,699,200

Jan

Mary

South

39

226,700

2,720,400

Feb

Bob

North

44 558,400

6,700,800

-eb

Jill

North

46 350.400

4.204,800

Feb

Frank

North

74

411,800

4,941,600

12

reb

=aul

South

29

154,200

1,850,400

-eb

3andy

South

45

258,000

3,096,000

16 17

-eb

Mary

South

52

233,800

2,805,600

Mar

3ob

North

30

353,100

4.237,200

Mar

Jill

North

44 532,100

6,385,200

Mar Frank

North

57 258,400

3,100,800

Mar

Daul

South

13

286,000

3,432,000

20

Vlar

3andy

South

14

162,200

1,946,400

Mar

Mary

South

36

134,300

1,611,600

*

f

Total

5.209.600

62.799.000

None

24

Average

25

Mh

27

Var

M 4 ► M \Sheet 1 , hi ►!

Figure 2-10: Excel 2003's new list feature makes it easy to identify a worksheet database.

Excel also lets you work with data objects independent of Excel through both Data Access Objects (DAO) and ActiveX Data Objects (ADO). Both systems allow you to access external databases from VBA and make changes to the database.

You can also create Web queries to bring in data stored in a corporate intranet or on the Internet. Figure 2-11 shows an example of a Web Query.

Figure 2-11: Create a Web Query to import data into a worksheet.
0 0

Post a comment