CODEWITHSUNDEEP
delphiexcel
Corne Beukes
Corne Beukes

Reputation: 1999

Determine when an Excel workbook has closed with Delphi

The following code opens the document specified by the 'app' parameter and then waits until the particular document has been closed. This works fine for all document types, except when you have an Excel workbook open and open another Excel workbook. The code thinks the document has closed when it is actually still open. How would I solve this?

procedure RunAppAndWAit( a: TApplication; app, par, verb: string);
var
  seinfo: tshellexecuteinfo;
  exitcode: dword;
begin
  fillchar( seinfo, sizeof( seinfo), 0);
  seinfo.cbsize := sizeof( tshellexecuteinfo);

  with seinfo do
  begin
    fmask := see_mask_nocloseprocess;
    wnd := a.Handle;
    lpfile := pchar( app);
    lpDirectory := pchar( ExtractFileDir( app));
    lpParameters := pchar( par);
    lpVerb := pchar( verb);

    nshow := sw_shownormal;
  end;

  if ShellExecuteEx( @seinfo) then
  begin
    repeat
      a.ProcessMessages;
      GetExitCodeProcess( seinfo.hprocess, exitcode);
    until ( exitcode <> still_active) or a.terminated;
  end
  else
    sshowmessage( 'Unable to open ' + app);
end;

Upvotes: 3

Views: 1779

Answers (2)

RobertFrank
RobertFrank

Reputation: 7394

This isn't pretty and may not be as reliable as you wish, but you could loop (or better, use a timer event?) calling the Windows EnumWindows function looking for title bars that match what you'd expect Excel to show for this file. (Obviously, this is an Excel-specific solution.)

For example, look for a title bar that contains the word "Excel" and your file name, which is what Excel shows in the title bar.

There may be holes in this approach that make it fragile. In fact, I'm a bit hesitant to post this since I don't think the solution is particularly robust. However, if you have no other way to solve your problem, this might work...

Google "EnumWindows Delphi" for sample code.

... on further thought,below is another way. As Jeroen noted, you could use an API to Excel. If you're doing a lot of these calls, then put the CreateOLEObject and unAssigned assignment outside the function might make it less heavy. (And you'll need some try...except blocks in case Excel is no longer running, etc.) This solution, too, is Excel-specific and clumsy, IMO. I don't know if there might be circumstances (like a File, Dialog box open in Excel?) that would cause this to return erroneous result.

So, basically, I'm saying, here are two relatively weak approaches that are specific to Excel and may not always work. (When I say it that way, I'd almost rather just delete this entire post... But, maybe it'll give you some ideas on how you want to proceed.)

This code is not tested, but similar code has worked for me in the past:

  uses ComObj;

  function FindWorkbook( Workbookname: String):boolean;
  var
    ExcelOLE: Variant;
    WorkbookNumber: Integer;
  begin
    Result := FALSE;
    ExcelOLE := CreateOLEObject('Excel.Application');
    try
      for WorkbookNumber := 1 to ExcelOLE.Workbooks.Count do
        if UpperCase(WorkbookName) = UpperCase(ExcelOLE.Workbooks[WorkbookNumber].Name) then
          Result := TRUE;
    finally
      ExcelOLE := unAssigned;
    end;
  end;

Upvotes: 1

Jeroen Wiert Pluimers
Jeroen Wiert Pluimers

Reputation: 24493

Your attempt only works for applications that open the document in the same process which launches the document.

A lot of applications don't work this way any more: the process launching the document will pass the document to another process that shows/edits it, and the launching process dies.

You will need to find an API that supports event callbacks (in this case for Excel, most likely the COM API that Excel exposes) that lets you watch more closely what Excel actually does with your document.

Open your document using this API, register an event that gets called when the document is closed, wait for the event, then close.

Upvotes: 4

Related Questions