Using automation and Excel Application.Ready

Question

I'm trying to automat Excel from a Win32 Delphi-application. I have had to put in numereous Sleep() in my code so that Excel is (more likely) ready for the next automation command. Typically after opening a file, but also in many other cases. That is not ideal, of course, so I was looking for a way to check if Excel is ready.

I found the property Application.Ready, but it never ever returns false in my use-case. Maybe Application.Ready only return false if Excel is waiting for the user to handle av messagebox, and similiar?

Are there other ways to find out, using automation, if Excel is ready?

Code example:

procedure CopyFromExcelSheet;
var
  ExcelApp: OleVariant;
  WorkSheet: OleVariant;
begin
  ExcelApp := CreateOleObject('Excel.Application');
  ExcelApp.Visible := False;
  ExcelApp.Workbooks.Open(<Filename>);

  GiveExcelSomeTime(ExcelApp, 600);

  WorkSheet := ExcelApp.Workbooks[1].Sheets[1];
  WorkSheet.Activate;
  
  GiveExcelSomeTime(ExcelApp, 300);

  WorkSheet.UsedRange.Copy;
  etc.
end;



procedure GiveExcelSomeTime(AApp: OleVariant; ATime: Integer);
var
  LMillisec: Integer;
begin
  LMillisec := 0;

  while not AApp.Ready do
  begin
    Sleep(100);
    LMillisec := LMillisec + 100;
    if LMillisec > ATime * 2 then
      Break;
  end;

  //This one I have never seen:
  if LMillisec > 0 then
    ShowMessage('Waited '+IntToStr(LMillisec)+' for Excel');

  if LMillisec = 0 then
    Sleep(ATime);
end;

Answer 1

It looks like you could benefit from implementing an Ole Message filter. I had the same problem once and I found this link.

How to extend existing interface IMessageFilter with TInterfacedObject?

I then implemented the following unit and I just call IOleMessageFilter.RegisterFilter when doing automation and IOleMessageFilter.RevokeFilter when I'm done. See if that works for you.

unit Rgd.OleMessageFilter;

{Usage: IOleMessageFilter.RegisterFilter;
        IOleMessageFilter.RevokeFilter;

 You do not create an instance of IOleMessageFilter.  The class function IOleMessageFilter.RegisterFilter
 creates the instance, which is reference counted and is freed when revoked.

}

interface

uses WinApi.ActiveX, System.Classes, WinApi.Windows, System.SysUtils;

type
  IOleMessageFilter = class(TInterfacedObject, IMessageFilter)
  public
    {IOleMessageFilter interface...}
    function HandleInComingCall(dwCallType: Longint; htaskCaller: HTask; dwTickCount: Longint; lpInterfaceInfo: PInterfaceInfo): Longint; stdcall;
    function RetryRejectedCall(htaskCallee: HTask; dwTickCount: Longint; dwRejectType: Longint): Longint; stdcall;
    function MessagePending(htaskCallee: HTask; dwTickCount: Longint; dwPendingType: Longint): Longint; stdcall;
    {class functions to Register and Revoke...}
    class procedure RegisterFilter();
    class procedure RevokeFilter();
  end;

implementation

{TOleMessageFilter...}
function IOleMessageFilter.HandleInComingCall(dwCallType: Integer; htaskCaller: HTask; dwTickCount: Integer; lpInterfaceInfo: PInterfaceInfo): Longint;
begin
  Result := 0;
end;

function IOleMessageFilter.MessagePending(htaskCallee: HTask; dwTickCount, dwPendingType: Integer): Longint;
begin
  Result := 2 // PENDINGMSG_WAITDEFPROCESS
end;

function IOleMessageFilter.RetryRejectedCall(htaskCallee: HTask; dwTickCount, dwRejectType: Integer): Longint;
begin
  Result := -1;
  if dwRejectType = 2 then
  begin
    Result := 99;
  end;
end;

class procedure IOleMessageFilter.RegisterFilter;
var
  OldFilter, NewFilter: IMessageFilter;
begin
  OldFilter := nil;
  NewFilter := IOleMessageFilter.Create;
  CoRegisterMessageFilter(NewFilter, OldFilter);
end;

class procedure IOleMessageFilter.RevokeFilter;
var
  OldFilter, NewFilter: IMessageFilter;
begin
  OldFilter := nil;
  NewFilter := nil;
  CoRegisterMessageFilter(NewFilter, OldFilter);
end;

end.