Using the Open Database Method

Excel 2002 offers a new method for working with databases. The OpenDatabase method, which applies to the Workbooks collection, is the easiest way to get the database data into a Microsoft Excel spreadsheet. This method requires that you specify the name of a database file you want to open. The following example procedure opens the Northwind database located in the C:\Program Files\Microsoft Office\Office10\Samples folder. When you run this procedure, Excel displays the dialog box listing all the tables and queries in the database (Figure 15-13). After making a selection

J 5hippefs.xls

A

I B

B

EZ

1

IS h i ppe rl Dl Corn p anvNam e

Phone

2

1

Speedy Express

(503) 555-3831

3

United Package

(503) 555-3199

A

3

Federal Shipping

(503) 555-3331

5

6

7

8

-

H <

► m • Shippers/

l<l I

±1Q

from the list, a brand new workbook is open with the worksheet showing data from the selected table or query.

Sub OpenAccessDatabase() Workbooks.OpenDatabase _

Filename:="C:\Program Files\Microsoft Office\" _ & "0ffice10\Samples\Northwind.mdb"

End Sub

Sub OpenAccessDatabase() Workbooks.OpenDatabase _

Filename:="C:\Program Files\Microsoft Office\" _ & "0ffice10\Samples\Northwind.mdb"

End Sub

Figure 15-13:

Use the OpenDatabase method with one argument (database file name) to allow the selection of a table or query from a list box.

Figure 15-13:

Use the OpenDatabase method with one argument (database file name) to allow the selection of a table or query from a list box.

ITlrii

X

A

B

C

1 T«iiiMosfF>i|>eiisivePimluct$

llnliPi ice

2jCote de Blaye

2Ë3.5

3 Thüringer Rostbratwurst

123.73

4 Mishi Kobe Niku

37

5 iSir Rodney's Marmalade

8!

6 ;Carnarvon Tigers

62.5

7 Raclette Courdavault

55

8 Manjimup Dried Apples

53

9jTarle au sucre

43.3

10 Ipoh Coffee

4E

11 Rössle Sauerkraut

15. G

12

-

M < ► H North wind/ |

Database data stored in a table or query can be easily retrieved into an Excel workbook using the OpenDatabase method introduced in Excel 2002.

The OpenDatabase method has four optional arguments that you can use to further qualify the data that you want to retrieve:

Optional Arguments for Data Type Description the OpenDatabase Method

CommandText Variant

CommandType Variant

BackgroundQuery Variant

ImportDataAs Variant

The SQL query string. See the following example for using this argument.

The command type of the query. The following command types are available: Default, SQL, and Table.

The background of the query. It can be one of the following constants: PivotCache or QueryTable.

Specifies the format of the query. Use xlQueryTable report or xlPivotTableReport to generate a Query table or PivotTable report out of the retrieved database data.

The following example procedure demonstrates how to use the OpenData-base method with optional arguments. This procedure creates a PivotTable report out of the retrieved customer records. When you run the procedure, Excel displays a list of available fields based on the submitted query string. You can drag one or more fields to the Pivot table layout grid to create your PivotTable report. Figure 15-15 displays the CustomerId field by country.

Sub CountCustomersByCountry() Workbooks.OpenDatabase _

Filename:="C:\Program Files\Microsoft Office\" _

& "0ffice10\Samples\Northwind.mdb", _ CommandText:="Select * from Customers", _ BackgroundQuery:=PivotTable, _ ImportDataAs:=xlPivotTableReport

End Sub

QBtmk2.il>

-inT^Î

A

1 C

1

-

2

J

3

Cnurit of CustornerlD

Country *

Customs rID *

Total

5

Argentina

CACTU

6

OCEAN

7

RANCH

AJ

Arqenliiia Total

■3

; Austria

ERNSH

ID

PICCÛ

tl

Austria Total

12

Belgium

MAISD

13

SUPRD

U

Belgium Total

15

Brazil

COM Ml

IB

FAMIA

17

GO URL

If]

HANAR

. Al

M <

* Ki\NurlliHind/

JjJ._1

Using the OpenDatabase method's optional arguments, you can specify that the database data be retrieved into a specific format, such as a PivotTable report or a Query table report.

0 0

Post a comment