Retrieve data on timed intervals 1

[JTBorton]

I'm working with a macro that connects to and controls an OPC server (OPC = OLE for Process Control) to interact with the control system of a chemical plant. I'd like to run a subroutine that collects process values every few seconds and pushes them down on the spreadsheet. I want the user to still have control to do things while this is happening. I've thought about using the DoEvents statement, but that may require a loop to run continuously in the background which would slow things down and prevent other calculations. Ive also thought about using Application.Wait, but I don't think the user would be able to continue working while the macro waits. Does anyone have any thoughts on how to approach this?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Forgot to specify - this is using Excel 2007.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

Use the OnTime method.

Run the routine using ADODB objects.

You can ues the CopyFromRecordset method to 'append' each recordset in the next empty cell on your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

SkipVought,

I've never used ADO objects before. Why do you suggest using them instead of simply pasting the obtained values on the next line?

Thanks!

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Oh, I guess you are using another access method. That's fine.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

SkipVought,

I'm using OPC objects to interact with the control system. They have methods to get the data. The OPC objects are VBA classes defined by an industry standard set by the OPC foundation.

But what were you thinking with the ADO objects? I'm interested to hear more. After all, I may need to transfer the information to a historical database in the near future.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

ActiveX Data Objects can access databases. You can either navigate a table, accessing row by row or execute a query against one or more tables in a database, returning a recordset as defined by your criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}