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.
- PI Web API 2017 installed within your domain using Kerberos or Basic Authentication.
- PI ProcessBook 2012 SP1+
- .NET Framework 3.5
- 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.
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.
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.
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:
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.
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", "", "",
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?