Excel's ListObject (table)

Add-in Express™ Support Service
That's what is more important than anything else

Excel's ListObject (table)
Each ListObject object represents a table in the worksheet 
chrzastek




Posts: 7
Joined: 2019-03-05
Hi Add-In Express Team and all interested,

I got a great help from Add-In Express Team (Dmitry Kostochko) on named ranges. Going further I try to access Excel's ListObjects.
According to Microsoft Office Dev Center:
ListObjects is collection of all the ListObject objects on a worksheet. ListObject object represents a table in the worksheet. ListObject object is a member of the ListObjects collection. The ListObjects collection contains all the list objects on a worksheet.

Visual Basic:
Set myWorksheetLists = Worksheets(1).ListObjects


Please advise how to access this object via Add-In Express VCL?

Regards,
Arkadiusz
Posted 07 Mar, 2019 03:55:42 Top
Dmitry Kostochko


Add-in Express team


Posts: 2811
Joined: 2004-04-05
Hello Arkadiusz,

The ListObjects collection was introduced in the Excel 2003 Object Model. Since we are using Excel 2000 class wrappers (Excel2000.pas in the uses clause), you cannot access this collection via early binding, i.e. via properties of the Excel200._Worksheet interface.

I would suggest using late binding, i.e. OleVariant. Please have a look at the code below:

var
  activeSheet: _Worksheet;
begin
  activeSheet := ExcelApp.ActiveSheet as _Worksheet;
  if Assigned(activeSheet) then begin
    // check Excel version!
    if Self.HostMajorVersion >= 11 then begin
      // 9  = Office 2000
      // 10 = Office XP
      // 11 = Office 2003
      // 12 = Office 2007
      // 14 = Office 2010
      // 15 = Office 2013
      // 16 = Office 2016 & 2019

      ShowMessage(OleVariant(activeSheet).ListObjects.Count);
    end;
  end;
end;


In general, you type cast an interface to OleVariant and then access needed objects, properties, methods.
Posted 07 Mar, 2019 05:25:27 Top
Arkadiusz Chrzastek




Posts: 7
Joined: 2019-03-05
Hello Dmitry,

Your last sentence
"type cast an interface to OleVariant and then access needed objects, properties, methods"

is big eye opener and will be great help for me. So now I have this working function to count ListObjects in Excel Workbook:

function GetListObjectsCount : Integer;
var
  i, j : integer;
begin
  if (HostMajorVersion >= 11) then
  begin
    result := 0;
    for i := 1 to excel_app.ActiveWorkbook.Worksheets.Count do
      if OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects.Count > 0 then
      begin
        result := result + OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects.Count;
        for j := 1 to OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects.Count do
          ShowMessage(OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects[j].Name);
      end;
  end
  else
    result := -1;
end;


My next step was obvious function GetListObjectByName:

// this function is outside of unit where TAddInModule is, so...
// excel_app = ExcelApp, HostMajorVersion = adxAddIn.HostMajorVersion
function GetListObjectByName(lo_name : string) : OleVariant;
var
  i, j : integer;
begin
  result := Null;
  if (HostMajorVersion >= 11) then
  begin
    result := 0;
    for i := 1 to excel_app.ActiveWorkbook.Worksheets.Count do
      if OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects.Count > 0 then
      begin
        for j := 1 to OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects.Count do
          if String(OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects[j].Name) = lo_name then
          begin
            result := OleVariant(OleVariant(excel_app.ActiveWorkbook.Worksheets.Item[i]).ListObjects[j]);
          end;
      end;
  end;
end;

// this works but is not safe if specified table does not exist
procedure TAddInModule.TestRibbonButtonClick(Sender: TObject; const RibbonControl: IRibbonControl);
begin
  ShowMessage(GetListObjectByName('Table1').Name);
end;

// this is safe when specified table does not exist, but does not work :(
procedure TAddInModule.TestRibbonButtonClick(Sender: TObject; const RibbonControl: IRibbonControl);
var
  list_object : OleVariant;
begin
  list_object:= GetListObjectByName('Table1');
  if list_object<> Null then
    ShowMessage(list_object.Name)
  else
    ShowMessage('not found');}
end;


Assigning OleVariant to variable "list_object" does not work. Please advise.

Best regards,
Arkadiusz
Posted 08 Mar, 2019 04:16:00 Top
Dmitry Kostochko


Add-in Express team


Posts: 2811
Joined: 2004-04-05
Hello Arkadiusz,

Thank you for the code samples, they were very helpful. Firstly, please use Unassigned instead of Null (or 0) to initialize OleVariant:

function GetListObjectByName(lo_name : string) : OleVariant; 
var 
  i, j : integer; 
begin 
  // result := Null; 
  result := Unassigned;
  if (HostMajorVersion >= 11) then 
  begin 
    // result := 0;
// ...


Then, you can use the VarIsClear function to check OleVariant:

var 
  list_object : OleVariant; 
begin 
  list_object:= GetListObjectByName('Table1'); 
  // if list_object<> Null then 
  if (Not VarIsClear(list_object)) then
    ShowMessage(list_object.Name) 
  else 
    ShowMessage('not found');} 
end;


Also, you can access a ListObject object not only by index but also by name, for example:

var
  v: OleVariant;
begin
  // ...
  try
    v := OleVariant(ExcelApp.ActiveWorkbook.Worksheets.Item[1]).ListObjects['Table1'];
  except
    v := Unassigned;
  end;
  if VarIsClear(v) then
    ShowMessage('not found');
  // ...


Note, the try/except block is required because Excel throws an exception if ListObject with the requested name does not exist.
Posted 11 Mar, 2019 04:56:38 Top
Arkadiusz Chrzastek




Posts: 7
Joined: 2019-03-05
Dear Dmitry,

You are Jedi master of programming!
Thank you again for explaining me the concept of late binding.

I have now such set of working functions:

function GetListObjectByName(lo_name : string) : OleVariant;
function GetListObjectsCount : Integer;
function ListObjectExists(lo_name : string) : boolean;
function CreateNewListObject(ws_name, lo_name, lo_range : string) : OleVariant;

// THIS IS WORKING !!!

// I had to define it myself
TListObjectSourceType = (xlSrcExternal, xlSrcRange, xlSrcXml, xlSrcQuery, xlSrcModel);

function CreateNewListObject(ws_name, lo_name, lo_range : string) : OleVariant;
var
  ws : Excel2000._Worksheet;
  r : Excel2000.ExcelRange;
begin
  try
    ws := GetWorksheetByName(ws_name);
    r := ws.Range[lo_range, EmptyParam].Cells;
    result := OleVariant(ws).ListObjects.Add(xlSrcRange, r, EmptyParam, xlNo, EmptyParam, EmptyParam);
    result.Name := lo_name;
  except
    result := Unassigned;
  end;
end;


// AND THIS AMAZING PROCEDURE IS ALSO WORKING !!!

// predefined column names
const
  TABLE_COLUMN_NAMES : array[0..4] of string = ('id', 'table', 'ref_id', 'active', 'project_id');

procedure InitializeTable;
var
  i : integer;
  lo : OleVariant;
begin
  if Assigned(GetWorksheetByName('Sheet1')) then
    if not ListObjectExists('Table1') then
    begin
      lo := CreateNewListObject('Sheet1', 'Table1', '$A$1:$E$2');
      for i := 1 to lo.ListColumns.Count do
        OleVariant(lo.ListColumns[i]).Name := TABLE_COLUMN_NAMES[i - 1];
    end;
end;


Grateful regards,
Arkadiusz
Posted 12 Mar, 2019 08:07:59 Top
Dmitry Kostochko


Add-in Express team


Posts: 2811
Joined: 2004-04-05
Hi Arkadiusz,

Thank you for the good news and kind words!
If you have any questions or face problems, feel free to contact us.
Posted 12 Mar, 2019 10:02:27 Top