Solved

Close Workbooks in Excel

Posted on 2001-07-04
16
3,066 Views
Last Modified: 2012-06-21
I want to close all workbooks in an runnig Excel from my Delphi4.0 Programm.



0
Comment
Question by:zonk
16 Comments
 
LVL 13

Expert Comment

by:Epsylon
ID: 6252810
0
 

Author Comment

by:zonk
ID: 6252900
That only with the Delphi 5.0 Compontents. I was hoping that there is a simple solution for D4.
0
 
LVL 8

Expert Comment

by:Cesario
ID: 6253027
you can try this

for i := XLApplication.Workbooks.Count downto 1 do
  XLApplication.Workbooks[i].close

Best Regards

Cesario
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6253045
You need the excel type library for this:


uses Excel_tlb, ActiveX, ComObj;

var
  Excel: _Application;
  AppWasRunning: boolean; // tells you if you can close Excel when you've finished
  lcid: integer;
  Unknown: IUnknown;
  Result: HResult;
  SaveChanges: Variant;
begin
  lcid := LOCALE_USER_DEFAULT;
  AppWasRunning := False;

  {$IFDEF VER120}      // Delphi 4
  Result := GetActiveObject(CLASS_Application_, nil, Unknown);
  if (Result = MK_E_UNAVAILABLE) then
    Excel := CoApplication_.Create

  {$ELSE}              // Delphi 5
  Result := GetActiveObject(CLASS_ExcelApplication, nil, Unknown);
  if (Result = MK_E_UNAVAILABLE) then
    Excel := CoExcelApplication.Create
  {$ENDIF}

  else begin
    { make sure no other error occurred during GetActiveObject }
    OleCheck(Result);
    OleCheck(Unknown.QueryInterface(_Application, Excel));
    AppWasRunning := True;
  end;
  Excel.Visible[lcid] := True;

  SaveChanges := true;
  Excel.Workbooks['map1.xls'].Close(SaveChanges, EmptyParam, EmptyParam, LCID);

  Excel := nil;
end;
0
 
LVL 1

Expert Comment

by:MaxSCZ
ID: 6253087
Hi,
try this

//Closing All WorkBooks and Setting the UserControl Property to False


   xlApp.WorkBooks.Close;  //Close all open workbooks
   xlApp.UserControl := False;
   xlApp := Nil;

bye Max
0
 
LVL 13

Accepted Solution

by:
Epsylon earned 200 total points
ID: 6253119
Or without type library (late binding):

uses ComObj;

var
  Excel: Variant;
  SaveChanges: OleVariant;
begin
  try
    Excel := GetActiveOleObject('Excel.Application');    
  except
    Excel := CreateOleObject('Excel.Application');    
  end;
  Excel.Visible := True;

  SaveChanges := true;
  Excel.Workbooks['map1.xls'].Close(SaveChanges);

  Excel := Unassigned;
end;
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6253146
Excel.Workbooks['map1.xls'].Close(SaveChanges);

must be

  Excel.Workbooks.Close;

to close them all workbooks and leave Excel open.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:zonk
ID: 6253445
That works only with delphi 5.0.
0
 

Author Comment

by:zonk
ID: 6253460
That doesnt work.
Delphi responds Method Workbooks not supported
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6253649
Both methods (early and late binding) work on Delphi 4. For early binding you need the excel_tlb.pas. You can create this unit in Delphi. Excel must be installed. The late binding method doesn't need the excel_tlb.pas.

By the way what Excel version do you use?
0
 

Author Comment

by:zonk
ID: 6253658
Excel 2000, but it will need to run with 5.0 as well.
Maybe I am doing somethin wrong then, but as posted above excel wont accept the workbooks command.

0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6253748
So Excel.Workbooks.Close doesn't work? What is the exact error?
0
 

Author Comment

by:zonk
ID: 6264201
Delphi reports a EOleSys Error, with "Method workbooks not supported by Automatisationobjekt"
(something like that its in german)
0
 
LVL 13

Expert Comment

by:Epsylon
ID: 6264257
That's very odd. Are you sure Excel has been installed properly.
0
 
LVL 26

Expert Comment

by:Russell Libby
ID: 8701861
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept Epsylon's comment as answer

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Thank you,
Russell

EE Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now