Introducing Data Lists

---

In Microsoft Excel Version 2002 and earlier versions, you needed to create a data list to use certain functions such as sorting and filtering data or creating a PivotTable. Though the concept of a data list was only loosely defined in the help system, the bottom line was that you needed to have a series of columns, with headers, and a series of rows with values for the columns. Figure 26-1 shows a data list of suppliers derived from the Suppliers table in the Northwind sample database (included with Microsoft Access).

Figure 26-1: A data list has column headers and rows of data, with each row representing a complete set of column values.

One of the advances of Microsoft Office Excel 2003 is that the concept of data lists has been codified into a coherent whole, implementing the new vision in a manner very like an Access table or a data form (not to be confused with a user form).

To create a data list using the Excel interface, follow these steps:

1. Type the names in the cells that will become the top row of the list.

2. Click Data, List, Create List to display the Create List dialog box (shown in Figure 26-2).

   
   Figure 26-2: The Create List dialog box lets you determine the structure of your data list.

3. Select the cells to be part of the list.

4. Select the My List Has Headers check box if the top row you selected contains the column headings.

5. Click OK.

After you've created your list, you can begin typing data into the insert row, where the left- most cell contains an asterisk. Pressing the Tab key moves you to the next column, whereas pressing the Enter key creates a new row in the list. Once you have data in the list, you can use the down arrow buttons in the first row (usually the header row) to filter the values in your list as if you had created an Advanced Filter. You can add a total row to the list by clicking any cell in the list and then clicking Data, List, Total Row. Clicking a cell in the total row displays a down arrow button that you can click to select the function you want to use in each total cell.

Note 

If you no longer want to use the list functionality but do want to retain the data in the list, click any cell in the list and then click Data, List, Convert to Range.

Creating Data Lists Programmatically

Individual data lists are represented in the Excel object model by the ListObject object, which has a variety of properties and methods that you can use to manipulate your lists using Microsoft Visual Basic for Applications (VBA). Table 26-1 summarizes the ListObject object's properties and methods.

Table 26-1: Properties and Methods of the Object

Property or Method	Description
Property
Active	This property returns a Boolean value that is True if the active cell is within the body of the ListObject.
DataBodyRange	This property returns a read-only Range object that refers to the list's cells between the header row and the insert row.
DisplayRightToLeft	This read-only property returns True if the worksheet, the list, or the window is displayed in a language that displays characters from right to left.
HeaderRowRange	This property returns a read-only Range object that refers to the cells in the header row of the ListObject.
InsertRowRange	This property returns a read-only Range object that refers to the cells in the insert row of the ListObject.
ListColumns	This property returns a ListColumns collection that contains all the columns in the ListObject.
ListRows	This property returns a ListRows collection that contains all the rows in the ListObject.
Name	A property used to identify the ListObject as a unique member of the ListObjects collection. The Name property can only be set and read using VBA; you can't affect it using the Excel interface.
Property
QueryTable	A property used to create a link to a table providing data to the ListObject.
Range	This property returns a read-only Range object that refers to all the cells in the ListObject.
SharePointUrl	This read-only property returns a string that contains the URL of the Microsoft SharePoint list for a given ListObject object.
ShowAutoFilter	A Boolean value that, when set to True, displays the results of the active AutoFilter on the contents of the ListObject. The default value is False.
ShowTotals	A Boolean value that, when set to True, displays a Totals row for the ListObject. The default value is False.
SourceType	A read-only constant that reflects the type of source providing data to the ListObject. The available data sources are xlSrcExternal (an external data source such as a Web page), xlSrcRange (a range of cells in an Excel workbook), and xlSrcXml (an XML data file).
TotalsRowRange	This property returns a read-only Range object that refers to the cells in the totals row of the ListObject.
XmlMap	This read-only property returns an XmlMap object representing the schema mapped to the specified list.
Method
Delete	This method deletes the active ListObject and deletes all cell data associated with it.
Publish(Target(Url, ListName, Description), LinkSource)	This method publishes a LinkObject to a SharePoint Services site. The Target argument contains an array of three values: the URL of the SharePoint server, the list name, and an optional description. The LinkSource argument is a required Boolean value that, when set to True, links the current LinkObject to the published LinkObject. Setting the LinkSource argument to False means the two objects won't be linked (and that any updates to the LinkObject that called the Publish method won't be reflected in the LinkObject on the SharePoint site).
Refresh	This method updates the data and schema for a ListObject that draws its data from a SharePoint site.
Method
Resize(Range)	This method lets you change the range of cells associated with the list. If the list draws its data from a source on a SharePoint site, you can only add rows, not columns. The method doesn't delete any existing data.
Unlink	This method breaks the link between the ListObject and a source on a SharePoint site. No data is deleted.
Unlist	This method removes data list functionality from the list. The cells in the ListObject become regular cells and retain their data.
UpdateChanges (XlListConflict)	This method updates the list on a SharePoint site with the changes made to the list in the worksheet. The optional XlListConflict constant can be any of these intrinsic constants: xlListConflictDialog (the default, which displays the Conflict dialog box), xlListConflictRetryAllConflicts (which attempts to update the data again), xlListConflictDiscardAllConflicts (which ignores any changes that conflict with other users' changes), and xlListConflictError (which indicates an error and halts the update).

All the data lists in a worksheet have a place in the Worksheet object's ListObjects collection. The collection starts out empty, but you can use the collection's Add method to create a new ListObject in the worksheet that calls the method.

The ListObjects.Add method has the following syntax:

expression.Add(SourceType, Source, LinkSource, HasHeaders, <;$RD>
   Destination).Name=namestring

Table 26-2 details the Add method's arguments.

Table 26-2: Arguments of the ListObjects Collection's Add Method

Argument	Required or Optional	Description
expression	Required	An expression that returns a ListObject object.
SourceType	Optional	One of two XlListObjectSourceType constants: xlSrcExternal, which indicates an external data source, or xlSrcRange (the default), which indicates the data source is a range in a workbook.
Source	Optional if SourceType is set to xlSrcRange; required if SourceType is set to xlSrcExternal	When SourceType is set to xlSrcRange, this argument refers to the range used to create the ListObject. When SourceType is set to xlSrcExternal, this argument must contain an array of three strings (a URL to a page on a SharePoint site, a list name, and a value representing the view to be applied to the list). There's no list of these values in the Visual Basic Editor Help system, so you should leave the third element of the array blank unless you are, or can get the correct values from, a SharePoint administrator.
LinkSource	Optional	A Boolean value that indicates whether to link the list to an external data source. The default value is True if the SourceType argument is set to xlSrcExternal. Setting the argument to either True or False when the SourceType argument is set to xlSrcRange creates an error.
HasHeaders	Optional	A variant value that indicates whether the ListObject has an existing set of column labels. The argument can be set to xlGuess, xlNo, or xlYes. If the source data doesn't contain column labels, or if Excel can't detect them, the method will create headers.
Destination	Required if the SourceType argument is set to xlSrcExternal; ignored if the SourceType argument is set to xlSrcRange	A Range object that identifies the cell at the top left corner of the new list object. The destination must be on the worksheet that called the Add method.
namestring	Required	A string that contains a unique name for the list.

Note 

The Add method inserts new columns to the right of the cell identified in the Destination argument to ensure that there's room for the new list; existing data won't be overwritten.

Inside Out
A Better Way to Create Lists

If you check the Visual Basic Editor Help file for the ListObjects collection's Add method, you won't see the .Name = namestring bit at the end. The example in the Help system requires you to create a new object and use a much longer statement to generate the same result. Also, if you use the Add method in the manner described in the Help system, you'll get error messages because the ListObject you create has no name. The Add statement in the example might seem a bit confusing, but you can think of it as a form of the following statement:

ListObjects(0).Name = namestring

You're creating the member of the ListObjects collection in the same statement where you name it, but all is well because the ListObject exists when the Visual Basic Editor encounters the Name method.

As an example of how to use the ListObjects collection's Add method, consider the worksheet shown in Figure 26-3, which has a series of cells that contain values to be used as column labels.

Figure 26-3: This worksheet has existing column labels and is ready for a list.

You could create a list based on that set of column labels using this procedure (which you would add to a code module):

Sub CreateListObject()
   ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$1"), , xlYes)<;$RD>
   .Name = "Suppliers1"
End Sub

Lists are a terrific addition to your battery of Excel tools, but they're even more powerful when you combine them with XML data. The next section of this chapter introduces the basics of the Extensible Markup Language and how Excel uses it to facilitate data handling.

---