Logging in Excel

[Koen]

Hello,

I am trying to log data which is suplied by an OPC client (S7 - 200 PC Access). I managed to show the data in a cell, i.e. A1.

Every time the data is changed, i want to write the data to the cell below the last one (A2 then A3 then A4 ....). But it's really important that the logging continues when I'm manualy writing some random things in any other cell. The interval between a data change is different every time.

http://excel.bigresource.com/Track/excel-lIHqe5IW/ helped me a little, but there is still the problem that the logging is not independent of what else I am doing in excel. (when I'm writing in a cell, data which comes in at that moment is lost or skipped.)

I hope someone can help me with this.

(maybe, if it's usefull, I do have an OPC item (boolean) which changes from 0 to 1 every time the data I need to log changes.)

Best Regards,

Koen

 

[Koen]

So my question actualy is, how can I create a macro which runs constantly from when I hit a button. And keeps running when i'm writing in a cell or going to another sheet or whatever..

Can someone please help me with this?

Thankyou very much.

 

[Ruddles]

As far as I'm aware this isn't possible: editing a cell will suspend any VBA activity in the current instance of Excel.

Can you run the data collection in a separate instance of Excel and fetch the data from there into the one you're editing?

 

[Koen]

Hi there, thanks for the reaction.

Is it possible to build something where when something happens my cursor moves out of the cell I am in at that moment.

that would solve my problem to.

And by 'something happens' I mean:

Private Sub MyOPCGroup_DataChange(ByVal TransactionID As Long, _
                                    ByVal NumItems As Long, _
                                    ClientHandles() As Long, _
                                    ItemValues() As Variant, _
                                    Qualities() As Long, _
                                    TimeStamps() As Date)

 

[Ruddles]

Npo, because editing a cell will suspend any VBA activity in the current instance of Excel, so the code to do this wouldn't be executing whilst you were 'in' the cell.

Have you considered running the data collection in a separate instance of Excel?

 

[Koen]

do you mean in another sheet or a whole new workbook? And do you mean reading the livefeed into that other sheet and then copying it to sheet1 with a button or so?
That would be fine for me, but I'm a VBA dummie.

I don't know how to copy a constantly expanding range of data from sheet 2 to sheet 1. And how can I copy only the data that is added after the previous copy?

thanks

 

[Ruddles]

You need to confirm this yourself but I believe it needs to be not just a completely different workbook because editing a cell will suspend any VBA activity in the current instance of Excel, but a distinct instance of Excel - another copy of Excel running in a different window which appears in Task Manager as a completely separate task.

Try this test. Open another workbook in the same instance of Excel as your logging workbook and try to edit a cell in that. I believe your logging will stop in the same way as it did when you tried to edit a different sheet in the same workbook.

Now try this: check there's only one EXCEL.EXE visible in Task Manager > Processes. Open another instance of Excel and make sure it shows as a separate EXCEL.EXE in Task Manager. Create a workbook and start editing a cell in that: your logging should continue unaffected.

My first thought was for the logging workbook to copy each data item to an Access database as part of the logging process, i.e. as it adds it to the worksheet. The 'other' workbook could check the database periodically - perhaps in the Worksheet_Change or Worksheet_Calculate event handler or via a timed event (using Application.OnTime) or via a command button. Any new data it finds can be copied into thew 'other' workbook and deleted (or marked as 'read') in the Access database.

It's not a trivial piece of coding if you need make it totally robust, but neither is it impossible. Someone is going to have to bring some VBA skills to bear on it, certainly. I imagine you wouldn't like data to go missing so some thought will have to be given as to how the two workbooks will 'contend' in the shared database - that is, resolve any issues which occur because one process is trying to add something whilst the other is trying to delete something.

I assume this is for work rather than personal amusement. Do you have any IT or programming people who can assist you? Or any sort of budget to get the task completed?

 

[Ruddles]

I've just been discussing this with one of the other guys at work and he's thinking along the same lines as I am.

On the other hand I'm always happy to receive constructive criticism, so if anyone disagrees, please don't hesitate to say so!

(In fact I helped write a system many years ago where one process sporadically produced small CSV files and another process kept a watch on a folder and imported them as they appeared, then deleted them. We had to be careful resolving the contention but it seemed to work okay.)

 

[RoryA]

It is in fact possible to run VBA code while in edit mode using windows timers, but it is generally not regarded as a good idea. If you search the forum for Jaafar's in-cell clock, you will see what I mean.