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 XlListObjectSourceType Indicates the kind of source for the query.
Source Optional Variant When SourceType = xlSrcRange: A Range object representing the data source. If omitted, the Source will default to the range returned by list range detection code.

When SourceType = xlSrcExternal: An array of String values specifying a connection to the source, containing the following elements:
  • 0 - URL to SharePoint site
  • 1 - ListName
  • 2 - ViewGUID
When SourceType = xlSrcQuery: Accepts ODBC or OLEDB connection string (this also works with xlSrcExternal).

When SourceType = xlSrcModel: Accepts a WorkbookConnection object (see TableObject for example).
LinkSource Optional 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 will return an error if not omitted.
XlListObjectHasHeaders Optional Variant An XlYesNoGuess constant that indicates whether the data being imported has column labels. If the Source does not contain headers, Excel will automatically generate headers. Default value: xlGuess.
Destination Optional Variant A Range object specifying 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.

The Destination argument must be specified when SourceType is set to xlSrcExternal. The Destination argument is ignored if SourceType is set to xlSrcRange.

The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns will be inserted at the Destination to fit the new list. Therefore, existing data will not be overwritten.
TableStyleName Optional String The name of a TableStyle; for example "TableStyleLight1".

Return value

A ListObject object that represents the new list object.

Remarks

When the list has headers, the first row of cells will be converted to Text, if not already set to text. The conversion will be based on the visible text for the cell. This means that if there is a date value with 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 will be appended to make each column header unique.

Example

The following example adds a new ListObject object based on data from a Microsoft SharePoint Foundation site to the default ListObjects collection and places the list in cell A1 in the first worksheet of the workbook.

Note

The following code example assumes that you'll substitute a valid server name and the list guid in the variables strServerName and strListGUID. Additionally, the server name must be followed by "/_vti_bin" (strListName) or the sample will not work.

Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, _ 
Source:= Array(strServerName, strListName, strListGUID), LinkSource:=True, _ 
XlListObjectHasHeaders:=xlGuess, Destination:=Range("A1")), 
TableStyleName:=xlGuess, Destination:=Range("A10")) 

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.