Using Comobj unit to select an Excel worksheet
lazarus / 34-Windows

----------------------------------------------------------------------
Caravelle · 2015-02-14 20:31

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:\Testbi
ld.JPG');
   // Alternative
   // Um das richtige Worksheet zu addressieren verwendet man die
Namen von Workbook und Worksheet
   varXLApp.WorkBooks['Test.xls'].WorkSheets['Tabelle2'].SetBackGround
Picture('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:
Quote...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 · 2015-02-14 20:55

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 · 2015-02-14 21:06

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 · 2015-02-15 15:46

QuoteNo, don't double-post.
I have now deleted the old post.

QuoteAnyway, 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 QuoteMethod '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 · 2015-02-15 16:16

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

----------------------------------------------------------------------
wp · 2015-02-15 16:41

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.