?
Solved

Copy some excell cells to a memo.

Posted on 2006-06-30
5
Medium Priority
?
481 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month11 days, 3 hours left to enroll

770 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