Yogesh wrote:
In my project I want to use Excel as if one of my GUI control (the way we
use button / text box, in simillary way I want excel sheet on my form)is
it possible ? How? Can anyone help me?
I have used the following successfully, although it does not
put an Excel sheet actually IN the form. You create an instance of Excel
and access it via VBA commands. The documentation is not good, and I
had to work out a lot of stuff by trial and error. The O'Reilly book "VB
and VBA in a nutshell" was useful.
Use the Task Manager after running code like this, to make sure that the
processes spawned by the Delphi app have been closed - look for EXCEL.EXE
in the processes list.
Create a form, put ComObj in the uses clause; declare a variant;
put a button called bbProcess on the form and use the click handler shown.
Put an OpenDialog and a SaveDialog on.
Works OK with Delphi 7, Office 97 and WinXP
Good luck!
Rob
8< ---------------------------------------------------
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, StdCtrls, Buttons;
type
TForm1 = class(TForm)
bbProcess: TBitBtn;
SaveDialog1: TSaveDialog;
OpenDialog1: TOpenDialog;
procedure bbProcessClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
private
{ Private declarations }
public
{ Public declarations }
function SelectWorksheet(nme: string; XL: variant): integer;
end;
var
Form1: TForm1;
implementation
var
XL: Variant; // OLE Excel spreadsheet
{$R *.dfm}
procedure TForm1.FormCreate(Sender: TObject);
begin
XL := CreateOLEObject('Excel.Application'); // Ole object creation
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
XL := Unassigned; // release the OLE object
{ ? Frankly I'm not 100% sure about this. "Free" is not supported
apparently. Anyone got any comments?}
end;
procedure TForm1.bbProcessClick(Sender: TObject);
var
F1, WSName: string;
XLPage, k: integer;
begin
// Get the Worksheet name
WSName := 'Sheet1'; // default
// Find and open the spreadsheet
if OpenDialog1.Execute then
try
XL.Workbooks.Open(OpenDialog1.FileName);
// Next line makes the spreadsheet visible
// comment it out to work with a non-visible spreadsheet
XL.Visible := true;
// get the right page
XLPage := SelectWorksheet(WSName,XL);
if XLPage > -1 then
begin
// start on row 1 and step through until a blank cell
k := 1;
F1 := XL.Cells[k,1].Value;
repeat
XL.Cells[k,6].Value := F1;
inc(k);
F1 := XL.Cells[k,1].Value;
until F1 = '';
ShowMessage('Finished!');
// save the modified spreadsheet
if SaveDialog1.Execute then
XL.Workbooks[1].SaveAs(SaveDialog1.FileName);
end;
finally
XL.Quit;
end;
end;
function TForm1.SelectWorksheet(nme: string; XL: variant): integer;
// Purpose: Select the named worksheet from the workbook. If found,
// return the index of the sheet. If not found, return -1.
// This is recursive - be careful!
// Global variables used: XL
// my Library modules used: <none>
// Change log:
var
k: integer;
begin
k:= 1;
while (k <= XL.WorkBooks[1].WorkSheets.Count) and
(XL.WorkBooks[1].WorkSheets[k].Name <> nme) do
inc(k);
if k > XL.WorkBooks[1].WorkSheets.Count then
begin
if MessageDlg('I cannot find the Excel work sheet called '''+nme+'''.
'+
'Do you know the correct sheet
name?',mtWarning,[mbYes,mbNo],0) = mrYes then
begin
if InputQuery('Enter data','Enter the sheet name',Nme) then
k := SelectWorksheet(nme, XL);
end
else
k := -1;
end
else
XL.WorkBooks[1].WorkSheets[k].select;
result := k;
end;
end.
_______________________________________________
Delphi mailing list -> [email protected]
http://www.elists.org/mailman/listinfo/delphi
