Excel 插件‘Aspen process data ‘在VBA中的基本使用

获取当前值Current process data (single value)

References

打开VBA编辑器，找到References勾选需要的插件

需要额外勾选的插件有：
AspenProcessDataAddin
Aspen DataSource Locator
Aspen Process Data

如下图所示

读取当前值

'create a new IP21 DataSource object 
Private IP21DataSources As New AtProcessData.DataSources

Public Const VAR_Server As String = "IP21-XXXXXXXX"'IP21 server name
Public Const ATTR_IP_INPUT_VALUE As String = "IP_INPUT_VALUE"
'attribute name of 'Current Value' in IP21 server

Public Function ReadTagValue(sTag As String, sAttribut As String) As Variant
    ' Read a tag for a given attribute
    ' return "" if nothing found or error

    Dim resu As Variant 'Variant for output
    Dim oAtDataSource As AtProcessData.DataSource
    Dim oAtTag As AtProcessData.Tag
    Dim oAtAttr As AtProcessData.Attribute

    resu = ""
    Set oAtDataSource = IP21DataSources.Item(VAR_Server)
    'Set DataSource
    Set oAtTag = oAtDataSource.Tags.Add(sTag)
    'Set Tag Name
    Set oAtAttr = oAtTag.Attributes.Add(sAttribut)
    'Set Attribute Name

    oAtTag.Attributes.Query.UseCurrentTime = True
    'Set Time as current time
    oAtTag.Attributes.Read False
    'Enable to read attribute value

    If (oAtAttr.Valid = True) Then
        If oAtAttr.Value = "" Then
        	'If failed to fetch current value, return ''
            ReadTagValue = resu
        	Exit Function
        Else
        	'Get current value
            resu = (oAtAttr.Value)
        End If
    Else
    	'If failed to recognize the attribute name, return ''
        ReadTagValue = resu
        Exit Function
    End If

    oAtDataSource.Tags.RemoveAll
    Set oAtDataSource = Nothing
    Set oAtAttr = Nothing
    Set oAtTag = Nothing

    ReadTagValue = resu
    'return Output value

End Function

Sub test()
Cells(1, 1).Value = ReadTagValue("XXXXXXXXX", ATTR_IP_INPUT_VALUE)
End Sub

Note:
1、获取当前时间值
oAtTag.Attributes.Query.UseCurrentTime = True
2、获取某时间点的值
oAtTag.Attributes.Query.Time = Format(“2021/07/01 00:00:00”, “yyyy/MM/DD HH:mm:ss”)

获取历史趋势Historic process data list

References

打开VBA编辑器，找到References勾选需要的插件

需要额外勾选的插件有：
AspenProcessDataAddin
Aspen DataSource Locator
Aspen Process Data

如下图所示

获取历史值列表

'create a new IP21 DataSource object
Private IP21DataSources As New AtProcessData.DataSources

Public Const VAR_Server As String = "IP21-XXXXXXX" 'IP21 server name
Public Const ATTR_IP_INPUT_VALUE As String = "IP_INPUT_VALUE" ''attribute name of 'Current Value' in IP21 server
Public Const ValueList_MaxNum As Integer = 500 ' maximum capacity of history value list
Public Const IntervalPeriod As Integer = 2 'interval period between each history data
Dim HistoryCap As Variant 'real capacity of history value list


Sub ReadTagHistory(sTag As String, sAttribut As String, sStartTime As Date, sEndTime As Date, HistoryOutput() As Double)
    ' Read a list of historic tag value for a given attribute
    ' Output to an externally defined list variable
    ' return "" if nothing found or error
    Dim i As Integer
    Dim oAtDataSource As AtProcessData.DataSource
    Dim oAtTag As AtProcessData.Tag
    Dim oAtAttr As AtProcessData.Attribute
    Dim oHistory As AtProcessData.History
    
    'Set DataSource
    Set oAtDataSource = IP21DataSources.Item(VAR_Server)
    'Set Tag Name
    Set oAtTag = oAtDataSource.Tags.Add(sTag)
    'Set Attribute Name
    Set oAtAttr = oAtTag.Attributes.Add(sAttribut)
    'Set History list object
    Set oHistory = oAtTag.History
    
    'Settings of history data filtter
    oHistory.Query.BeginTime = sStartTime
    oHistory.Query.EndTime = sEndTime
    oHistory.Query.Extrapolate = False
    oHistory.Query.DetermineInterpolationStart = False
    oHistory.Query.Period = IntervalPeriod 'interval period between each history data
    oHistory.Query.PeriodUnits = apdHour ' unit of interval period time
    oHistory.Query.Method = apdValue
    oHistory.Query.Start = apdStartTime
    oHistory.Query.Stepped = False
    oHistory.Query.MaxPoints = ValueList_MaxNum
    oHistory.Query.Type = apdInterpolated
    
    'Get history value
    oHistory.Read False
    oAtTag.Attributes.Read False
    
    'Fill the history value list
    HistoryCap = 0
    If oHistory.Samples.Count = 0 Then
        If oAtAttr.Value = "" Then
        End If
    Else
        For i = 1 To oHistory.Samples.Count
            If oHistory.Samples(i).Value = "" Then
            Else ' If value is not NULL
                HistoryCap = HistoryCap + 1 'add the capacity of history value list
                HistoryOutput(HistoryCap) = oHistory.Samples(i) 'save the value in the list
            End If
        Next
    End If
    
    oAtDataSource.Tags.RemoveAll
    Set oAtDataSource = Nothing
    Set oAtAttr = Nothing
    Set oAtTag = Nothing
    Set oHistory = Nothing

End Sub

Public Function AddHour(ByVal sTime As String, sAddNum As Integer) As String
'Add X hours to a Time string
    Dim dt As Date
    dt = CDate(sTime)
    dt = DateAdd("h", sAddNum, dt)
    AddHour = Format(dt, "YYYY/MM/DD hh:mm:ss")
End Function

Sub test()
    Dim ValueList(ValueList_MaxNum) As Double
    Dim index As Integer
    Dim Start_Time As Date, End_Time As Date
    Start_Time = Format("2021/07/01 00:00:00", "YYYY/MM/DD hh:mm:ss")
    End_Time = Format("2021/07/02 00:00:00", "YYYY/MM/DD hh:mm:ss")
    Call ReadTagHistory("XXXXXXXXX", ATTR_IP_INPUT_VALUE, Start_Time, End_Time, ValueList)
    Cells(1, 2) = "XXXXXXXXX"
    For index = 1 To HistoryCap - 1 'print result except value at End Time
        Cells(index + 1, 1) = AddHour(Start_Time, (index - 1) * IntervalPeriod)
        Cells(index + 1, 2) = ValueList(index)
    Next
End Sub

获取历史值列表运行结果

Note

时间计算函数——小时增加

输入时间字符串和增加的小时数，输出一个计算后的时间字符串

Public Function AddHour(ByVal sTime As String, s