7 Replies Latest reply on Aug 22, 2016 12:39 PM by cjcoronel24

    Pull Data into VBA Processbook

    cjcoronel24

      Hi All,

       

      So I would like to use Process book VBA in order to run a for loop in the background.  My question is, " Is there a way to pull a pi tag and make it into an array behind the scenes without any value or trend on the display?"

       

      My end game is this.  I want to have 3 buttons, one for daily, one for weekly and one for monthly.  When the weekly button is clicked, the time range specified here (7 days) will run through a Pi Point (availability) and will pull to a list box all the times the availability dropped a certain value. 

       

      So again I will need to know how to pull a pi tag and store the values into an array in vba processbook.  I have done this in excel which is easy enough however having difficulties with Processbook.

       

      Thank you in advance for your help!

        • Re: Pull Data into VBA Processbook
          gonmerciel

          Hi Cristopher,

           

          You can reference PISDK and use it to get RecordedValues for a tag, specifying the filter you like (or getting all values and filter them yourself). Here you can find and example, and here you have the reference for that specific method.

           

          Let me know if you need help getting it to work.

           

          Gonzalo

            • Re: Pull Data into VBA Processbook
              cjcoronel24

              Gonzalo,

               

              Thank you for the help.  This is EXACTLY where I want to go with the code.  However I am having difficulties with accessing PISDK.  Although I have some VBA experience, I am very new to declaring objects.  Perhaps you can add some more insight on this.

               

              Thank you again

               

              Chris

            • Re: Pull Data into VBA Processbook
              s199146

              Christopher,

               

              Here is an example:

               

              Private Sub getData()

                  'Connects to a PI server and retrieves data

                  Dim srv As PISDK.Server

                  Dim ptlist As PISDK.PointList

                  Dim pt As PISDK.PIPoint

                 

                  Set srv = Servers("server name")

                 

                  'connect to PI server

                  srv.Open

                 

                  'build a list of PI points

                  Set ptlist = New PISDK.PointList

                  ptlist.Add srv.PIPoints("sinusoid")

                  ptlist.Add srv.PIPoints("sinusoidu")

                  ptlist.Add srv.PIPoints("cdt158")

                   'keep adding points here;

                     

                  'loop through each point

                  For Each pt In ptlist

                      MsgBox pt.Name & vbCrLf & pt.Data.Snapshot ' insert into the listbox here

                  Next

                     

                  'Close connection

                  srv.Close

                 

                  Set ptlist = Nothing

                  Set srv = Nothing

              End Sub

               

              Notes:

              1) Make sure you add a reference to the PISDK 1.3 Type Library

              2) Use some error trapping technique when adding your points to the list because it will raise an error if the point being added doesn't exist in PI.

              3) Check out the documentation normally found at "C:\Program Files (x86)\PIPC\HELP\pisdk.chm".

               

              Hope this helps,

              Paulo C.

              1 of 1 people found this helpful
                • Re: Pull Data into VBA Processbook
                  cjcoronel24

                  Paulo!  Thank you for the help this worked tremendously.

                   

                  Now I have a follow up question.  So now that I am able to bring data in vba I ran an if statement if the inverters I have online are less than 10 to spit out the value.  Below is a snippet of what I have down.  Now I would like to make this smarter.  I see the code you reference uses “For Each” which I assumes goes through the whole date in chronological order.  I want to say

                   

                  If pv < 10 Then

                                  Hold value

                  While the pc< is still less than 10

                  Skip to the next value.

                  If you read pv=10

                  Then take the time stamp from when it was 9 and the time stamp when it was 10 and record it on the list.

                   

                  Is that clear?  Let me know if you need for clarification.

                   

                  Thanks,

                  Chris

                  • Re: Pull Data into VBA Processbook
                    cjcoronel24

                    Follow up though is it possible to take all those recorded values and put them into an array?  I think then it’ll be easy to perform the if statement I want to implement.

                     

                    Thanks,

                    Chris

                      • Re: Pull Data into VBA Processbook
                        Eugene Lee

                        Hi Chris,

                         

                        Here is some sample code to put them into an array.

                         

                        Dim srv As Server
                        Dim pt As PIPoint
                        Set srv = servers.DefaultServer
                        Set pt = srv.PIPoints("cdt158")
                        Dim vals As PIValues
                        Set vals = pt.Data.RecordedValues("*-10s", "*")
                        ReDim arr(vals.Count) As PIValue
                        i = 1
                        For Each v In vals
                        Set arr(i) = v
                        i = i + 1
                        Next v
                        
                        
                        'Print value 1,4,7
                        Debug.Print arr(1).Value & " " & arr(1).TimeStamp.LocalDate
                        Debug.Print arr(4).Value & " " & arr(4).TimeStamp.LocalDate
                        Debug.Print arr(7).Value & " " & arr(7).TimeStamp.LocalDate
                        
                        1 of 1 people found this helpful