Querying Microsoft Excel Workbooks

When using ADO to access data from Excel 2007 workbooks, you use the same OLE DB provider that you used earlier in this chapter to access data from Microsoft Access 2007. In addition to Access, this provider also supports most ISAM data sources (data sources that are laid out in a tabular, row and column format). You will use the Sales.xlsx workbook, shown in Figure 20-7, as the data source for the Excel examples.

15 Sales.xlsx

_ h *

II

A

B

C

D

E

1

State

I Channel

Type

Price Range

Quantity i

2

WA

Retail

Art

Mid

9

3

WA

Retail

Art

Low

143

4

WA

Retail

Art

High

17

5

WA

Retail

Art

Mid

23

6

WA

Retail

Art

High

26

7

WA

Retail

Art

Mid

6

8

WA

Retail

Art

Low

4

9

WA

Retail

Art

High

13

10

WA

Retail

Art

Mid

7

11

WA

Retail

Art

Low

25

12

WA

Retail

Dinosaurs

Mid

22

-

h

Sales t?

□ nii r

Figure 20-7

When using ADO to work with Excel, the workbook file takes the place of the database, while worksheets within the workbook, as well as named ranges, serve as tables. Compare a connection string used to connect to an Access database with a connection string used to connect to an Excel workbook.

Connection string to an Access database:

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Files\Northwind 2007.accdb;"

Connection string to an Excel workbook:

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\Sales.xlsx;" & _ "Extended Properties=Excel 12.0;"

Note that the same provider is used, and that the full path and filename of the Excel workbook takes the place of the full path and filename of the Access database. The only difference is that you must specify the type name of the data source you want to connect to in the Extended Properties argument. When connecting to Excel 2007, you set the Extended Properties argument to Excel 12.0. For versions of Excel earlier than 2007, you set the Extended Properties argument to Excel 8.0.

You query data from an Excel worksheet using a plain text SQL statement exactly like you would query a database table. However, the format of the table name is different for Excel queries. You can specify the table that you want to query from an Excel workbook in one of four different ways:

□ Worksheet Name Alone—When using the name of a specific worksheet as the table name in your SQL statement, the worksheet name must be suffixed with a $ character and surrounded with square brackets. For example, [Sheet1$] is a valid worksheet table name. If the worksheet name contains spaces or non-alphanumeric characters, you must surround it with single quotes. An example of this is ['My Sheet$'].

□ Worksheet-level Range Name—You can use a worksheet-level range name as a table name in your SQL statement. Simply prefix the range name with the worksheet name it belongs to, using the formatting conventions just described. An example of this would be [Sheet1$SheetLevelName].

□ Specific Range Address—You can specify the table in your SQL statement as a specific range address on the target worksheet. The syntax for this method is identical to that for a worksheet-level range name: [Sheet1$A1:E20].

□ Workbook-level Range Name—You can also use a workbook-level range name as the table in your SQL statement. In this case there is no special formatting required. You simply use the name directly, without brackets.

Although your sample workbook contains only one worksheet, this is not a requirement. The target workbook can contain as many worksheets and named ranges as you wish. You simply need to know which one to use in your query. The following procedure demonstrates all four table-specifying methods just discussed:

Public Sub QueryWorksheet()

Dim rsData As ADODB.Recordset Dim sConnect As String Dim sSQL As String

' Create the connection string.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Files\Sales.xlsx;" & _ "Extended Properties=Excel 12.0;"

' Query based on the worksheet name. 'sSQL = "SELECT * FROM [Sales$]" ' Query based on a sheet-level range name. 'sSQL = "SELECT * FROM [Sales$SheetLevelName];" ' Query based on a specific range address. 'sSQL = "SELECT * FROM [Sales$A1:E89];" ' Query based on a book-level range name. sSQL = "SELECT * FROM BookLevelName;"

Set rsData = New ADODB.Recordset rsData.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText

' Check to make sure we received data. If Not rsData.EOF Then

Sheet1.Range("A1").CopyFromRecordset rsData

Else

MsgBox "No records returned.", vbCritical

End If

' Clean up our Recordset object.

rsData.Close

Set rsData = Nothing

End Sub

By default, the OLE DB provider for Microsoft Jet assumes that the first row in the table you specify with your SQL statement contains the field names for the data. If this is the case, you can perform more complex SQL queries, making use of the WHERE and ORDER BY clauses. If the first row of your data table does not contain field names, however, you must inform the provider of this fact or you will lose the first row of data. The way to accomplish this is by providing an additional setting, HDR=No, to the Extended Properties argument of the connection string:

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\Sales.xlsx;" & _ "Extended Properties=""Excel 12.0;HDR=No"";"

Note that when you pass multiple settings to the Extended Properties argument, the entire setting string must be surrounded with double quotes and the individual settings must be delimited with semicolons. If your data table does not include column headers, you will be limited to SELECT queries.

Inserting and Updating Records in Microsoft Excel Workbooks

ADO can do more than just query data from an Excel workbook. You can also insert and update records in the workbook, just as you would with any other data source. Deleting records, however, is not supported. Updating records, although possible, is somewhat problematic when an Excel workbook is the data source, because Excel-based data tables rarely have anything that can be used as a primary key to uniquely identify a specific record. Therefore, you must specify the values of enough fields to uniquely identify the record concerned in the WHERE clause of your SQL statement when performing an update. If more than one record meets WHERE clause criteria, all such records will be updated.

Inserting is significantly less troublesome. All you do is construct a SQL statement that specifies values for each of the fields, and then execute it. Note once again that your data table must have column headers in order for it to be possible to execute action queries against it. The following example demonstrates how to insert a new record into the sales worksheet data table:

Public Sub WorksheetInsert()

Dim objConn As ADODB.Connection Dim sConnect As String Dim sSQL As String

' Create the connection string.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\Sales.xlsx;" & _ "Extended Properties=Excel 12.0;"

' Create the SQL statement.

"VALUES('VA', 'On-Line', 'Computers', 'Mid', 30);"

' Create and open the Connection object. Set objConn = New ADODB.Connection objConn.Open sConnect

' Execute the insert statement.

objConn.Execute sSQL, , adCmdText Or adExecuteNoRecords

' Close and destroy the Connection object.

objConn.Close

Set objConn = Nothing

End Sub

Note that if you use ADO to insert a new record into an Excel worksheet, and you use a range name as the table in the INSERT statement, that range name will not be extended to include the new record. Therefore, you should only use ADO to insert records into worksheets in situations where the worksheet name can be used as the table in the INSERT statement.

Querying Text Files

The last data access technique to discuss in this chapter is querying text files using ADO. The need to query text files doesn't come up as often as some of the other situations addressed in this chapter. However, when you're faced with an extremely large text file, the result of a mainframe database data dump, for example, ADO can be a lifesaver.

Not only will it allow you to rapidly load large amounts of data into Excel, but using the power of SQL to limit the size of the resultset can also enable you to work with data from a text file that is simply too large to be opened directly in Excel. For the discussion on text file data access, use a comma-delimited text file, Sales.csv, whose contents are identical to the Sales.xlsx workbook used in the Excel examples in the previous section. The following example demonstrates how to construct a connection string to access a text file:

szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\;" & _ "Extended Properties=Text;"

Note that in the case of text files, the Data Source argument is set to the directory that contains the text file. Do not include the name of the file in this argument. Once again, the provider is informed of the format to be queried by using the Extended Properties argument. In this case, you simply set this argument to the value "Text".

Querying a text file is virtually identical to querying an Excel workbook. The main difference is how the table name is specified in the SQL statement. When querying a text file, the filename itself is used as the table name in the query. This has the added benefit of allowing you to work with multiple text files in a single directory without having to modify your connection string.

As with Excel, you are limited to SELECT queries if the first row of your text file does not contain field names. You must also add the HDR=No setting to the Extended Properties argument if this is the case, in order to avoid losing the first row of data. The example text file has field names in the first row, and you should assume that you need to limit the number of records you bring into Excel by adding a restriction in the form of a WHERE clause to your query. The following procedure demonstrates this:

Public Sub QueryTextFile()

Dim rsData As ADODB.Recordset Dim sConnect As String Dim sSQL As String

' Create the connection string.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Files\;" & _ "Extended Properties=Text;"

' Create the SQL statement.

sSQL = "SELECT * FROM Sales.csv WHERE Type='Art';"

Set rsData = New ADODB.Recordset rsData.Open sSQL, sConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText

' Check to make sure we received data. If Not rsData.EOF Then

' Dump the returned data onto Sheetl. Sheet1.Range("A1").CopyFromRecordset rsData

Else

MsgBox "No records returned.", vbCritical End If

' Clean up our Recordset object.

rsData.Close

Set rsData = Nothing

End Sub

0 0

Post a comment