Using Comobj unit to select an Excel worksheet

[Caravelle]

I'm trying again here after not getting any responses in the General Programming section.

Laz 1.2.2;  FPC 2.6.4;   Vista SP2;  fading braincells.

I found a simple way to extract data from a small Excel file using the code at:
http://wiki.lazarus.freepascal.org/Office_Automation#Reading.2FWriting_an_Excel_file_using_OLE

Code: [Select]

uses .....  comobj;
procedure TForm1.Button1Click(Sender: TObject);
Var   XLApp: OLEVariant;
      x,y: byte;
      path: variant;
 begin
 XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
 try
   XLApp.Visible := False;         // Hide Excel
   XLApp.DisplayAlerts := False;
   path := edit1.Text;
   XLApp.Workbooks.Open(Path);     // Open the Workbook
   for x := 1 to 4 do
    begin
     for y := 1 to 6 do
      begin
       SG.Cells[x,y] := XLApp.Cells[y,x].Value;  // fill stringgrid with values
      end;
    end;
 finally
   XLApp.Quit;
   XLAPP := Unassigned;
  end;This works very well, my thanks to the person who wrote it.  The only snag is that it opens the most recently-opened worksheet (my xls file has 12 worksheets, one for each month), so I need to be able to specify which sheet to read.

After searching I discovered this entry in the German wiki http://wiki.lazarus.freepascal.org/ExcelAutomation/de#Worksheet_aktivieren.  It suggests this:

Code: [Select]

begin
   //  Um das richtige Worksheet zu addressieren verwendet man den Index von Workbook und Worksheet
   varXLApp.WorkBooks[1].WorkSheets[1].SetBackGroundPicture('D:\Testbild.JPG');
   // Alternative
   // Um das richtige Worksheet zu addressieren verwendet man die Namen von Workbook und Worksheet
   varXLApp.WorkBooks['Test.xls'].WorkSheets['Tabelle2'].SetBackGroundPicture('D:\Testbild.JPG');
end;I have not been able to make this work.  Are the WorkBooks and Worksheets indexed from 0 or 1 ?    Is it necessary to give a WorkBooks index when there is only one WorkBook ?  When I got past the  indexing error messages with

Code: [Select]

XLApp.WorkBooks.WorkSheets['January'].Activate;  I got this message:

...EOleError...Method 'WorkSheets' is not supported by Automation Object

     How can this be true in the light of what the German wiki article says?

I would very much appreciate some advice, please.  This is my first attempt at OLE.

Caravelle

[wp]

No, don't double-post. If no one answers on your first post this is not because it was the wrong board, probably just because nobody knows the answer...

I don't either, I just have an old link from my Delphi days: http://www.djpate.freeserve.co.uk/AutoExcl.htm#OpenWorkbook - maybe it helps.

Anyway, why don't you use fpspreadsheet? It can read Excel files without OLE, no need to have Excel installed. And using the visual spreadsheet controls you can get the data into a grid without writing a line of code:
- http://wiki.lazarus.freepascal.org/FPSpreadsheet
- http://wiki.lazarus.freepascal.org/FPSpreadsheet_tutorial:_Writing_a_mini_spreadsheet_application

[wp]

This is working:

Code: [Select]

procedure TForm1.Button1Click(Sender: TObject);
Var
  XLApp: OLEVariant;
  book: OLEVariant;
  sheet: OLEVariant;
  x,y: byte;
  path: variant;
begin
  XLApp := CreateOleObject('Excel.Application'); // requires comobj in uses
  try
    XLApp.Visible := False;         // Hide Excel
    XLApp.DisplayAlerts := False;
    path := 'D:\Prog_Lazarus\svn\lazarus-ccr\components\fpspreadsheet\examples\excel8demo\test2.xlsx';
    book := XLApp.Workbooks.Open(Path);     // Open the Workbook
    sheet := book.Worksheets.Item[2];      // 2nd sheet --> index seems to begin with 1, not 0
    for x := 1 to 2 do
    begin
      for y := 1 to 2 do
      begin
        StringGrid1.Cells[x,y] := UTF8Encode(sheet.Cells[y,x].Value); 
      end;
    end;
  finally
    XLApp.Quit;
    XLAPP := Unassigned;
  end;
end;

[Caravelle]

No, don't double-post.

I have now deleted the old post.

Anyway, why don't you use fpspreadsheet?

Because I simply can't get fpspreadsheet to do what I want.  I have already spent hours trying, following the guidance you quote.   As I came across a bit of code using OLE that worked first time, why not use it and save myself a lot of time and trouble?  Excel will always be installed on our system, because we use it for data entry.  All I want from my program is to read a worksheet created with Excel itself; then process the data, and insert it after modification into a sqlite table.   Moreover my final program will not display the Excel data in a grid at all, so whether that can be done with or without code is irrelevant.  The grid is simply a feature of the example in the wiki.  All I am trying to do now is what I said, add the ability to select a specific worksheet to the existing code in the wiki.   Once I get that going I re-use the relevant bits of code in my own program.

Thank you for the working code you have provided, I shall have to try to work out why the version I was using did not work, especially why I got the error

Method 'WorkSheets' is not supported by Automation Object

when your code uses that method successfully.

I do have one question though.  The first time in a programming session that I run the program (both my original version and yours), I get an OLE error "The specified module could not be found" (the Comobj unit, maybe ?).  The second time I run it, with no modification, I get no such error.  If I close the project and re-open it, I do not get the error again.  Is there something I can do to fix this ?

Many thanks

Caravelle

[marcov]

(maybe same issue as http://bugs.freepascal.org/view.php?id=24622 ?)

[wp]

Sorry I can't help you with OLE...

What is it that you cannot do with fpspreadsheet? Which kind of file? xlsx? Can you post the file? I'll be happy to look into fpspreadsheet to solve your issue.