Problem using RTD in EXcel 2003
Hi,
I'm trying to read values every second from an RTD server to an Excel
spreadsheet using a macro. Problem is when I run the macro, values from
the RTD server is not updated. I've tried to set the calculation mode to
manual, and then force a calculation everytime I want to read data, but
it doesn't work. It gives me the same value all the time. Here's the
code I'm using:
Public Sub Start_Logging()
Dim x As Integer
Dim intNewHour As Integer
Dim intNewMinute As Integer
Dim intNewsecond As Integer
Dim varWaitTime As Variant
blnLogg = True
x = 1
Application.Calculation = xlCalculationManual
Worksheets("Sheet1").Activate
ActiveSheet.Cells(1, 1).Select
Do
'Set logging to every second
intNewHour = Hour(Now())
intNewMinute = Minute(Now())
intNewsecond = Second(Now()) + 1
varWaitTime = TimeSerial(intNewHour, intNewMinute, intNewsecond)
Application.Wait varWaitTime
'Do full calculation
Application.CalculateFull
'Write new value to cells
ActiveSheet.Cells(x, 1).Value = TIME
ActiveSheet.Cells(x, 2).Value = ActiveSheet.Range("e2").Value
x = x + 1
DoEvents
Loop Until blnLogg = False
Application.Calculation = xlCalculationAutomatic
End Sub
If I place a comment mark in front of the "do" and "do until..." and
"application.calculation....", so I have to run the macro everytime to
read/write values, it's working. So the problem has something to do with
the Do...Loop.
The blnLogg is a flag I use, so I can stop updating pressing another
button, setting the flag to false.
Running Windows XP SP2, Excel 2003.
Regards,
Anders Frafjord
|