chrzastek
Guest
|
Hi Add-In Express Team and all interested,
I got a great help from Add-In Express Team (Dmitry Kostochko) on https://www.add-in-express.com/forum/read.php?FID=1&TID=15352. Going further I try to access Excel's https://docs.microsoft.com/en-us/office/vba/api/excel.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 |
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
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. |
|
Arkadiusz Chrzastek
Guest
|
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 |
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
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. |
|
Arkadiusz Chrzastek
Guest
|
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 |
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
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. |
|