Solved

Porting data to Excel

Posted on 1998-01-07
16
376 Views
Last Modified: 2010-04-04
Hi,

I collect data from a serial port and I want to send it to an excel file.
I want :
 the data is placed in the active cell,
 the active cell is changed to another position :
  Up, Right, Left or Down one cell
  (depending on what user wants)

I use at the moment the TExcel component for this.
I tried to use the Command ActiveCell.Offset(0,1).Activate,
but I don't get a reaction of Excel.

Has anybody worked with TExcel?
Has anybody already realised what I want to get done?
Is there a better solution?

Thanks,
Zif.
0
Comment
Question by:ZifNab
[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
16 Comments
 
LVL 5

Expert Comment

by:ronit051397
ID: 1355642
Did you try "talking" to Excel by OLE?
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355643
Not yet, good idea.
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355644
How to put data in correct workbook and worksheet?

  try
    FExcel := CreateOLEObject( 'Excel.Application' );
    FExcelCreated := True;
  except
    FExcelCreated := False;
  end;
0
Technology Partners: 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!

 
LVL 5

Expert Comment

by:ronit051397
ID: 1355645
Are you using Delphi 3.0?
Do I understand that you want to put data in the active cell?
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355646
I'm using D2.

I want to port data read from the serial port to a excel sheet.
This data can be 1 value or 21 values.

I want to put the data directely to the active cell of the active sheet of the active workbook. Then I want to change the active cell, e.g. the cell just under the filled cell becomes the active cell.
If 21 values are read, these data come under each other (beginning from the active cell of Excel sheet).

My program has to run in background when Excel is running. I just want to use the program for getting information from the serial port and I have written this already in delphi. Don't want to write it in visual basic.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1355647
To put 'ronit' in the active cell you need to write:

procedure TForm1.Button1Click(Sender: TObject);
var FExcel: variant;
begin
  FExcel:=CreateOLEObject('Excel.Application');
  FExcel.ActiveCell.FormulaR1C1:='ronit'
end;

But I'am getting Access violation all the time...


0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1355648
By the way, If performance is important to you, that is, Data refreshing is made in short intervals of time, then, using OLE is not proper for your case, since OLE is extremely slowwww.
Instead, send data via DDE which is much faster. I once wrote a code, using DDE between Delphi and Excel, similar to your case, but it was long time ago, and it will take me some time to rewrite it, also because DDE 'needs' more code.
Anyway, there is an example in the Delphi Super Page. The file name is aexcel.zip, by Stefan Hoffmeister.
0
 
LVL 3

Expert Comment

by:Matvey
ID: 1355649
Well, hello again

I thought of messages :)   :

Maybe you can send KeyPress Messages to Excel...Just a wild gess

Matvey
0
 
LVL 4

Expert Comment

by:itamar
ID: 1355650
Hi Zif,

another guess ...

What about making a DLL and declare as a Function in Excel ?

Itamar
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355651
About DDE, the component I now use is TExcel. The previous version of TAExcel. But I can't get to do it wath I want. I can write to the active cell. But I can't change the active cell!
eg. with ActiveCell.Offset(0,1).Active I get no response of Excel!
0
 
LVL 5

Accepted Solution

by:
ronit051397 earned 50 total points
ID: 1355652
Regarding the last comment, I checked and you should write Activate and not Active:

ActiveCell.Offset(1, 0).Activate  //for one row down

This was tested with Excel95.
By the way if you write data to 21 cells at the same time, this is not the proper way to do it, meaning, to move the selection 21 times. you can post data to 21 cells without 'running' in the cells, but this is another issue and worth more then 30 points.

Itamar, there is no need to 'invent the wheel'.
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355653
Offcourse I give more points if the comments/answers give a better result then I first thought I would get.

About Activate : I know, it was a mistype from me. It works in excel, but if you call this function with TExcel, Excel just doesn't react to it. Strange isn't it? (Maybe because Excel hasn't got the focus at that time, how to give the Excel window the focus?)

About other issue: please, I'm very interested.

I think I want to work with DDE
0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355654
To let you know I'm willing to offer more points ...
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1355655
What I have answered is not working. Wait I am still checking...
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1355656
To move 1 row down write: SELECT("R[1]C")
To move 1 row up write: SELECT("R[-1]C")
To move 1 column right write: SELECT("RC[1]")
To move 1 column left write: SELECT("RC[-1]")

And In general, with DDE, you cannot use the Excel Basic methods, only the Excel macro commands and the .Activate is a Basic method but SELECT(..) is a macro command.

As for Speeding Up:
There is no need to write the speeding code because if you take a look at Hoffmeister's Demo you'll see that he says that you can get an extra code for speeding up this particular process. That's what he says:
'Please contact Stefan Hoffmeister for the high-speed DDE code, that will allow you to move 1000 elements in less than 150 milliseconds. eMail: Stefan.Hoffmeister@Uni-Passau.de'

So, contact him and ask him for the code.

0
 
LVL 8

Author Comment

by:ZifNab
ID: 1355657
Ronit! Thanks a lot!!

Regards,
Zif.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tidtcpserver connection lost handle 2 124
Delphi: making a BW image transparent 10 137
can i put an image from resource into image tag ? 6 41
Browsing a TTreeView in Delphi 5 49
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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…

739 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