Office Object Model: Excel

List object types

There are five ListObject types in Excel which are identified by the xlListObjectSourceType enum:

xlSrcExternal - External data source
xlSrcModel - PowerPivot Model
xlSrcQuery
xlSrcRange
xlSrcXml

xlSrcXml allows to access data from Excel, CSV, SharePoint etc. Some VBA examples that try to demonstrate this include

Creating a data table

The following snippet creates an Excel table with Visual Basic for Applications.

option explicit

sub main() ' {

  '
  ' Create some t est data
  '
    testData

  '
  ' Create a data table from the test data:
  '
    dim dataTable as listObject
    set dataTable = activeSheet.listObjects.add(xlSrcRange, range(cells(3,4), cells(12,6)))

    dataTable.name = "datTbl"

  '
  ' Add a totals row at the bottom of the table:
  '
    dataTable.showTotals = true

  '
  ' Show maximum number of second column (column name of which
  ' is colTwo)
  '
    dataTable.listColumns("colTwo").totalsCalculation = xlTotalsCalculationMax


end sub ' }

sub testData() ' {

    cells( 3, 4) = "colOne" : cells( 3, 5) = "colTwo" : cells( 3, 6) = "colThree"
    cells( 4, 4) = "bar"    : cells( 4, 5) =      15  : cells( 4, 6) =        34
    cells( 5, 4) = "foo"    : cells( 5, 5) =      21  : cells( 5, 6) =        30
    cells( 6, 4) = "baz"    : cells( 6, 5) =      20  : cells( 6, 6) =        35
    cells( 7, 4) = "bar"    : cells( 7, 5) =      18  : cells( 7, 6) =        29
    cells( 8, 4) = "foo"    : cells( 8, 5) =      16  : cells( 8, 6) =        31
    cells( 9, 4) = "foo"    : cells( 9, 5) =      21  : cells( 9, 6) =        36
    cells(10, 4) = "bar"    : cells(10, 5) =      18  : cells(10, 6) =        34
    cells(11, 4) = "baz"    : cells(11, 5) =      19  : cells(11, 6) =        32
    cells(12, 4) = "foo"    : cells(12, 5) =      17  : cells(12, 6) =        31

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/ListObject/data-table.bas

When running the snippet, it produces:

queryTable

The queryTable property (whose data type also is queryTable) provides a listObject's link to the list server.

TODO

1:1 relationship to a queryTable

It seems that there is a 1:1 relationship between a listObject and a queryTable: the list object has the property .queryTable and the query table has the property .listObject which, at least in my tests point to one another.

In fact, after createing a list object with sheet.listobjects.add(), the newly created list object already has a query table object.

Selecting from MS-access tables with guids

As far as I can tell, it is not possible to select guid-columns from MS Access tables.

VBA Examples

Adding rows to a list object

Excel ListObject: connectionString property to select data from another Excel worksheet

Reading a CSV file into an Excel table (listObject) with Visual Basic for Application

Using an autoFilter on a listObject.

Displaying the result of a Power Query program.

Office Object Model: Excel - ListObject