Solved

Copy some excell cells to a memo.

Posted on 2006-06-30
5
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 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