Delphi asks for a non-existant .pas file. 3

[MichaelHooker]

Delphi 7 personal edition, Win XP SP2.

I have recently successfully installed "XLSReadWrite" into my Delphi system. This is a rather old but fast and functional widget for reading/writing Excel files, and it must be properly installed because all the demos work, even the new stand-alone test program I wrote to try it out works. This is not a query about XLSReadWrite. If you only want to read a file, you declare "XLSRead" in the uses section; and when you click the right icon, "XLSRead1: TXLSRead" is added to the Form Types section (in my case I then change the name of the component to XLSRead, to match the sample code). You then add various procedures to cope with the events you want to use.

I got my test program working just as I wanted, and then started transferring the code to my existing 1,000+ line program, hoping that I would no longer have to manually create the csv files from the Excel Workbook form in which the data reaches me. My new routine would (hopefully will) read two specific Worksheets and produce the csv files and we carry on from there combining the data in these files with the data in the text files which were subject of my previous query in this Forum.

But I can't run the result. Using a breakpoint and stepping through line by line I can see that the program runs happily past the line:

XLSRead.FileName := string + '.xls';

so it must recognise the XLSRead.FileName property, and indeed Watch tells me exactly what that is.
But when we get to:

XLSRead.Read;

which unsurprisingly is the method for reading the Excel file referenced by the filename , I get a message:

"Source file not found: XLSRead.pas"

and I'm offered the chance to browse for it. I browse but there's no such file in the package. There's an XLSRead.dcu, which is presumably what the XLSRead in the uses clause refers to, but that's not the same thing at all. And I don't have a clue what to do next... I didn't get this message with any of the XLSRead demos or my test program which uses the same code to the letter (and they only have the one main unit, Unit1) and I'm sure it's nothing to do with XLSRead itself, otherwise I'd be addressing this to the author.

Clearly there's a lot I don't understand about how Delphi uses external packages, and the documentation I have isn't very helpful. Can some kind person please explain what sort of error I should be looking for? The new code was cut and pasted from the working test program so I'm fairly sure it's not the problem.

Many thanks in advance.

Michael Hooker

 

[whosrdaddy]

simple,

the .dcu file is a compiled version of .pas version.

with many component packages you don't get the source files (*.pas) ,unless you pay for it.

I tend to avoid packages where I don't have the source files. It's like working with a blackbox, you put something in it and you get something out of it, but you can't see what's happening inside the box.

about your problem, try to copy the xslread.dcu file into your project directory (or into the lib directory), that should do the trick.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[MichaelHooker]

Magic! Copying the dcu file to the program's folder worked a treat.

The directory the dcu file is in is already in the library path (the XLSReadWrite installation routine saw to that). My test program was in a different folder, parallel to the one the project I'm working on is in, so whatever it is that makes a difference it looks to be fairly random.

In principle I agree with what you say about third party packages, but (a) you get what you pay for and I'm retired and my pension has to be carefully managed and (b) when I do get the source code I don't understand it. Especially if it's in C. I did once, a long time ago, manage to write a program myself that read Excel files, after looking at an xls file in a hex editor and working out where the bits I wanted were. You don't actually have to know what all the bytes describing the individual cells mean if all you want is the values they contain and there aren't any formulae or special formatting of values. But Excel has got a lot more complicated since then - multiple worksheets for a start.

Many thanks for your help, it's much appreciated. I'll try not to come across any more problems for a bit. Fortunately my basic program works fine, it's the enhancements that cause all the trouble. And anomalous input files, but we've solved that one until they try something even weirder.

Michael Hooker

 

[CamaroLT]

One of the other things you can do is look into managing XLS files via ADO. Its free, you control whats going on. About the *ONLY* thing I haven't figured out is how to create the XLS ground up from within Delphi. If you want, I can come up with some quick source code to deal with XLS files.

I haven't gotten too complex with ADO/XLS though. Basically I've used the XLS file from an export of another program to dump into a MS SQL DB, meaning the XLS file is the middle man from QuickBooks to MSSQL.

ADO MIGHT do what you want it to do, it may not. Entirely depends on how you're dealing with things.

-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[MichaelHooker]

Thanks, but that would mean finding out what ADO is! The XLSWrite module in XLSReadWrite would create an Excel spreadsheet if I ever wanted to do that, but I don't think I ever shall.

All I needed to do was turn a couple of Excel worksheets into plain text data without actually opening up Excel and going through all the tedious procedure involved to Save As a csv. The end result of my program is a simple formatted text file which (a) gets cut and pasted into an e-mail for daily distribution, (b) gets posted to a website from where it can be downloaded and (c) gets imported into a giant MySQL database for online consultation.

The system has been going for years, but the data supplier suddenly decided to send us <some> of the data in a daily Excel Workbook instead of the plain csv files we were all set up for, so it was just a matter of finding an add-on to cope with this extra stage. And we did, thanks to Tek-Tips we overcame the mystery missing file problem, ironed out another problem which arose, the person who uses the program is very happy to now be avoiding Excel completely, saving time and the possibility of error, and around 1600 people are happy to find the results in their mailboxes every day.

I have to say, I always find csv files the perfect medium for my programs, which don't do much more than a lot of complicated string processing. Instant StringLists and you can read them in a Memo control. Why make life complicated?

Michael Hooker

 

[CamaroLT]

ADO is Active Data Objects. They're components that talk to any type of database. XLS is a.. uhh.. could be considered a type of a database. All a database is, is a bunch of tables with a bunch of rows that contain particular fields that hold certain data. For Excel, your work book would be the XLS file itself, or the database, the work sheets would be the tables, so on and so on. ADO is able to speak the language that Excel uses. ADO is capable is speaking with CSV as well.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job

 

[MichaelHooker]

Thanks but no thanks. ADO may be capable of speaking to whatever but I'm not capable of speaking to ADO and see no reason to learn how to use it (them?) when I'm trying hard to learn other things (JavaScript, PHP, MySQL, SQLite, AutoIt) and my head hurts already. I may never have to unscramble an Excel file in Delphi again, but if I do I have a tool that works.

Michael Hooker

 

[roo0047]

You dont need ADO to read from excel. Here's the meat of a working application that shows just how simple it is:

uses
  ..., ComObj;

type
  TSgForm = class(TForm)
    SG: TStringGrid;
     procedure FormShow(Sender: TObject);
    ...
  private
    { Private declarations }
    ...
    procedure FillStringGrid;
  public
    { Public declarations }
  end;

var
  SgForm: TSgForm;

implementation

var
  XL: Variant;

procedure OpenTimeSheets;
var XlFn: string;
begin
  if not VarIsEmpty(XL) then
  try
    XlFn:= XL.Workbooks[1].Name
  except
    XLFileOpen:= false
  end;
  if EmpFileName = '' then
    EmpFileName:= AppPath + 'TimeSheet' + #32 +
                  FormatDateTime('yyyy-mm-dd', StrToDate(WeekEnding)) + '.xls';
  if Not XLFileOpen then
    if XlFn <> EmpFileName then
      if FileExists(EmpFileName) then begin
        XL.Workbooks.Open(EmpFileName);
        XLFileOpen:= true
     end else
       CopyTemplateAndOpenExcelFile(EmpFileName);
  XL.Visible:= true;
end; //OpenTimeSheets

procedure TSgForm.FillStringGrid;
var
  X, Y: Integer;
  aName: string;
const
  xlCellTypeLastCell = $B;
begin
  try
    XL.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate; //Select last cell
    SG.ColCount:= XL.ActiveCell.Column;  //Set string grid size
    SG.RowCount:= XL.ActiveCell.Row;
    try            //mine are hard coded 'cause I know where they are...
      EmpName:=    XL.ActiveSheet.Cells[8, 2].Value;
      Location:=   XL.ActiveSheet.Cells[8, 6].Value;
      EmpNo:=      XL.ActiveSheet.Cells[10, 6].Value;
      WeekEnding:= XL.ActiveSheet.Cells[10, 2].Value;
      JobNo:=      XL.ActiveSheet.Cells[14, 2].Value;
      WorkPerf:=   XL.ActiveSheet.Cells[14, 3].Value;
      Caption:= EmpName;
      for X:= 1 to SG.ColCount - 1 do begin
        for Y:= 1 to SG.RowCount - 1 do begin
          SG.Cells[X, Y]:= XL.ActiveSheet.Cells[Y, X].Value;
          IF TRIM(SG.Cells[X, Y]) = '' then
            SG.Cells[X, Y]:= IntToStr(x) + ',' + IntToSt(y);
        end;
        Application.ProcessMessages;
      end;
    except
      ...
    end;
  finally
    ...
  end
end;

procedure TSgForm.FormShow(Sender: TObject);
begin
  FillStringGrid
end;

initialization
begin
  Template:= AppPath + 'Template.xls';
  CoInitialize(nil);
  XL:= CreateOleObject('Excel.Application');
  XLFileOpen:= false
end;

finalization
begin
  XL.Quit;
end;

Roo
Delphi Rules!

 

[Griffyn]

Hi Roo,

Just want to point out to others, that the one drawback with your approach is that your code requires that Excel is installed on the PC your Delphi program is running on, whereas the others don't need it at all.

 

[roo0047]

You are correct and good point. I don't think "others" would include ADO either.


Roo
Delphi Rules!

 

[CamaroLT]

Talking with Excel is supported with ADO even if Excel isn't installed. I know this because I'd written an application for the store I used to work at to help track inventory. It'd read and write directly from an Excel spreadsheet on a brand new install of XP.


-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=
NEVER send the boss to do a techs job