Copy some excell cells to a memo.

Hi.

I dont know how to work with Excell and Delphi so this should be very easy for someone.
Well i'd like to copy certain cells in this order :

L4
H4
D4
L5
H5
D5
L6
H6
D6
L7
H7
D7
....
L804
H804
D804
....
Lx
Hx
Dx


to a memo until there is no data. (numbers) ...Also there are some empty cells.

Thanks in advance.
LVL 16
CodedKAsked:
Who is Participating?
 
Russell LibbyConnect With a Mentor Software Engineer, Advisory Commented:
Here is a code example that gets the desired ranges and adds the data (each row:col value = line) to the memo on form1. The only item worth mentioning is I use 16384 as the max limit on the row scan, eg:

ovRanges[0]:=ovSheet.Range['L4', ovSheet.Range['L16384'].End[xlUp]];

Indicates to get the range of cells from L4 to the last non empty column up to L16384. You can bump this number up to MaxWord if needed. The total number of rows to iterate is based on the range column with the most items in it. Simply speaking, it goes until all 3 row:col values are empty. This allows it to correctly handle columns with sparse data in it (lost of empty cells).

Just change the ExcelFile constant to the excel file you are using.

Regards,
Russell

---

unit Unit1;

interface

{$IFDEF VER150}
  {$DEFINE DS_DELPHI5_UP}
  {$DEFINE DS_DELPHI6_UP}
  {$DEFINE DS_DELPHI7_UP}
  {$DEFINE DS_DELPHI_7}
{$ENDIF}

{$IFDEF VER140}
  {$DEFINE DS_DELPHI5_UP}
  {$DEFINE DS_DELPHI6_UP}
  {$DEFINE DS_DELPHI_6}
{$ENDIF}

{$IFDEF VER130}
  {$DEFINE DS_DELPHI5_UP}
  {$DEFINE DS_DELPHI_5}
{$ENDIF}

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Math, {$IFDEF DS_DELPHI6_UP} Variants, {$ENDIF} ComObj, ActiveX, StdCtrls;

type
  TForm1            = class(TForm)
     Memo1:         TMemo;
     Button1:       TButton;
     procedure      Button1Click(Sender: TObject);
  private
     // Private declarations
  public
     // Public declarations
  end;

const
  ExcelFile         =  'c:\book1.xls';
  xlUp              =  -4162;

var
  Form1:            TForm1;

implementation
{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
var  ovApp:         OleVariant;
     ovWB:          OleVariant;
     ovSheet:       OleVariant;
     ovValue:       OleVariant;
     ovRanges:      Array [0..2] of OleVariant;
     dwRange:       Integer;
     dwCount:       Integer;
     dwIndex:       Integer;
begin

  // Create excel application to interact with
  ovApp:=CreateOleObject('Excel.Application');

  // Resource protection
  try
     // Open workbook file
     ovWB:=ovApp.WorkBooks.Open(ExcelFile);
     // Resource protection
     try
        // Get the active sheet
        ovSheet:=ovWB.ActiveSheet;
        // Resource protection
        try
           // Get the max range for each of the columns
           ovRanges[0]:=ovSheet.Range['L4', ovSheet.Range['L16384'].End[xlUp]];
           ovRanges[1]:=ovSheet.Range['H4', ovSheet.Range['H16384'].End[xlUp]];
           ovRanges[2]:=ovSheet.Range['D4', ovSheet.Range['D16384'].End[xlUp]];
           // Resource protection
           try
              // Lock the memo to be updated
              Memo1.Lines.BeginUpdate;
              // Resource protection
              try
                 // Clear memo lines
                 Memo1.Lines.Clear;
                 // Get count of range with most items
                 dwCount:=Max(Max(ovRanges[0].Count, ovRanges[1].Count), ovRanges[2].Count);
                 // Check for zero count
                 if (dwCount > 0) then
                 begin
                    // Get values and add to memo
                    for dwIndex:=1 to dwCount do
                    begin
                       // Walk the three ranges
                       for dwRange:=0 to High(ovRanges) do
                       begin
                          // Get value
                          ovValue:=ovRanges[dwRange].Item[dwIndex].Value;
                          // Check var type
                          if (TVariantArg(ovValue).vt in [VT_EMPTY, VT_NULL]) then
                             // Add blank string
                             Memo1.Lines.Add(EmptyStr)
                          else
                             // Add as string
                             Memo1.Lines.Add(ovValue);
                       end;
                    end;
                 end;
              finally
                 // Unlock memo
                 Memo1.Lines.EndUpdate;
              end;
           finally
              // Clear interface
              for dwRange:=0 to High(ovRanges) do ovRanges[dwRange]:=Unassigned;
           end;
        finally
           // Clear interface
           ovSheet:=Unassigned;
        end;
     finally
        // Clear interface
        ovWB:=Unassigned;
        // Quit the application
        ovApp.Quit;
     end;
  finally
     // Clear interface
     ovApp:=Unassigned;
  end;

end;

end.


0
 
CodedKAuthor Commented:
Hi Russell, thanks for your help...
I'm trying your code here but it doesn work...
Memo stays empty.

Is there something that i should (add / remove) from the uses of from endifs ?

:/
0
 
CodedKAuthor Commented:
The button onclick event in not handled right...
If i place any command before your code ... it does nothing..

procedure TForm1.Button1Click(Sender: TObject);
var  ovApp:         OleVariant;
     ovWB:          OleVariant;
     ovSheet:       OleVariant;
     ovValue:       OleVariant;
     ovRanges:      Array [0..2] of OleVariant;
     dwRange:       Integer;
     dwCount:       Integer;
     dwIndex:       Integer;
begin
 ShowMessage('Button pressed !!!');
  // Create excel application to interact with
  ovApp:=CreateOleObject('Excel.Application');
...
...
end;
0
 
Russell LibbySoftware Engineer, Advisory Commented:
Um, did you bind the button's onlcick event handler to this event? Better yet, just make it a procedure and call the procedure in your code to test

Russell
0
 
CodedKAuthor Commented:
Sorry Russell, that was stupid.
I've prepared the form with my own procedures and just copy-paste your proc... :)
Thanks, it works fine...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.