ListObject Object

Represents a list object on a worksheet.

Several ways to update a shared list
1) Refreshing - discards local changes and updates with the data from the server
2) Synchronising - updates both the worksheet list and the server any conflicts can be resolved by the user who is synchronising

objListObject.Refresh

objListObject.UpdateChanges( xlListConflictDialog.xlListConflictError

If the worksheet list is not shated then this method will cause an error.

ListObject(1).Unlist

Creating a List

creates a list around the active cell.

ActiveWorkbook.ListObjects.Add SourceType:=xlScrRange.xlSrcExternal, _ 
                      Range("A2:C5"), , xlNo

When SourceType is xlScrExternal, the source argument is a two element array containing the following:
1) The sharepoint list address plus the folder name
2) The name (or GUID) of the list

It is a good idea to create a new workbook when inserting a shared list manually.

SharePoint Lists Web Service to access the list directly though code.

ListObject Collection

The ListObjects collection contains all the list objects on a worksheet.
The ListObjects property can be used to return a read-only collection of ListObject objects in the worksheet.
Use the ListObjects property of the Worksheet can be used to return a ListObjects collection of all the ListObjects on that worksheet.

Dim objWorksheet As Worksheet 
Dim objListObject As ListObject 

Set objWorksheet = ActiveWorkbook.Worksheets("Sheet1") 
   
If objWorksheet.ListObjects.Count > 0 Then 
   Set objListObject = objWorksheet.ListObjects(1) 
End If

Returns a Range object that represents the range that contains the data area in the list between the header row and the insert row. Read-only.

objListObject.DataBodyRange

True if the specified window, worksheet, or ListObject is displayed from right to left instead of from left to right
False if the object is displayed from left to right. Read-only Boolean

objListObject.DisplayRightToLeft

Returns a Range object that represents the range of the header row for a list. Read-only Range.

objListObject.HeaderRowRange

Returns a ListColumns collection that represents all the columns in a ListObject object. Read-only.

objListObject.ListColumns

Returns a ListRows object that represents all the rows of data in the ListObject object. Read-only.

objListObject.ListRows

Returns or sets the name of the ListObject object.
This name is used solely as a unique identifier for the Item property of the ListObjects collection objects.
This property can only be set through the object model. Read/write String.
By default, each ListObject object name begins with the word "List", followed by a number (no spaces). If an attempt is made to set the Name property to a name already used by another ListObject object, a run-time error is thrown.

objListObject.Name

Returns the QueryTable object that provides a link for the ListObject object to the list server. Read-only.

objListObject.QueryTable

Returns a Range object that represents the range to which the specified list object in the above list applies. Read-Only.

objListObject.Range

Retrieves the current data and schema for the list from the server that is running Microsoft Windows SharePoint Services
This method can be used only with lists that are linked to a SharePoint site.
If the SharePoint site is not available, calling this method will return an error.
Calling the Refresh method does not commit changes to the list in the Excel workbook. Uncommitted changes in the list in Excel are discarded when the Refresh method is called.
To avoid losing any uncommitted changes, call the UpdateChanges method of the ListObject object before calling the Refresh method.

objListObject.Refresh

Resizes the specified range. Returns a Range object that represents the resized range.

objListObject.Resize

Returns a String representing the URL of the SharePoint list for a given ListObject object. Read-only String.
Accessing this property generates a run-time error if the list is not linked to a SharePoint site.

objListObject.SharePointURL

Returns Boolean to indicate whether the AutoFilter will be displayed. Read/write Boolean.
ShowAutoFilter property defaults to True for a new ListObject object.

objListObject.ShowAutoFilter

Gets or sets a Boolean to indicate whether the Total row is visible. Read/write Boolean.

objListObject.ShowTotals

Returns a one of the XlListObjectSourceType constants indicating the current source of the list. Read-only.

objListObject.SourceType

Returns a Range representing the Total row, if any, from a specified ListObject object. Read-only.

objListObject.TotalsRowRange

Updates the list on a Microsoft Windows SharePoint Services site with the changes made to the list in the worksheet. Returns Nothing.
This method applies only to lists linked to a SharePoint site. If the SharePoint site is not available, an error is generated.
Optional XlListConflict. Conflict resolution options.

objListObject.UpdateChanges XlListConflict.xlListConflictError

Returns an XmlMap object that represents the schema map used for the specified list. Read-only.
Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

objListObject.XmlMap

ListRow Object

Represents a row in a List object. The ListRow object is a member of the ListRows collection.
The ListRows collection contains all the rows in a list object.
Use the ListRows property of the ListObject object to return a ListRows Object collection.

Deletes the cells of the list row and shifts upward any remaining cells below the deleted row. You can delete rows in the list even when the list is linked to a SharePoint site.
The list on the SharePoint site will not be updated, however, until you synchronize your changes.

Dim objListRow As ListRow 

objListRow.Delete

ListColumn Object

Represents a column in a list.
The ListColumn object is a member of the ListColumns collection. The ListColumns collection contains all the columns in a list (ListObject object).
Use the ListColumns property of the ListObject object to return a ListColumns collection.

Returns or sets the name of the list column.
This is also used as the display name of the list column. This name must be unique within the list. Read/write String.
Note If this list is linked to a SharePoint list, this property is read-only.

objListColumn.Name

Returns a ListDataFormat object for the ListColumn object. Read-only.
Use the ListDataFormat property to return a ListDataFormat object.

objListColumn.ListDataFormat

Deletes the column of data in the list. Does not remove the column from the sheet.
If the list is linked to a Microsoft Windows SharePoint Services site, the column cannot be removed from the server, and an error is generated.

Dim objListColumn As ListColumn 

objListColumn.Delete

Returns a String representing the formula a calculated column.
The formula is expressed in Excel syntax (US English locale, A1 notation). Read-only String.
If the ListColumn object does not belong to a list that is linked to a SharePoint site or if it is not a column designated as a calculated column on the SharePoint site, you will get a run-time error.

objListColumn.SharePointFormula

Determines the type of calculation in the Totals row of the list column based on the value of the XlTotalsCalculation enumeration. Read/write.
The Totals row doesn't need to be showing in order to set this property.
There is no fixed "default" value for this property. Excel may change the state of this property, as other columns are added or deleted.

objListColumn.TotalsCalculation

Returns an XPath object that represents the Xpath of the element mapped to the specified Range object. Read-only.

objListColumn.XPath

Publishing Lists

Publishes the ListObject object to a server that is running Microsoft Windows SharePoint Services.
Returns a String which is the URL of the published list on the SharePoint site

objListObject.Publish(Array("HTTP://MyServer", "MyList", "Description of my list"), True)

Delete Method

Deletes the ListObject object and clears the cell data from the worksheet.
If the list is linked to a SharePoint site, deleting it does not affect data on the server that is running Windows SharePoint Services
Any uncommitted changes made to the local list are not sent to the SharePoint list.
There is no warning that these uncommitted changes are lost.

objListObject.Delete

Unlink Method

Removes the link between the worksheet and the SharePoint server.
Returns Nothing

objListObject.Unlink

To reestablish the link you must delete the list and create it again.

Unlist Method

Convert the list to a regular range preserving the data.
After you use this method, the range of cells that made up the the list will be a regular range of data. Returns Nothing
Removes the list functionality from a ListObject object.
Running this method leaves the cell data, formatting, and formulas in the worksheet. The Total row is also left intact
This method removes any link to a Microsoft Windows SharePoint Services site. AutoFilter and the Insert row are also removed from the list.

objListObject.Unlist

ActiveSheet.ListObjects("myTable").Range.Select 
ActiveSheet.ListObjects("myTable").DataBodyRange.Select 
ActiveSheet.ListObjects("myTable").DataBodyRange(2, 4).value 

ActiveSheet.ListObjects("myTable").ListColumns.Add 
ActiveSheet.ListObjects("myTable").ListColumns.Add Position:=2 

ActiveSheet.ListObjects("myTable").ListRows.Add 
ActiveSheet.ListObjects("myTable").ListRows.Add Position:=1 

ActiveSheet.ListObjects("myTable").ListColumns(2).Range.Select 
ActiveSheet.ListObjects("myTable").ListColumns("Category").Range.Select 

ActiveSheet.ListObjects("myTable").ListColumns(4).DataBodyRange.Select 
ActiveSheet.ListObjects("myTable").ListColumns("Category").DataBodyRange.Select 

ActiveSheet.ListObjects("myTable").ListColumns.Count 

ActiveSheet.ListObjects("myTable").ListColumns("Feb").Delete 
ActiveSheet.ListObjects("myTable").Range.Rows("4:6").Delete 

ActiveSheet.ListObjects("myTable").HeaderRowRange(5).Select 
ActiveSheet.ListObjects("myTable").TotalsRowRange(3).Select 
ActiveSheet.ListObjects("myTable").ListRows(3).Range.Select 
ActiveSheet.ListObjects("myTable").HeaderRowRange.Select 
ActiveSheet.ListObjects("myTable").HeaderRowRange.Select

'when converting a table back to a standard range, the formatting is not removed
ActiveSheet.ListObjects("myTable").Unlist 

ActiveSheet.ListObjects("myTable").Resize Range("$A$1:$J$100") 

ActiveSheet.ListObjects("myTable").TableStyle = "TableStyleLight15" 

ActiveSheet.ListObjects("myTable").ShowTableStyleFirstColumn = True 
ActiveSheet.ListObjects("myTable").ShowTableStyleLastColumn = True 

ActiveSheet.ListObjects("myTable").ShowTableStyleColumnStripes = True 
ActiveSheet.ListObjects("myTable").ShowTableStyleRowStripes = False 

ActiveSheet.ListObjects("myTable").ShowHeaders = False 
ActiveSheet.ListObjects("myTable").ShowAutoFilterDropDown = False 

ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2" 

ActiveSheet.Range("myTable[Category]").Select 

Dim tableName As String 
Dim tableRange As Range 

Set tableName = "myTable" 
Set tableRange = Selection.CurrentRegion 
ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, _ 
    Source:=tableRange, _ 
    xlListObjectHasHeaders:=xlYes _ 
    ).Name = tableName 

ActiveSheet.ListObjects("myTable").ShowTotals = True 
ActiveSheet.ListObjects("myTable").ListColumns("TotalColumn").TotalsCalculation = xlTotalsCalculationAverage 

Dim myArray As Variant 
myArray = Range("A2:D2") 

'Assign values in array to the table
ActiveSheet.ListObjects("myTable").ListRows(2).Range.Value = myArray 


Sub InATable() 
   Dim ActiveTable As ListObject 

   On Error Resume Next 
   Set ActiveTable = ActiveCell.ListObject 
   On Error GoTo 0 

   'Confirm if a cell is in a Table
   If ActiveTable Is Nothing Then 
       MsgBox "Select table and try again" 
   Else 
       MsgBox "The active cell is in a Table called: " & ActiveTable.Name 
   End If 
End Sub