Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Close Workbooks in Excel

Posted on 2001-07-04
16
Medium Priority
?
3,365 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
Industry Leaders: 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 800 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

Technology Partners: 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!

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

610 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