5 Replies Latest reply on Nov 9, 2018 3:28 PM by stuart.watson

    MSExcel VBA using Datalink Functions


      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

          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
              Set rs = Nothing
              Set cn = Nothing
          End Sub


          Please let us know,

            • Re: MSExcel VBA using Datalink Functions


              We need to extract data from PI database and using VBA code to do that (VBA code in remote machine). But the connection string is not going through and getting the error.


              Automation Error

              Unspecified Error

              Error num# 80004005


                    strConnection = "Provider=PIOLEDB;Data Source=megatron;Time Zone=Server"


              Used same way as shown in above example. Below are 2 more ways I have tried

              a) conn.ConnectionString = "Provider=PIOLEDB;Data Source=Datamining;Integrated Security=SSPI;"

              b) conn.ConnectionString = "Provider=PIOLEDB;Data Source=Datamining;User Id=<username>;Password=<pwd>;"


              We are able to connect from excel sheet using Data -> From Other Source -> Data Connection Wizard / From Microsoft query -> Select DSN -> give password -> Connect.  ( user id we use is like .\Admin, same we are using in connection string )


              Appreciate any help. Thank you.

                • Re: MSExcel VBA using Datalink Functions

                  Try running the excel as admin and try again. Excel is usually very strict with macros and permissions especially when you reaching another server to get data. So sometimes you need to start excel as admin to be able to run some VBA codes.

                    • Re: MSExcel VBA using Datalink Functions

                      Thank you Jinmo. Will try that way and let know. But the connection string syntax looks fine ?

                      I see different provider names like PIOLED, PIOLED.1 etc. and DSN name is system DSN name that am using while connecting from excel (path as given below).

                      Data -> From Other Source -> Data Connection Wizard / From Microsoft query -> Select DSN -> give password


                      Or shall I give server name as DSN name ?


                      All these looks fine ?

                • Re: MSExcel VBA using Datalink Functions

                  To call the PI DataLink functions from VBA, you need to use "Application.Run" - here are some examples.


                                  vRawData = Application.Run("PITimeDat", p.AFAttribute, dteTimeStamps, p.AFRoot, "interpolated")

                                  vRawData = Application.Run("PINCompDat", p.AFAttribute, p.oStartTime.LocalDate, nTime, 1, p.AFRoot, "outside")

                                  vRawData = Application.Run("PITimeDat", p.AFAttribute, dteTimeStamps, p.AFRoot, "interpolated")

                                  vRawData = Application.Run("PITimeExpDat", "prevval('" & p.AFAttribute & "','*+0.001s')", dteTimeStamps, p.AFRoot, "interpolated")

                                  vRawData = Application.Run("PICompDat", p.AFAttribute, p.oStartTime.LocalDate, p.oEndTime.LocalDate, 1, p.AFRoot, "outside")


                  Typically a 2D array is returned, although I think it depends on the function, and whether there were errors or not.

                  And I am fairly sure the arrays are 1 indexed (not 0) - but I would verify for yourself.