1 Reply Latest reply on Nov 17, 2016 7:16 PM by pthivierge

    MSExcel VBA using Datalink Functions

    John_Fors

      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?

        • Re: MSExcel VBA using Datalink Functions
          pthivierge

          Hello John,

           

          Can you give us a little background on what you are trying to achieve?

          What this report will be used for? how many person will need to look at this report?

           

          The PI Datalink Methods you are trying to use are not meant to be publicly available so this is not something I can help you with.

           

          One alternative could be to use PI Web API from VBA.

          How to use PI Web API with VBA - Introduction

           

          Another one would be to use PI OLEDB Enterprise from Excel.

          It requires the OLEDB Driver to be installed on the client, this adds an extra installation step each time you want someone to be able to consume your VBA code.

          Below is an example with the OLEDB Provider Classic, you will need a different connection string ( strConnection ) to connect to PI OLEDB Enterprise.

          Public Sub GetPIOLEDBData()
              Dim cn, rs As Object
              Dim strSql As String
              Dim strConnection As String
          
              Set cn = CreateObject("ADODB.Connection")
              strConnection = "Provider=PIOLEDB;Data Source=megatron;Time Zone=Server"
              strSql = "SELECT * from piarchive..pisnapshot where tag='sinusoid'"
              cn.Open strConnection
              Set rs = cn.Execute(strSql)
              Do Until rs.EOF
                  Debug.Print rs!Tag, rs!Time, rs!value, rs!Status
                  rs.MoveNext
              Loop
              rs.Close
              Set rs = Nothing
              cn.Close
              Set cn = Nothing
          
          
          End Sub
          

           

          Please let us know,