Accessing Data in Excel via ADO - Cleaning up possible?

[kaywarner]

Hi All, thanks for taking the time to read the post.

My question is related to optomising an ADO connection.

At present, I retrive live data into my Delphi Application from an Excel spreadsheet(this is updated in Excel using the 'RTD' function). This means that I have to open Excel, open a ADO connection with my application to Excel, then Excel updates a cell value in a worksheet, then finally my delphi app reads it into a DBGrid and does some math and draws a graph.

my question is this, am I doing this right?!! Is there a way to connect directly into the Excel RTD function rather than have to open excel?

many, many thanks for your help in advance.

I am using this code:


///////////////////////////////////////////////////

procedure TForm1.ConnectToExcel;
var
strConn : widestring;
begin
strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source=' + Edit1.Text + ';' +
'Extended Properties=Excel 8.0;';

AdoConnection1.Connected:=False;
AdoConnection1.ConnectionString:=strConn;
try
AdoConnection1.Open;
AdoConnection1.GetTableNames(ComboBox1.Items,True);
except
ShowMessage('Unable to connect to Excel, make sure the workbook ' + Edit1.Text + ' exist!');
raise;
end;
end;(*ConnectToExcel*)

procedure TForm1.FetchData;

begin
StatusBar1.SimpleText:='';
if not AdoConnection1.Connected then ConnectToExcel;
AdoQuery1.Close;
AdoQuery1.SQL.Text:=Edit2.Text;
try
AdoQuery1.Open;
except
ShowMessage('Unable to read data from Excel, make sure the query ' + Edit1.Text + ' is meaningful!');
raise;

end;

Procedure TForm1.Timer(Sender: TObject);
begin
FetchData;
Price.Text:=DBGrid1.Fields[5].AsString;
Quant.Text:=DBGrid1.Fields[6].AsString;
Time.Text:=DBGrid1.Fields[7].AsString;
end;

"I won't say that its better than sex, but it does last longer" - Professor Stephen Hawking on Eureka Moments - HaHaHaHa - Awesome!

 

[DjangMan]

Another way is to automate Excel. That will remove the ADO connection overhead.

 

[kaywarner]

Thanks for the Reply DJangMan!

I am looking to remove the Excel aspect of it all together, rather than Excel doing the work. I feel that is slowing the process down somewhat!

What method would Excel use to get this information?
Is it COM based?

many thanks,

Kay



"I won't say that its better than sex, but it does last longer" - Professor Stephen Hawking on Eureka Moments - HaHaHaHa - Awesome!

 

[DjangMan]

From what I can see the RTD server will be pulling data from somewhere else. If you can find that 'somewhere else' then you may be able to query that directly. This page:

http://www.add-in-express.com/add-in-net/excel-rtd-servers.php

Shows how you can create a RTD server with their tool (which is a very cool tool, btw) and it looks like you have full control over how you want to collect the data that Excel is asking for. So that means that you can probably reproduce what the RTD is doing all within Delphi and bypass the Excel part.