• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3468
  • Last Modified:

Close Workbooks in Excel

I want to close all workbooks in an runnig Excel from my Delphi4.0 Programm.



0
zonk
Asked:
zonk
1 Solution
 
zonkAuthor Commented:
That only with the Delphi 5.0 Compontents. I was hoping that there is a simple solution for D4.
0
 
CesarioCommented:
you can try this

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

Best Regards

Cesario
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
EpsylonCommented:
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
 
MaxSCZCommented:
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
 
EpsylonCommented:
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
 
EpsylonCommented:
Excel.Workbooks['map1.xls'].Close(SaveChanges);

must be

  Excel.Workbooks.Close;

to close them all workbooks and leave Excel open.
0
 
zonkAuthor Commented:
That works only with delphi 5.0.
0
 
zonkAuthor Commented:
That doesnt work.
Delphi responds Method Workbooks not supported
0
 
EpsylonCommented:
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
 
zonkAuthor Commented:
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
 
EpsylonCommented:
So Excel.Workbooks.Close doesn't work? What is the exact error?
0
 
zonkAuthor Commented:
Delphi reports a EOleSys Error, with "Method workbooks not supported by Automatisationobjekt"
(something like that its in german)
0
 
EpsylonCommented:
That's very odd. Are you sure Excel has been installed properly.
0
 
Russell LibbySoftware Engineer, Advisory Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now