Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Excel's Data Structures

Excel's data-handling features fall into two distinct groups. Most worksheet functions are designed to operate on individual items of data (usually stored in single cells), whereas features such as pivot tables, filtering and so on operate on large sets of data, usually arranged in tables. There are comparatively few worksheet functions, such as VLOOKUP, MATCH and the Dxxx functions that fill the gap between the two paradigms, operating on tables of data but returning single-value results. The way in which we arrange our data on the sheet can have a significant impact on the ease with which Excel's features can be used.

Most workbooks that we see are organized in what can only be described as a haphazard nature. They often try to combine data entry, analysis and reporting within the same area of the worksheet and are therefore a compromise between format and function. To design the best user interfaces, we have to organize the sheet to appeal to the user (such as including blank rows and/or columns around the data), ignoring the arrangement required by Excel's features (such as having to be in a single table). Conversely, to make the most efficient use of many of Excel's features, we have to organize our data in specific ways, which will probably not be the nicest to look at (such as having to leave lots of white space around pivot tables to allow for their changing shape, or include artificial column and row labels).

Unstructured Ranges

Unstructured ranges are usually encountered in the parts of the workbook designed for data entry. The spatial arrangement of the data will probably have some meaning to the user, with labels and formatting used to identify the data to be typed into each cell. When data is arranged in this unstructured manner, we can only use worksheet functions for our analysis. We cannot directly create pivot tables or charts from this data, nor consolidate, filter or sort the items. In practice, we probably wouldn't want to operate on this data as a whole anyway. They're likely to be single, unrelated items of data, where the lack of a structure is not a problem. Ideally, each data-entry cell should be given an unambiguous name, so we can tell at a glance where it's used by other functions.Chapter 23 Excel, XML and Web Services.

Structured Ranges

Most of the features in Excel that are designed to operate on or with large sets of data require the data to be organized in a tabular arrangement, usually with a header row containing unique labels which Excel can use to identify each column. The most notable exceptions to this are the LOOKUP() function and array formulas (see later), which both work better without including a header row. The Data > Consolidate feature works best with an even stricter structure, where the contents of the first column in the data range can be used to identify each row, as you'll see later.

The easiest way for us to set up our data to be most useful to Excel, then, is to put it in a worksheet as a single table, with a header row and consistent data in each column, such as the list of customers shown in Figure 14-1. This data is from the sample NorthWind Access database supplied with Office, usually found at C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.

Figure 14-1. A Structured Range of Data

Chapter 13 Programming with Databases to retrieve data from a database, we can easily create a structured range by populating the sheet from an ADO recordset. Typical code to do that is shown in Listing 14-1, where rsData is an object variable which refers to an ADO recordset.

Listing 14-1. Creating a Structured Range from an ADO Recordset

If Not rsData.EOF Then
' Clear the destination worksheet.
Sheet1.UsedRange.Clear
' Add the column headers.
For lField = 0 To rsData.Fields.Count - 1
Sheet1.Cells(1, lField + 1).Value = _
rsData.Fields(lField).Name
Next lField
' Make the column headers bold, for clarity
Sheet1.Rows(1).Font.Bold = True
' Copy the data from the recordset
Sheet1.Range("A2").CopyFromRecordset rsData
' Give the retrieved data range a name for later use
Sheet1.Range("A1").CurrentRegion.Name = "Sheet1!MyData"
Else
MsgBox "No data located.", vbCritical, "Error!"
End If

Excel 2003's Lists

Working with a list of data is such a common use of Excel that Microsoft added the List feature in Excel 2003 to ease many of the tasks associated with them, such as sorting, filtering and adding and removing rows. A range can be converted to a List using the Data > List > Create List menu item. Figure 14-2 shows the same table of customers converted to a List (with rows 8 to 90 hidden to save space). Notice the thick (blue) border, the automatic appearance of the autofilter drop downs in the top row and the New Data row in row 93. The List can also be set to automatically show a total row, using the same totaling options that are provided by the SUBTOTAL() function. Showing the total row only makes sense if the list contains numeric data, as the only option for textual data is to count the rows. It would have been more helpful to have a "count distinct" option, but perhaps that will be added in a future version of Excel.

Figure 14-2. An Excel 2003 List Range

Chapter 23 Excel, XML and Web Services.

Query Tables

Whenever we use one of the Data > Import External Data menu items to import a text file, a table from a Web page or a database query, the result is a query table. This is just a defined area of the worksheet that encompasses the retrieved data and (optionally) stores the connection information used to obtain the data. If the connection information is stored, the query table can be configured to refresh the data when the file is opened or at regular intervals. We can also tell the query table how to handle different amounts of data, and whether to copy/delete any formulas in adjacent columns.

For anything other than the most basic of database queries, Excel uses the MSQuery application to provide an interface for creating the SQL SELECT statement. If you've used a UI for creating SQL statements before (such as MS Access), the MSQuery interface is easy to understand. Figure 14-3 shows the MSQuery screen, with a query that retrieves some example data from the NorthWind OrderDetails and associated tables.

Figure 14-3. The MSQuery UI for Creating SQL Select Statements

[View full size image]

The biggest problem with creating query tables is that the SQL produced by MSQuery is such poor quality and includes the full path to the database file being queried. This makes it almost impossible to create a worksheet using a query table to retrieve data from an Access database and expect it to work when installed at a client site. To create a robust solution, we always have to include some VBA code to set the query table's Connection and SQL properties. For example, we would rarely be able to use the built-in ability to refresh the query when the file was opened, because it would fail if the database was moved. Instead, we can use code similar to that shown in Listing 14-2, which sets the database location to the same directory as the workbook and updates the query table's properties before doing the refresh. Note that for this example to work correctly, you will need to copy the NorthWind database to the folder containing your workbook. In practice, we would prompt the user to select the database location the first time the workbook was opened and store that choice in the registry for subsequent use.

Listing 14-2. Refreshing a Query Table When Opening a Workbook

Private Sub Workbook_Open()
Dim sDatabase As String
Dim sConnect As String
Dim sSQL As String
'Where is the database to connect to?
'This is the usual location of the Northwind database.
'In practice, this should be a user-configurable option, 
'probably read from the registry.
sDatabase = Application.Path & "\Samples\Northwind.mdb"
If Len(Dir(sDatabase)) > 0 Then
'Create the connection string using ADO
sConnect = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDatabase & ";"
'Create a tidy SQL statement, without the file paths
sSQL = "SELECT O.OrderID, O.OrderDate, CUS.CustomerID, " & _
"       CUS.CompanyName, CUS.Country, CUS.City, " & _
"       CAT.CategoryName, P.ProductName, " & _
"       OD.Quantity, OD.UnitPrice, OD.Discount " & _
"  FROM Categories CAT, Customers CUS, " & _
"       `Order Details` OD, Orders O, Products P " & _
" WHERE CUS.CustomerID = O.CustomerID And " & _
"       OD.OrderID = O.OrderID And " & _
"       P.ProductID = OD.ProductID And " & _
"       CAT.CategoryID = P.CategoryID"
'Update and refresh the query table
With wksData.QueryTables(1)
.Connection = sConnect
.CommandText = sSQL
.Refresh
End With
End If
End Sub

As well as removing the hard-coded paths to the database file, handling the refresh through VBA also provides the ability to include parameters in the query, such as only retrieving the data for a specific country where the country name could be obtained from worksheet cells.

When creating a query table using Excel's UI, the result is a table that uses ODBC to connect to the database, rather than the ADO connections that we covered in Chapter 13 Programming with Databases. We can easily switch to using an ADO connection if we prefer, by adding the OLEDB; prefix to the ADO connection string.

Even though we end up with very similar code to connect to the database and run the query, using query tables is preferable to populating the worksheet from an ADO recordset, as the query table automatically handles whether to insert new rows for extra data and whether to copy any formulas from adjacent columns.