A
Anders Frafjord
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
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