Solved

Close Workbooks in Excel

Posted on 2001-07-04
16
3,215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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