|
|
Rank: Member
Groups: Member
Joined: 1/24/2012
Posts: 10
Location: mjcordeiro
|
Hello, I'm trying to use PI System with Microsoft Excel VBA. What I want to do is: Get a value in a certain date and compare that value with the current one. This will help us because when we are starting up our machine It's good to compare some variables with the last good startup. A practical exercise: Last Good startup: Start time: "2012-01-01 02:00:00" End Time: "2012-01-01 05:00:00" Variable name: "POWER" Now, I will create a chart in Excel With "POWER" variable and refresh the chart each 5seconds. How can I get "POWER" values and list them in a excel sheet with 5seconds of interval? How can I get a instant value of "POWER"? Any help is welcome I have this code, but I'm not sure this does what I want (I have doubts about Interval, for example): Quote:
Dim srv As Server
Dim TagName As String
Dim FilterString As String
Dim st As New PITimeFormat
Dim et As New PITimeFormat
Dim PIArray, PIArray1 As PIValues
Dim PIvalue As PIvalue
Set srv = Servers("myserver")
TagName = "POWER"
st.InputString = Format(Start, "dd-mm-yyyy h:mm:ss") ' Start time
et.InputString = Format(End1, "dd-mm-yyyy h:mm:ss") ' End time
FilterString = "TagVal('" & TagName & "')"
Set PIArray = srv.PIPoints(TagName).Data.RecordedValues(st, et, , FilterString, fvRemoveFiltered)
If PIArray.Count <> 0 Then
TimeOfValue = ""
Line = 5
Column = 2
For Each PIvalue In PIArray
TimeOfValue = TimeOfValue & Chr(10) & Chr(13) & PIvalue.TimeStamp.LocalDate
Cells(Line, Column).Value = PIvalue
Line = Line + 1
Next
MsgBox "You have " & PIArray.Count & " values in:" & TimeOfValue
Else
MsgBox "You have't values."
End If
regards, Mário
|
|
|
|
|
OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System. We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ or contact OSIsoft vCampus for more details.
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 4/7/2011
Posts: 137
Location: KZN, South Africa
|
hi mjcordeiro
Don't you have datalink in Excel to do this for you.
|
|
|
Rank: Member
Groups: Member
Joined: 1/24/2012
Posts: 10
Location: mjcordeiro
|
Hello squatty,
I'm sorry but didn't understand your answer. Are you asking or are you telling me?
In a excel sheet I have this made with PI Addin:
Start Date: 2012-01-02 02:00:00
End Date: 2012-01-02 05:00:00
Step: 5s
Tag: "POWER"
After I use the addin, it will fill a lots of cells with the timestamp and the respective value.
The question is how can I do the same thing but using Excel VBA?
I've tried to record a macro, but didn't work :(((
Thank you in advance,
Mário
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 4/7/2011
Posts: 137
Location: KZN, South Africa
|
Soory for the "dirty" code but I tried this and it worked.
Sub TEST()
Dim srv As Server
Dim TagName As String
Dim FilterString As String
Dim st As New PITimeFormat
Dim et As New PITimeFormat
Dim PIArray, PIArray1 As PIValues
Dim PIvalue As PIvalue
Dim ipid2 As IPIData2
Dim pdata As PIData
Dim pt As PIPoint
Set srv = PISDK.Servers.DefaultServer
TagName = "sinusoid"
st.InputString = Format(DateAdd("h", -12, Now()), "dd-mmm-yyyy h:mm:ss") ' Start time
Cells(2, 11).Value = st.InputString
et.InputString = Format(Now(), "dd-mmm-yyyy h:mm:ss") ' End time
Cells(3, 11).Value = et.InputString
Calculate
FilterString = "TagVal('" & TagName & "')"
Set pt = srv.PIPoints(TagName)
Set pdata = pt.Data
Set ipid2 = pdata
Set PIArray = ipid2.InterpolatedValues2(st, et, "5s")
Debug.Print PIArray.Count
If PIArray.Count <> 0 Then
TimeOfValue = ""
Line = 5
Column = 2
For Each PIvalue In PIArray
Debug.Print PIvalue.Value & vbTab & PIvalue.TimeStamp.LocalDate
TimeOfValue = TimeOfValue & Chr(10) & Chr(13) & PIvalue.TimeStamp.LocalDate
Cells(Line, Column).Value = PIvalue
Line = Line + 1
Next
MsgBox "You have " & PIArray.Count & " values in:" & TimeOfValue
Else
MsgBox "You have't values."
End If
End Sub
|
|
|
Rank: Member
Groups: Member
Joined: 1/24/2012
Posts: 10
Location: mjcordeiro
|
Thanks for your help. I've tried and it works fine  )) Is there any function to calculate: * Average * Minimum value * Maximum value of the collected data? Or I have to do it "manually"? Thanks in advance
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 4/7/2011
Posts: 137
Location: KZN, South Africa
|
Glad we could help.
You can try ipid2.Summary(st,et,calctype)
|
|
|
Rank: Member
Groups: Member
Joined: 1/24/2012
Posts: 10
Location: mjcordeiro
|
Thank you soooo much skatty.
regards,
Mário
|
|
|
|
Guest
|
