1—Finds items in the same month as the following paired date/time value.
2—Finds items on the same date as the following paired date/time value.
3—Finds items in the same hour as the following paired date/time value.
4—Finds items in the same minute as the following paired date/time value.
5—Finds items in the same second as the following paired date/time value.
Any number of search type and date/time value pairs can be passed. All search type values must be in odd positions in the array (1, 3, 5, etc.), and all date/time values must be in even positions (2, 4, 6, etc.). Any value passed for a search type that is not in the above list generates an error. Any value passed for a date/time value that is not a date/time value passed as a string generates an error. Any date/time value that can be entered in a cell and recognized as a date is valid. Below are some examples of Criteria2 values.
Show all items in the year 2014:Array(0, "1/1/2014")
Show all items in the year 2014 when the current year is 2014:Array(0, "1/1")
Show all items in the month January 2014:Array(1, "1/1/2014")
Show all items on the day January 15, 2014:Array(2, "1/15/2014")
Show all items on the days January 15, 20, and 25, 2014:Array(2, "1/15/2014", 2, "1/20/2014", 2, "1/25/2014")
Show all items in the year 2013 and in the month January 2014: Array(0, "1/1/2013", 1, "1/1/2014")
Show all items in the same hour as 3 PM on January 15, 2014:Array(3, "1/15/2014 15:0")
Array(3, "1/15/2014 3 PM")
Show all items in the same minute as 3:01 PM on January 15, 2014:Array(3, "1/15/2014 15:1")
Array(3, "1/15/2014 3:01 PM")
xlOr or 2—Logical OR of Criteria1 or Criteria2. Both Criteria1 and Criteria2 are strings that specify a condition, such as "<0" and ">100" for less than zero or greater than 100.
xlTop10Items or 3—Finds the highest-valued items displayed, where the number of items is specified as a number or string in Criteria1, such as 5 or "5", meaning the five largest items. The column specified by Field must contain at least one number, or an error occurs.
xlTop10Percent or 5—Finds the highest-valued items displayed, where the percentage is specified as a number or string in Criteria1, such as 20 or "20" for items in the top 20%. The column specified by "Field" must contain at least one number, or an error occurs.
The following are XlDynamicFilterCriteria constants:
xlFilterToday—Filters all date values equal to today.
xlFilterYesterday—Filters all date values equal to yesterday.
xlFilterTomorrow—Filters all date values equal to tomorrow.
xlFilterThisWeek—Filters all date values in the current week.
xlFilterLastWeek—Filters all date values in the last week.
xlFilterNextWeek—Filters all date values in the next week.
xlFilterThisMonth—Filters all date values in the current month.
xlFilterLastMonth—Filters all date values in the last month.
xlFilterNextMonth—Filters all date values in the next month.
xlFilterThisQuarter—Filters all date values in the current quarter.
xlFilterLastQuarter—Filters all date values in the last quarter.
xlFilterNextQuarter—Filters all date values in the next quarter.
xlFilterThisYear—Filters all date values in the current year.
xlFilterLastYear—Filters all date values in the last year.
xlFilterNextYear—Filters all values related to next year.
xlFilterYearToDate—Filters all date values from today until a year ago.
xlFilterAllDatesInPeriodQuarter1—Filters all date values in Quarter1.
xlFilterAllDatesInPeriodQuarter2—Filters all date values in Quarter2.
xlFilterAllDatesInPeriodQuarter3—Filters all date values in Quarter3.
xlFilterAllDatesInPeriodQuarter4—Filters all date values in Quarter4.
xlFilterAllDatesInPeriodJanuary—Filters all date values in January.
xlFilterAllDatesInPeriodFebruary—Filters all date values in February.
xlFilterAllDatesInPeriodMarch—Filters all date values in March.
xlFilterAllDatesInPeriodApril—Filters all date values in April.
xlFilterAllDatesInPeriodMay—Filters all date values in May.
xlFilterAllDatesInPeriodJune—Filters all date values in June.
xlFilterAllDatesInPeriodJuly—Filters all date values in July.
xlFilterAllDatesInPeriodAugust—Filters all date values in August.
xlFilterAllDatesInPeriodSeptember—Filters all date values in September.
xlFilterAllDatesInPeriodOctober—Filters all date values in October.
xlFilterAllDatesInPeriodNovember—Filters all date values in November.
xlFilterAllDatesInPeriodDecember—Filters all date values in December.
xlFilterAboveAverage—Filters all above-average values.
xlFilterBelowAverage—Filters all below-average values.
Accessing Table Parts
While the ListObject, ListColumns, and ListRows properties provide access to the main parts of a Table, there are other ways to access parts of a Table, using the same structured referencing syntax described in Chapter 4. These forms of referencing can be more convenient, depending on programming style and preferences. The examples below assume the following: a Table named "tblRegister" on a worksheet named "Register" with column names "Date", "Description", "Category", and "Amount".
A sample Table.
To use a structured reference, use the Range object to retrieve the range described by the reference. The Range object is a child object of many Excel objects, including the Application and Worksheet objects. When you use Range with the Application object, the reference must have global scope. When you use Range with the Worksheet (or Sheet) object, the reference can have global or local (Worksheet) scope.
NOTE
The scope of a name determines from where it can be accessed. A reference with global scope can be accessed from any object. A reference with local scope can be referenced only from the worksheet to which the specific reference refers.
Table names have global scope, which means you can access them from any code module without qualifying the reference with the Worksheet object in which the Table resides. For example, this reference:
ThisWorkbook.Worksheets("Register").Range("tblRegister[Date]")
Is equivalent to:
Application.Range("tblRegister[Date]")
Range("tblRegister[Date]")
[tblRegister[Date]]
To support this convention, Excel enforces the rule that every Table in a workbook must have a unique name. This rule governs all globally scoped names, not just names of Tables.
TIP
To reduce the probability of a name collision, an option is to preface all Table names with a common prefix—often referred to as “Hungarian notation” and described in Chapter 2—such as “tbl”.
CAUTION
Excel 2003 does not support structured references. There are also some differences between Excel 2007, 2010, and 2013 in terms of support for structured references. For more information, see the section “Forward and Backward Compatibility” in Chapter 2.
Creating and Naming a Table
You create Tables by using the ListObjects object's Add method. After you create a new Table, the ListObject object's DisplayName property is set to the new Table's name. The DisplayName property is used instead of the Name property because Excel is stricter about what can be assigned to the DisplayName property; instead of altering the name
when it is not valid, Excel generates an error, which produces more predictable results than when using the Name property.
The following sections provide examples of creating Tables with four different sources: xlSrcRange (a range of existing data), xlSrcExternal (external data source), xlSrcModel (Power Pivot Data Model), and xlSrcQuery (query). The source type xlSrcXml (XML source) is not covered, but workarounds are shown.
Using a Range of Existing Data (SourceType Set to xlSrcRange)
This example creates a new Table, using an existing range of data with headers. The SourceType parameter is set to xlSrcRange:
Dim TableRange As Range
Dim Table As ListObject
Set TableRange = ThisWorkbook.Worksheets("Register").Range("A1").CurrentRegion
Set Table = ThisWorkbook.Worksheets("Register").ListObjects.Add(xlSrcRange, TableRange, , xlYes)
Table.DisplayName = "tblRegister"
Note that the fourth parameter, xlYes, tells Excel that the list of data has existing headers. In this example, the Table is named as soon as it is created; this helps you locate the ListObject later.
Using a Power Pivot Data Model (SourceType Set to xlSrcModel)
This example uses a TableObject to create a connection to a SQL Server database. The SQL table "Product" is added to the Power Pivot Data Model. The Table is placed into the worksheet "Sheet1" at cell "A1". Because the interaction is with the Data Model, the TableObject must be used instead of a ListObject with xlSrcModel passed for SourceType. Change the text "YourServerName" to the name of the desired SQL server. The database used is AdventureWorks2012:
Dim SQLConnection As WorkbookConnection
Dim TargetWorksheet As Worksheet
Dim Table As TableObject
Dim ConnectionString As String
Set TargetWorksheet = ThisWorkbook.Worksheets("Sheet1")
ConnectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Initial Catalog=AdventureWorks2012;Data Source=YourServerName"
Set SQLConnection = ActiveWorkbook.Connections.Add2("FriendlyName", "Description", _
ConnectionString, "Product", 3, True)
With TargetWorksheet
Set Table = .ListObjects.Add(SourceType:=xlSrcModel, Source:=SQLConnection, _
Destination:=.Range("A1")).NewTable
End With
Table.ListObject.DisplayName = "tblNewTable"
The xlSrcModel constant was added in Excel 2013.
This next example assumes that the workbook already has a SQL Server connection with a table in the Power Pivot Data Model, and the objective is to pull the data out of the Data Model table into a new Excel Table. The source type is xlSrcModel and assumes that the Data Model table name is "Product". This example works only in Excel 2013:
Dim ModelSource As Model
Dim SourceTable As ModelTable
Dim TargetWorksheet As Worksheet
Dim Table As TableObject
Set TargetWorksheet = ThisWorkbook.Worksheets("Sheet1")
Set ModelSource = ThisWorkbook.Model
Set SourceTable = ModelSource.ModelTables("Product")
Set Table = TargetWorksheet.ListObjects.Add(SourceType:=xlSrcModel, _
Source:=SourceTable.SourceWorkbookConnection, _
LinkSource:=True, Destination:=DestinationSheet.Range("A1")).TableObject
Table.Refresh
Using an External Data Source or Query (SourceType Set to xlSrcExternal or xlSrcQuery)
This example uses a QueryTable object to create a connection to a SQL Server database. The Table "Product" is added to the worksheet "Sheet1" at cell "A1". Change the text "YourServerName" to the name of the desired SQL server. The database used is AdventureWorks2012:
Dim TargetWorksheet As Worksheet
Dim Table As QueryTable
Dim ConnectionString As String
Set TargetWorksheet = ThisWorkbook.Worksheets("Sheet1")
ConnectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Initial Catalog=AdventureWorks2012;Data Source=YourServerName"
Set Table = TargetWorksheet.ListObjects.Add(SourceType:=xlSrcExternal, Source:=ConnectionString, _
LinkSource:=True, Destination:=DestinationSheet.Range("A1")).QueryTable
Table.CommandText = Array("""AdventureWorks2012"".""Production"".""Product""")
Table.CommandType = xlCmdTable
Table.Refresh BackgroundQuery:=False
Table.ListObject.DisplayName = "tblNewTable"
The xlSrcQuery constant was added in Excel 2007.
This example uses the SourceType of xlSrcExternal, which is used for any external data connection. Passing xlSrcQuery for the SourceType parameter produces the same result. Typically, xlSrcQuery is used for database connections, and xlSrcExternal is used for SharePoint connections. There is a lot of latitude in data connections.
CAUTION
When using xlSrcExternal, you must specify the Destination parameter. When using a QueryTable object, you must set the CommandText and CommandType properties before refreshing the connection.
Using an XML Source (SourceType Set to xlSrcXml)
By design, the ListObjects object's Add method with the xlSrcXml source type is supposed to create a ListObject object by using an XML file as the source. However, this method is unreliable, and there are no known working examples of using it. Two methods are recommended for importing an XML source file into a Table. The first is to import an XML file into a new, blank workbook:
Workbooks.OpenXML Filename:="C:XML File Name.xml", LoadOption:=xlXmlLoadImportToList
The second is to import an XML file into the existing worksheet at the specified range:
ActiveWorkbook.XmlImport URL:="C:XML File Name.xml", ImportMap:=Nothing, Overwrite:=True, _
Destination:=Range("A1")
NOTE
In both of these examples, if the specified XML source doesn’t refer to a schema, Excel creates one based on what it finds in the referenced XML file.
Table Information
The following examples assume that DataBodyRange is a valid Range object. If there are no existing rows in the Table (that is, if ListRows.Count equals 0), any reference to DataBodyRange generates an error.
Determining Whether a Table Exists
Determining whether a Table exists is not a straightforward operation. It requires some error handling because an error occurs when a Table name that doesn't exist is used with the ListObjects collection. The following code illustrates how to use error handling to determine whether a Table exists:
Dim Table As ListObject
Set Table = Nothing
On Error Resume Next
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
On Error GoTo 0
If Table Is Nothing Then
Debug.Print "Table does not exist"
Else
Debug.Print "Table exists"
End If
TIP
Why set the object variable to Nothing before attempting to assign it a value? In the case above, it’s not necessary because VBA initializes every variable when it is defined with the Dim statement. But it’s included above as an example of writing robust code because, if an error occurs, the variable is not touched and, if it already contains a reference to another object, the following test will not produce the desired result.
Determining a Table's Address
This example displays a Table's address and the Table's DataBodyRange address in the Immediate window:
Dim Table As ListObject
Set Table
= ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Debug.Print "Table's address: " & Table.Range.Address
Debug.Print "Table's data body range address: " & Table.DataBodyRange.Address
Determining the Number of Rows
You determine the number of rows in a Table by using the ListRows object's Count property:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Debug.Print "Number of rows: " & Table.ListRows.Count
The Count property returns 0 if the Table is empty (that is, has one row ready for data entry and no data in any cells).
Determining the Number of Columns
You determine the number of columns in a Table by using the ListColumns object's Count property:
Dim Table As ListObject
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Debug.Print "Number of columns: " & Table.ListColumns.Count
Determining Whether a Column Exists
Determining whether a column exists is not a straightforward operation. It requires some error handling because an error occurs when a column name that doesn't exist is used with the ListColumns collection. The following code illustrates how to use error handling to determine whether a column exists:
Dim Table As ListObject
Dim ListColumn As ListColumn
Set Table = ThisWorkbook.Worksheets("Register").ListObjects("tblRegister")
Set ListColumn = Nothing
On Error Resume Next
Set ListColumn = Table.ListColumns("Description")
On Error GoTo 0
If ListColumn Is Nothing Then
Debug.Print "Column does not exist"
Else
Debug.Print "Column exists"
End If
Excel Tables Page 13