Delphi Open and Read Excel files

[BillKilgore]

Hello,

I've been trying to develop a Delphi 7 app that will read data from a
text or csv file, perform some basic functions, load it into an array,
and append the array contents below the current last row in an existing
Excel (XLS) file.
The pace is slow as documentation is somewhat sketchy so it's strictly
cut and paste, trial and error.
As it stands now, the code as shown will compile but if I try run it I get
the error message,
Project AccessingXL raised exception class EClassNotFound with message,
"class TExcelWorkbook Not Found."

When I remove the comment slashes from 'TExcelWorkbook' and 'TExcelWorkSheet'
under 'type' the compiler rejects with the message,
"Incompatible types: ' TExcelWorkbook' and '_Workbook'" and the same for TExcelWorksheet.

I think my problem is that I'm getting examples from different versions of Delphi, possibly 3 through 5.
I'm using 7 Professional and there is, of course, nothing in "help" or in my copy of Excel 2000, that I can determine. It seems like parts of some examples conflict with parts of others.

I've gotten most of my examples here, from the Dorothy Pate site, and numerous other sites as well. The LCID reference in 'var' is from a previous try and other than 1033 for the US I have no idea what it's here for.

In the interest of brevity I've included what I take to be the pertinent code. I think I get the Range stuff all right though I may need a hint on finding the last row.

I've included code from the unit declaration down for clarity. Thank you very much for sticking with me this far. Any help would be greatly appreciated.

Thank you, Bill Kilgore


UNIT TryAccessingXL;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Excel2000, OleServer, StdCtrls, ComObj,
AccessExcelSpecific; //Unit for basic Functions and Procedures unique to this application.

type
TAccess_Excel = class(TForm)
MyXLApplication: TExcelApplication;
// MyXLWorkbook: TExcelWorkbook;
// MyXLWorksheet: TExcelWorksheet;
btnOpenXL: TButton;
btnCloseXL: TButton;
procedure OpenXLFile(Sender: TObject);
procedure btnOpenTickersClick(Sender: TObject);
procedure btnReadCloseClick(Sender: TObject);
procedure CloseProgram(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
LCID,Rowe,Colmn : integer;
ticker_symbol, close_data, close_price, close_date, close_volume : string;
Access_Excel: TAccess_Excel;
ATryoutFile : TextFile;
MyXLWorkbook,MyXLWorksheet,RawData : OleVariant;
ArrayOfClose,SortedArrayOfClose : variant;

implementation

{$R *.dfm}

procedure TAccess_Excel.OpenXLFile(Sender: TObject);
Begin
MyXLApplication.Connect;
MyXLWorkbook.ConnectTo(MyXLApplication.ActiveWorkbook);
MyXLWorksheet.ConnectTo(MyXLApplication.ActiveSheet as _Worksheet);
MyXLWorksheet.ConnectTo(MyXLApplication.Worksheets.Item['RawData'] as _Worksheet);

MyXLWorkbook := MyXLApplication.Workbooks.Open('C:\AccessingXL\8.5 TEST TryOut.xls',
EmptyParam,EmptyParam,EmptyParam,
EmptyParam, EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,0);

MyXLWorksheet := MyXLWorkbook.Worksheets.Item['RawData'];
MyXLWorksheet.Activate;

MyXLWorksheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
// Get last row value
Rowe := MyXLApplication.ActiveCell.Row;
// Get rightmost column value
Colmn := MyXLApplication.ActiveCell.Column;

End; //procedure TAccess_Excel.OpenXLFile
.
.
.
.
END.

 

[svanels]

There are some methods higlighted in the FAQ forum, maybe they can help you out.

How to export data to Excel
faq102-1562

Regards

Steven

 

[BillKilgore]

Thank you svanels. I was aware of FAQ 102-1562. It was one of the examples I’ve tried to adapt to my situation and couldn’t get to work. It seems to me that there must be a simpler way than the introduction of VBA into the mix. The code included here has been adapted from the Deborah Pate website. Specifically Pate’s example of how to open an Excel file subheading, Late Binding. The code below lists my adaptation of Pate’s lead. The reason for the TExcelWorkbook and TexcelSheet being commented out is explained in my original statement above.


type
TAccess_Excel = class(TForm)
MyXLApplication: TExcelApplication;
// MyXLWorkbook: TExcelWorkbook;
// MyXLWorksheet: TExcelWorksheet;
.
.
.
.

var
LCID : integer;
MyXLWorkbook,MyXLWorksheet,RawData : OleVariant;
ArrayOfClose,SortedArrayOfClose : variant;

procedure TAccess_Excel.OpenXLFile(Sender: TObject);
Begin
MyXLApplication.Connect;
MyXLWorkbook.ConnectTo(MyXLApplication.ActiveWorkbook);
MyXLWorksheet.ConnectTo(MyXLApplication.ActiveSheet as _Worksheet);
MyXLWorksheet.ConnectTo(MyXLApplication.Worksheets.Item['RawData'] as _Worksheet);

MyXLWorkbook := MyXLApplication.Workbooks.Open('C:\AccessingXL\8.5 TEST TryOut.xls',
EmptyParam,EmptyParam,EmptyParam,
EmptyParam, EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,0);

MyXLWorksheet := MyXLWorkbook.Worksheets.Item['RawData'];
MyXLWorksheet.Activate;

MyXLWorksheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;
// Get last row value
Rowe := MyXLApplication.ActiveCell.Row;
// Get rightmost column value
Colmn := MyXLApplication.ActiveCell.Column;

End; //procedure TAccess_Excel.OpenXLFile

I went back through Web examples and once more concluded that the Pate example looks to be the most promising. If I can connect the dots. Help in this is greatly needed and appreciated.

Thanks again,

Bill Kilgore

 

[buho]

I think you are mixing things (starting with the gal's name: she is Deborah .

procedure TForm1.Button1Click(Sender: TObject);
var
  LCID : integer;
begin
  LCID := GetUserDefaultLCID;
  //
  XLApp.Connect;
  // Lets have Excel visible
  XLApp.Visible[LCID] := True;
  XLApp.DisplayAlerts[LCID] := True;
  // Open file
  XLApp.Workbooks.Open('D:\TEST.XLS',
                       EmptyParam, //UpdateLinks: OleVariant
                       EmptyParam, //ReadOnly: OleVariant
                       EmptyParam, //Format: OleVariant
                       EmptyParam, //Password: OleVariant
                       EmptyParam, //WriteResPassword: OleVariant
                       EmptyParam, //IgnoreReadOnlyRecommended: OleVariant
                       EmptyParam, //Orign: OleVariant
                       EmptyParam, //Delimiter: OleVariant
                       EmptyParam, //Editable: OleVariant
                       EmptyParam, //Notify: OleVariant
                       EmptyParam, //Converter: OleVariant
                       EmptyParam, //AddToMru: OleVariant
                       LCID
                       );
  XLBook.ConnectTo(XLApp.ActiveWorkbook);
  XLSheet.ConnectTo(XLApp.ActiveSheet as _Worksheet);
  XLSheet.ConnectTo(XLApp.Worksheets.Item['Hoja1'] as _Worksheet);
  //
  // Do something with the sheet and save it
  //
  XLBook.Disconnect;
  XLApp.Quit;
  XLApp.Disconnect;
end;

The code above will open an excel sheet without glitch.

Note some issues:

1) You need to open the file before connecting the book and sheet (it is said in Pate's site, but in a very unclear way).

2) I'm using D6 and Office 97. The Workbooks.Open param list will vary if you use Office 2000/XP.

Now to your problems:

>>
As it stands now, the code as shown will compile but if I try run it I get the error message, Project AccessingXL raised exception class EClassNotFound with message, "class TExcelWorkbook Not Found."
<<

The code you posted here is doomed to failure in a zillion ways.

var
  MyXLWorkbook,MyXLWorksheet : OleVariant;
Begin
  MyXLApplication.Connect;
  [b]// The OleVariants are empty![/b]
  MyXLWorkbook.ConnectTo(MyXLApplication.ActiveWorkbook); [b]<---[/b]
  MyXLWorksheet.ConnectTo(MyXLApplication.ActiveSheet as _Worksheet); [b]<---[/b]
  MyXLWorksheet.ConnectTo(MyXLApplication.Worksheets.Item['RawData'] as _Worksheet); [b]<---[/b]
.....

<<
When I remove the comment slashes from 'TExcelWorkbook' and 'TExcelWorkSheet' under 'type' the compiler rejects with the message, "Incompatible types: ' TExcelWorkbook' and '_Workbook'" and the same for TExcelWorksheet.
>>

Ominous. Some very weird problem in the TLB Delphi used to generate the components.

Please, drop an Excel application, workbook and worksheet in a clean project, add my code, try to compile it and tell me what you get (remember your "Open" param list may vary).

buho (A).

 

[BillKilgore]

Buho,

It worked! Immediately!
Sorry for not getting back sooner. Some guy cut through our fiber cable with a backhoe.
I loaded and compiled it right off. I'm now working on the variant array transfer. Having the usual problems but I'm, at least, on familiar ground. When it's finished I'll post the complete package here as I'm sure there are others just as bewildered as I.

Thank you once more, Bill Kilgore

 

[buho]

Mixing models is a recipe for disaster; you can opt for late or early binding, but stay with only one model.

Early binding (the one I'm using in the example) is a little more difficult, but is more robust (and, secondarily, more efficient). Late binding is easier in the sense you can find more examples in the 'net.

buho (A).