[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

Saving spreadsheets to named files and closing them from Delphi 7

I have been successfully writing to excel spreadsheets  from Delphi 7 bit have a couple of niggling issues. I can open and create the spreadsheets but have been unable to save with a particular file name. The save dialog appears with default directory of My documents and name of sheet1.xls.

Also need to be able to close a spreadsheet at the end (after saving).

In the past I have just created and let the user manually save and close. This however usually leaves a copy of excel.exe running in the back ground and can affect opening of other spreadsheets from shortcuts. I have to close it via task manager or anotehr program (hence the code in the procedure below to call processkiller)


procedure Ttfsqltest.btnexcelClick(Sender: TObject);
var
  fname:string;
  lcid,r,c,i,n:integer;
  killer: TProcessKiller;
begin
  n:=dbgrid1.Columns.Count;
  if n=0 then exit;
  n:=toint(inputbox('Columns','Number of columns to export (left most)',inttrim(n,3)));
  if (n=0) then exit;
  fname:=mfmain.appdir+sqh.fieldbyname('desc').asstring+'.xls';
  fname:=inputbox('Name','Name for XLS file',fname);
  if empty(fname) then exit;

  // kill any existing excel apps

  killer := TProcessKiller.Create;
  while killer.KillProcess('excel.exe') do ;
  killer.Free;

  //setup

  if fileexists(fname) then deletefile(fname);

  excel.Disconnect;
  lcid := GetUserDefaultLCID;
  excel.Visible[lcid]:=true;
  excel.Workbooks.add(xlWBATWorksheet,lcid);
  sheet:=excel.Workbooks[1].Worksheets[1];
  r:=0;
  with ApolloQuery1 do begin
    first;
    while not eof do begin
      r:=r+1;
      for i:=1 to n do begin
        sheet.cells[r,i]:=dbgrid1.Columns[i-1].Field.asstring;
      end;
      next;
    end;
  end;
//  excel.Saveworkspace(fname);
//  excel.quit;
end;
0
Kymberley
Asked:
Kymberley
1 Solution
 
ariohCommented:
to close sheet and excel with no need to kill excel.exe set all OLEVariant variables to null:
sheet := null;
excel.quit;
excel := null;

to save as...:
 workbook.UserControl := false;
 workbook.DisplayAlerts := False;
 workbook.SaveAs( FileName );
 workbook.DisplayAlerts := true ;
 workbook.UserControl := true;
0
 
KymberleyAuthor Commented:
It seems the following works:

  excel.Workbooks[1].Close(true,fname,EmptyParam,lcid);
  excel.Disconnect;
  excel.quit;
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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