troubleshooting Question

Delphi and Excel 2k3 interaction?

Avatar of PeterdeB
PeterdeBFlag for Netherlands asked on
Delphi
7 Comments1 Solution526 ViewsLast Modified:
Hi Folks,

After digging through EE database and copying pasting my way into Delphi's IDE I managed to create an application which interacts with Excel.

It does require some modifications, so that is why I post it here. If you need more info please ask.

1. btnOpen should fire up a file selectiondialog in which I can select an excel file and show it in the panel. It now opens up test.xls rather relentlessly ;-)

2.btnSearch should search all sheets of the opened workbook for a certain string and add all occurences to the memo. This kinda works....(emphasizing kinda ;-)

3. Correctly open and close  objects etc.

So if you take care of either one of the mentioned probs I will assign and close this q and fire another one up.

Thanks,

Dweep

Ps please comment on what you do and on what I did or shouldn't have done I'm eager to learn;-)
unit main;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, shellapi, ExtCtrls, StdCtrls, ComObj, OleServer, Excel2000;
 
type
  TfrmExcel = class(TForm)
    panel1: TPanel;
    btnOpen: TButton;
    mmo1: TMemo;
    btnSearch: TButton;
    edt1: TEdit;
    ExcelApplication1: TExcelApplication;
  function GetWindowHandle(ExcelObject: Variant): hwnd;
  procedure EmbedInPanel(WindowHandle: hwnd);
    procedure btnOpenClick(Sender: TObject);
    procedure btnSearchClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;
 
var
  frmExcel: TfrmExcel;
 
implementation
 
{$R *.dfm}
 function TfrmExcel.GetWindowHandle(ExcelObject: Variant): hwnd;
var s: string;
begin
 // temp change caption so we can find it
 s := 'excel find me';
 ExcelObject.Caption := s;
 
 result := FindWindow(nil, pchar(s));
 
 // change the caption back to normal
 ExcelObject.Caption := '';
end;
 
 
procedure TfrmExcel.EmbedInPanel(WindowHandle: hwnd);
var Style: integer;
begin
 // remove the caption
 Style := GetWindowLong(WindowHandle, GWL_STYLE);
 Style := Style and (not (WS_CAPTION));
 SetWindowLong(WindowHandle, GWL_STYLE, Style);
 
 // move to the panel
 if Windows.SetParent(WindowHandle, Panel1.Handle) = 0 then
  style := GetLastError();
 
 // set size correctly
 SetWindowPos(WindowHandle, 0, 0, 0, Width, Height, SWP_NOZORDER or SWP_SHOWWINDOW);
 ShowWindow(WindowHandle, SW_MAXIMIZE);
end;
 
procedure TfrmExcel.btnOpenClick(Sender: TObject);
var
 WorkBook: variant;
 Excel: Variant;
 WindowHandle: hwnd;
begin
 Excel:= CreateOleObject('Excel.Application');
 WindowHandle := GetWindowHandle(Excel);
 if WindowHandle <> 0 then
  EmbedInPanel(WindowHandle);
  Excel.Visible := True;
 WorkBook := Excel.Workbooks.open('test.xls');
 
end;
 
 
 
procedure TfrmExcel.btnSearchClick(Sender: TObject);
var
  Sheet_name : string;
    i, r, c, last_column, last_row :integer;
 WorkBook, sheet: variant;
 Excel: Variant;
 WindowHandle: hwnd;
begin
{ I suppose I should be able to get my hands on this document without exactly copying the code I used within the btnOpen event, I only dunno how}
 
 Excel:= GetActiveOleObject('Excel.Application');
  WindowHandle := GetWindowHandle(Excel);
 if WindowHandle <> 0 then
  EmbedInPanel(WindowHandle);
  Excel.Visible := True;
 WorkBook := Excel.Workbooks.open('test.xls');
 
 for i := 1 to Excel.ActiveWorkbook.WorkSheets.Count do
     begin
     Sheet_name := Excel.ActiveWorkbook.WorkSheets[i].Name;
     Sheet := WorkBook.Worksheets.Item[Sheet_name];
     Sheet.Activate;
     Sheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
     Last_column := Excel.ActiveCell.Column;
     Last_row := Excel.ActiveCell.Row;
     r := 0;
     repeat
        inc(r);
        c := 0;
        repeat
           inc(c);
           if pos(edt1.text, Excel.Cells.Item[r,c]) > 0  then  mmo1.Lines.add(Excel.cells.item[r,c]);
        until c = Last_column;
     until r = Last_row;
   
  end;
 // Excel.Quit;
 // Excel := Unassigned;
end;
end.
ASKER CERTIFIED SOLUTION
SteveBay

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros