Solved

Copy some excell cells to a memo.

Posted on 2006-06-30
5
463 Views
Last Modified: 2010-04-05
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.
0
Comment
Question by:CodedK
  • 3
  • 2
5 Comments
 
LVL 26

Accepted Solution

by:
Russell Libby earned 125 total points
ID: 17020559
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
 
LVL 16

Author Comment

by:CodedK
ID: 17027507
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
 
LVL 16

Author Comment

by:CodedK
ID: 17027520
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
 
LVL 26

Expert Comment

by:Russell Libby
ID: 17028863
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
 
LVL 16

Author Comment

by:CodedK
ID: 17029384
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now