Solved

Close Workbooks in Excel

Posted on 2001-07-04
16
3,128 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi Mdi application Child forms get behind control 7 270
Magic Software info 18 138
How to debug For loops? 3 56
Dynamically Created Query 3 62
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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