ListObjects.Add Method (Excel)
Creates a new list object.
Syntax
expression .Add(SourceType, Source, LinkSource, XlListObjectHasHeaders, Destination, TableStyleName)
expression A variable that represents a ListObjects object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
SourceType |
Optional |
Indicates the kind of source for the query. |
|
Source |
Optional |
Variant |
If SourceType = xlSrcRange: A Range object that represents the data source. If SourceType is omitted, Source defaults to the range returned by list range detection code. If SourceType = xlSrcExternal: An array of String values that specifies a connection to the source, containing the following elements in the specified array positions:
|
LinkSource |
Optional |
Variant |
Boolean. Indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, the default is True. Invalid if SourceType is xlSrcRange, and returns an error if not omitted. |
XlListObjectHasHeaders |
Optional |
An XlYesNoGuess constant that indicates whether the data being imported has column labels. If Source does not contain headers, Excel automatically generates headers. |
|
Destination |
Optional |
Variant |
A Range object that specifies a single-cell reference as the destination for the top-left corner of the new list object. If the Range object refers to more than one cell, an error is generated. If SourceType is set to xlSrcExternal, Destination must be specified. If SourceType is set to xlSrcRange, Destination is ignored. The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns are inserted at the Destination to fit the new list. Therefore, existing data is not overwritten. |
TableStyleName |
Optional |
Variant |
A String that represents the name of the pivot table style to apply to the new list object. |
Return Value
A ListObject object that represents the new list object.
Remarks
When the list has headers, the first row of cells is converted to Text, if it is not already set to text. The conversion is based on the visible text for the cell. This means that if there is a date value that has a Date format that changes with locale, the conversion to a list might produce different results depending on the current system locale. Moreover, if there are two cells in the header row that have the same visible text, an incremental Integer is appended to make each column header unique.
Example
The following example adds a new ListObject object based on data from a Microsoft SharePoint site to the default ListObjects collection and places the list in cell A1 in the first worksheet of the workbook.
Note
In the following code example, substitute a valid server name and the list GUID for the variables strServerName and strListGUID. Additionally, the server name must be followed by "/_vti_bin", or the sample will not work.
Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, _
Source:= Array(strServerName, strListName, strListGUID), LinkSource:=True, _
TableStyleName:=xlGuess, Destination:=Range("A10"))