Kymberley
asked on
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(S ender: 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.f ieldbyname ('desc').a sstring+'. xls';
fname:=inputbox('Name','Na me 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(xlWBAT Worksheet, 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.a sstring;
end;
next;
end;
end;
// excel.Saveworkspace(fname) ;
// excel.quit;
end;
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(S
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
if (n=0) then exit;
fname:=mfmain.appdir+sqh.f
fname:=inputbox('Name','Na
if empty(fname) then exit;
// kill any existing excel apps
killer := TProcessKiller.Create;
while killer.KillProcess('excel.
killer.Free;
//setup
if fileexists(fname) then deletefile(fname);
excel.Disconnect;
lcid := GetUserDefaultLCID;
excel.Visible[lcid]:=true;
excel.Workbooks.add(xlWBAT
sheet:=excel.Workbooks[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.
end;
next;
end;
end;
// excel.Saveworkspace(fname)
// excel.quit;
end;
ASKER
It seems the following works:
excel.Workbooks[1].Close(t rue,fname, EmptyParam ,lcid);
excel.Disconnect;
excel.quit;
excel.Workbooks[1].Close(t
excel.Disconnect;
excel.quit;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sheet := null;
excel.quit;
excel := null;
to save as...:
workbook.UserControl := false;
workbook.DisplayAlerts := False;
workbook.SaveAs( FileName );
workbook.DisplayAlerts := true ;
workbook.UserControl := true;