Copying data from Excel to Delphi (StringGrid)

Hi wim,
try something like this:
(please note: I'm a little rusty so double-check my syntax)


function TForm1.GetExcelData(ExcelFile, ExcelSheet: Widestring): boolean;
const
  MAXROWS: integer = 100;
  MAXCOLS: integer = 40;
var
  E, wb, ws, cells: OleVariant;
  rowcount, colcount: integer;
  r, c: integer;
begin
  result := true;
  try
    E := CreateOleObject('Excel.Application');
    E.Visible := False;
    wb := E.Workbooks.Open(ExcelFile);
    ws := wb.Worksheets.Item(ExcelSheet);
    cells := ws.Cells;
    rowcount := cells.Rows.Count;
    colcount := cells.Columns.Count;
    if rowcount > MAXROWS then
      rowcount := MAXROWS;
    if colcount > MAXCOLS then
      colcount := MAXCOLS;
    cells.Resize(rowcount,colcount);

    ResizeGrid(rowcount, colcount);

    for r := 1 to rowcount do
      for c := 1 to colcount do
      begin
        StringGrid1.Cells[c,r] := cells.Item(r,c).Text;
      end;
  except
    result := false;
  end;
  E := Unassigned;
end;

procedure ResizeGrid(rs, cs: integer);
begin
  with StringGrid1 do
  begin
    FixedRows := 0;
    FixedCols := 0;
    Rowcount := 0;
    Colcount := 0;
    Rowcount := rs + 1;
    Colcount := cs + 1;
    FixedRows := 1;
    FixedCols := 1;
  end;
end;

hi,
does this unit help :
http://www.advdelphisys.com/codelib/ads_excel_u.html

it has a function ExcelCopyToStringGrid that may be ok.

Hi rondi,

I tried your suggestion, but I get the error 'Member not found' on the command :
    ws := wb.Worksheets.Item(strWsName);

strWsName contains '999' and there is a sheet in the WB that has this name.

Any suggestions ?

To inthe,
I'm going to try out your suggestion in a minute. I'll be back.


Best regards,
The Mayor.

Minor problem. Try this

ws := wb.Worksheets(strWsName);

or

ws := wb.Worksheets[strWsName];


The best thing to do would be to import the Excel type
library so you don't have to guess late-bound methods & props.

rondi

Hi inthe,

Do you know what what I should give as parameter 'Excel' ?

I get an EOleVariantError.

Thanx,
The Mayor.


------------------------------

Hi rondi,

Still getting the same 'Member Not found' error.
I'm getting depressed now ;-)

The Mayor.

Ho Rondi,

What do you mean by 'Importing the Excel Type Library' ?

If you're using D3-6 and you've got Office installed, you can open your project and in Delphi, click Import type library. from the list of installed libraries, choose Microsoft Excel 10 Object Library (or 9 depending on Office version).

Delphi will create a .pas file that has the Excel object
definitions so you can see what you can and can't call.`

Hello everyone,

If the Experts don't object, how about I refund your points and move this to the PAQ as a zero point question for future reference wimmeyvaert?

darinw
Roving Moderator

Fine by me ...

wimmeyvaert:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

I just asked to refund my points since I gave the answer myself ...
Best regards,

The Mayor.

I just asked to refund my points since I gave the answer myself ...
Best regards,

The Mayor.