How to access workbooks from the ExcelApplication Delphi COM reference, currently there is no any?

Question

My Delphi application has to check whether there are any open Excel application or open Excel workbook on the user's desktop and issue some warning or even close them.

I am using COM/OLE/ActiveX to retrieve the list of active ExcelApplication instances and then read the workbooks on each of them. I have come up with the following code:

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComObj, Excel_TLB, ActiveX;

procedure TMainForm.MonikerBtnClick(Sender: TObject);
var
  ROT: IRunningObjectTable;
  Enum: IEnumMoniker;
  Moniker: IMoniker;
  BindCtx: IBindCtx;
  DisplayName: WideString;
  PDisplayName: POleStr;
  FApp: ExcelApplication;
  FWorkbooks: Workbooks;
  FWorkbook: _Workbook;
  i: Integer;
  ExcelAppGUID: string;
  TmpName: String;
  Obj: IUnknown;
begin
  ExcelAppGUID:=GUIDToString(ProgIDToClassID('Excel.Application'));

  // Get a reference to the Running Object Table
  OleCheck(GetRunningObjectTable(0, ROT));

  // Create an enumerator for the monikers in the ROT
  OleCheck(ROT.EnumRunning(Enum));

  // Create a bind context to use for moniker binding
  OleCheck(CreateBindCtx(0, BindCtx));

  // Iterate over all monikers in the ROT
  while Enum.Next(1, Moniker, nil) = S_OK do
  begin
    // Get the display name of the moniker
    OleCheck(Moniker.GetDisplayName(BindCtx, nil, PDisplayName));
    DisplayName:=PDisplayName;
    
    // Check if the moniker belongs to an instance of Excel
    if Pos(UpperCase(ExcelAppGUID), UpperCase(DisplayName)) > 0 then
    begin
      OleCheck(ROT.GetObject(Moniker, Obj));
      FApp:=(Obj as ExcelApplication);
      TmpName:=FApp.Name;

      FWorkbook:=FApp.ActiveWorkbook;
      TmpName:=FWorkbook.Name;

      // Access the workbooks in the instance of Excel
      FWorkbooks := FApp.Workbooks;
      for i := 1 to FWorkbooks.Count do
      begin
        FWorkbook := FWorkbooks[i];
        TmpName:=FWorkbook.Name;
      end;
    end;
    Moniker := nil;
  end;
end;

This code is Delphi 6 (not to be confused with Delphi 2006, this is millenium edition :) ), and I am running it on Windows 10, Excel 2016.

Today is Mondey. I have opened 2 unsaved Excel workbooks (Book1, Book2) and 1 saved Excel file (some_name.xlsx) on Friday, I had weekend and now I am back at my computer and I am trying to access those 3 Excel application instances / 3 workbooks from my Delphi code. Task Manager shows 3 Excel processes (actually 1 Excel proces with 3 subprocesses), the Delphi applications finds 2 Excel application instances, but both of then have Workbook - nil. So - I can access Excel applications from my Delphi code but those applications have no workbooks (both ActiveWorkbook is nil and Woorkbooks.Count is 0).

Why is that? I clearly see that there are open workbooks? How should I modify my code to access all 3 workbooks?

Additional information. Actually this code is bad. Because it retrieves 2 Monikers that represents ExcelApplications (I am guessing that one moniker represents both open and unshaved workbooks and another moniker represents the saved and opened file/workbook), but actually both Obj/FApp refers to the one instance.

E.g. I introduced this modification:

 FWorkbooks := FApp.Workbooks;
 FWorkbooks.Add(Null, LCID);

and now the codes starts to show FWorkbooks.Count>0 and names Book3, Book4, etc. But both Obj/FApp instances retrieves the same names of the books and both instances does not see the actual visible workbooks to whom I am trying to connect.

I have tried to use the code which (I guess) connects to all the active instances truly (ROT.GetObject may retrive the firt or the active Excel instance irrespective of the Moniker):

  //OleCheck(Moniker.BindToObject(BindCtx, nil, IDispatch, FApp));
  //OleCheck(Moniker.BindToObject(BindCtx, nil, CLASS_ExcelApplication, FApp));
  //OleCheck(Moniker.BindToObject(BindCtx, nil, IID_IDispatch, FApp));
  //OleCheck(Moniker.BindToObject(BindCtx, nil, IID_IDispatch, IDisp));
  //OleCheck(Moniker.BindToObject(BindCtx, nil, CLASS_ExcelApplication, FApp));

  Moniker.BindToObject(BindCtx, nil, IID_IDispatch, IDisp);

But this code always retruned Fapp and IDisp nil in any case, irrespective of the IID and the type of reference (as a 4th argument). So, I abandoned this approach, opted for ROT.GetActiveObject but without the complete result.

Answer 1

To solve my question I tried to get IDispatch from the Moniker with code:

Moniker.BindToObject(BindCtx, nil, IID_IDispatch, IDisp);

Then I tried to get the type information (described in How to get all the interfaces which are supported by the Delphi instance which is accessible from IDispatch?) from IDisp with code:

  IDisp: IDispatch;
  Report: TStringList;
  TypeInfo: ITypeInfo;
  TypeAttr: PTypeAttr;
  ii: Integer;
  RefTypeInfoCard: Cardinal;
  RefTypeInfo: ITypeInfo;
  RefTypeInfoAttr: PTypeAttr;
  GUIDString: String;
begin

  //...
  if Succeeded(IDisp.GetTypeInfo(0, 0, TypeInfo)) then begin
        // Get the TYPEATTR structure for the interface
        if Succeeded(TypeInfo.GetTypeAttr(TypeAttr)) then
        begin
          try
            GUIDString:=GUIDToString(TypeAttr.guid);
            // Loop through all of the supported interfaces and print their GUIDs
            for ii := 0 to TypeAttr.cImplTypes - 1 do
            begin
              if Succeeded(TypeInfo.GetRefTypeOfImplType(ii, RefTypeInfoCard)) then
              begin
                if Succeeded(TypeInfo.GetRefTypeInfo(RefTypeInfoCard, RefTypeInfo)) then
                begin
                  if Succeeded(RefTypeInfo.GetTypeAttr(RefTypeInfoAttr)) then
                  begin
                    Report.Add('     '+GUIDToString(RefTypeInfoAttr.guid));
                  end;
                end;
              end;
            end;
          finally
            TypeInfo.ReleaseTypeAttr(TypeAttr);
          end;
        end;
      end; 
end;

And in the case of GUIDString:

Excel_TLB
IID__Workbook: TGUID = '{000208DA-0000-0000-C000-000000000046}';

I could try to get the Excel Workbook interface:

  IIWB: _Workbook;
begin
  //...
  IIWB:=nil;
  try
    Moniker.BindToObject(BindCtx, nil, IID__Workbook, IIWB);
    if IIWB=nil then begin
      //...
    end else begin
      //Do something important with Workbook, like IIWB.Close etc.
    end;
  except
    on E: Exception do begin
      //just in case...
    end;
  end;
  //...
end; 

And, of course, I succeeded getting it. I could anything necessary on this interface.

So - I have not managed to get ExcelApplication instance with accessible workbooks from Monikers, but I could get _Woorkbook interfaces from distinct/separate Monikers and act on those workbook interfaces. It is necessary to understand that there can be multiple active Monikers for Excel instance and workbooks and the can be separate monikokers for Excel application and for the workbooks of those applications.