Introduction

 

Although it is considered an old technology, there are still many developers who use VBA to integrate their PI ProcessBook displays or Excel spreadsheets with the PI System. Since our most performant PI Developer Technology is PI AF SDK, which is a native .NET Framework, it cannot be used within the VBA environment. One option is to use PI SDK in VBA in order to communicate with the PI Data Archive. Nevertheless, if those developers want to work with elements, attributes or event frames, there wasn't any really good alternative.

 

Today we are releasing the first version of the PI Web API Wrapper for VBA, which is a client RESTful web service for PI Web API 2017. With this library, almost all methods available on PI Web API 2017 can be called through this library. This means you can get any element description, retrieve its attributes, search for event frames, create new PI Points, sends and get data in bulk within the VBA context.

 

 

It is always good to remember that PI Vision is the most suitable technology for any new development projects. This wrapper should be used only when PI Vision cannot be used for any reason.

 

Requirements

 

  • PI Web API 2017 installed within your domain using Kerberos or Basic Authentication.
  • PI ProcessBook 2012 SP1+
  • .NET Framework 3.5

 

Installation

 

  • Download the latest release from our GitHub repository
  • Create a new folder under %PIHOME% named WebAPIClient, if it doesn't exist.
  • Create a new folder under WebAPIClient named VBA, if it doesn't exist.
  • Copy all files from the dist folder to %PIHOME%\WebAPIClient\VBA.
  • Run as Administrator the reg.bat located on %PIHOME%\WebAPIClient\VBA in order to register the PIWebApiWrapper assmebly.

 

Usage

 

Create or edit a PI ProcessBook display. Press ALT+F11 to open Visual Basic for Applications. On the menu, click on Tools --> References. Find PIWebApiWrapper on the list box of the available reference and add it to the VBA project.

 

 

Source Code

 

The Visual Studio solution that generates the final library is available on the src folder. You might want to add or edit a method and rebuild the solution in order to generate custom assemblies.

 

Documentation

 

All classes and methods are described here on GitHub. You can also use the Object Browser from Visual Basic for Application to read the same information. Please refer to the screenshot below:

 

 

Remember

 

As this is a .NET library with COM objects and methods exposed, in order to be able to be consumed within the VBA environment, there are some things to have in mind, especially when comparing with C# development.

  • VBA is not compatible with async methods. Therefore, only sync methods are available in this library.
  • For each PI Web API action/method of each controller, there are two methods on this client library. One returns the response of the HTTP request itself and the other wraps the response on top of ApiResponse class, providing http information, such as status code. Please refer to the Get and GetWithHttpInfo methods on our documentation and you will realize the difference between them by comparing the method signature.
  • The Batch and Channel controllers are not exposed.
  • When working with data transfer objects (models) with an Items property (such as PIItemsElement), do not access or modify this property directly. Use CreateItemsArray(), GetItem(), SetItem() and GetItemsLength() instead.
  • For models that have the Value property, use SetValueWithString(), SetValueWithInt(), SetValueWithDouble() methods to set this property.
  • For the Api methods, all variables whose type are not a string must be defined. If a string variable is optional, define it as an empty string instead of Null.

 

Examples

 

There are two PI ProcessBook displays available on the Samples folder of this repository. In addition, please refer to the following examples to understand how to use this library:

 

Create an instance of the PI Web API top level object.

 

    Dim client As New PIWebApiClient
    Dim connectedToPIWebAPI As Boolean
    connectedToPIWebAPI = client.Connect("https://marc-web-sql.marc.net/piwebapi", True)

 

If you want to use basic authentication instead of Kerberos, set useKerberos to False and set the username and password accordingly. We recommend using Kerberos because it is the safest option. For basic authentication, the password needs to be hardcoded which is not recommended. If using Kerberos authentication is not an option, protect your VBA code with a password.

 

Get the PI Data Archive WebId

 

    Set dataServer = client.dataServer.GetByName(tbPIDataArchiveName.Text)

 

Create a new PI Point

 

    Dim response As ApiResponseObject
    Dim newPIPoint As New PIPoint
    newPIPoint.Name = "MyNewPIPoint"
    newPIPoint.Descriptor = "Point created for wrapper test"
    newPIPoint.PointClass = "classic"
    newPIPoint.PointType = "Float32"
    Set response = client.dataServer.CreatePointWithHttpInfo(dataServer.webId, newPIPoint)

 

Get PI Points WebIds

 

    Set point1 = client.point.GetByPath("\\" + tbPIDataArchiveName.Text + "\" + tbTagName1.Text)
    Set point2 = client.point.GetByPath("\\" + tbPIDataArchiveName.Text + "\" + tbTagName2.Text)
    Set point3 = client.point.GetByPath("\\" + tbPIDataArchiveName.Text + "\" + tbTagName3.Text)

 

Get recorded values in bulk using the StreamSet/GetRecordedAdHoc

 

    webIds = point1.webId + "," + point2.webId + "," + point3.webId
    Set compressedData = client.StreamSet.GetRecordedAdHoc(webIds, True, 1000)

 

Send values in bulk using the StreamSet/UpdateValuesAdHoc

 

 

    Call GetPIPoints
    Dim streamValuesItems As New PIItemsStreamValues
    Dim streamValue1 As New PIStreamValues
    Dim streamValue2 As New PIStreamValues
    Dim streamValue3 As New PIStreamValues
    Dim value1 As New PITimedValue
    Dim value2 As New PITimedValue
    Dim value3 As New PITimedValue
    Dim value4 As New PITimedValue
    Dim value5 As New PITimedValue
    Dim value6 As New PITimedValue


    streamValuesItems.CreateItemsArray (3)
    value1.SetValueWithInt (2)
    value1.Timestamp = "*-1d"
    value2.SetValueWithInt (3)
    value2.Timestamp = "*-2d"
    value3.SetValueWithInt (4)
    value3.Timestamp = "*-1d"
    value4.SetValueWithInt (5)
    value4.Timestamp = "*-2d"
    value5.SetValueWithInt (6)
    value5.Timestamp = "*-1d"
    value6.SetValueWithInt (7)
    value6.Timestamp = "*-2d"


    streamValue1.webId = point1.webId
    streamValue1.CreateItemsArray (2)
    Call streamValue1.SetItem(0, value1)
    Call streamValue1.SetItem(1, value2)
    Call streamValuesItems.SetItem(0, streamValue1)


    streamValue2.webId = point2.webId
    streamValue2.CreateItemsArray (2)
    Call streamValue2.SetItem(0, value3)
    Call streamValue2.SetItem(1, value4)
    Call streamValuesItems.SetItem(1, streamValue2)


    streamValue3.webId = point2.webId
    streamValue3.CreateItemsArray (2)
    Call streamValue3.SetItem(0, value5)
    Call streamValue3.SetItem(1, value6)
    Call streamValuesItems.SetItem(2, streamValue3)

    Dim response As ApiResponsePIItemsItemsSubstatus
    Set response = client.StreamSet.UpdateValuesAdHocWithHttpInfo(streamValuesItems)

 

Get AF Attribute given an AF Element path

 

    Set elem = client.element.GetByPath(ERD.CurrentContext(ThisDisplay))
    ElemDesc.Contents = elem.Description
    Dim attributes As PIItemsAttribute
    Set attributes = client.element.GetAttributes(elem.webId, 1000, False, False, False, 0)

 

Get current value given an AF Attribute path

 

  attributePath = ERD.CurrentContext(ThisDisplay) + "|" + AttrList.Text
    Set attr = client.attribute.GetByPath(attributePath)
    Set timedValue = client.Stream.GetEnd(attr.webId)
    AttrValue.Contents = timedValue.value

 

Get Event Frames given an AF database path

 

Set db = client.AssetData.GetByPath(dbPath)
Set efs = client.AssetData.GetEventFrames(db.webId, False, False, 100, True, 0, "", "*", "", elem.Name, elem.templateName, "", "", "None", "", "",

 

 

Final Remarks

 

This library will be updated for every new release of PI Web API in order to add the methods on this library which were added to the PI Web API release.

 

Please share your comments and thoughts about this new release! Will PI Web API Wrapper be useful in your upcoming projects?