AnsweredAssumed Answered

MSExcel VBA using Datalink Functions

Question asked by John_Fors on Nov 17, 2016
Latest reply on Nov 9, 2018 by stuart.watson

I'm trying to create a report using Excel as the visualization tool.  The report is driven by an AF hierarchy and the summation data is queried from the AF-Attributes from different levels in the AF hierarchy.  I've been using Excel VBA to get most of the data required by using a referenced library (OSIsoft_PIDatalink_AFData).  From the library I've successfully called the "AFSearch" to get lists of elements by category and AFPutVal where I had some back fill requirements. 

Example Snip:********

Public DataLink As New AFLibrary

Dim ElementList As Variant

ElementList = DataLink.AFSearch(AFDBName, "*", 0, "*", Category, "*", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", 0)

*******this works*******


I'm now trying to get the value from a PIPoint referenced attribute for two different points in time, Start of report Period and End of report period.  I've tried using the AFArcVal or AFTimeDat functions from the above library but have not been able to reverse engineer all the calling parameters.  Getting errors like "Unable to cast object of type 'system.Int32' to type 'System.Array' ".  Previous versions of Datalink allowed me to call the spreadsheet functions directly from VBA. The spreadsheet version of the function would be "PIArcVal" and use to be able to call like:

Dim Val as Variant

Val =PIArcVal("\\afServername\AFDbName\Root\Level2ID|HeartBeat_FaultCnt","*-1d",1,"","auto")


But VBA cannot resolve the function reference to make the call. 

What alternatives would be available to get an archive value from an attribute reference for a point in time using VBA?