5 Replies Latest reply on Aug 14, 2016 3:48 AM by Rick Davin

    How to Pull a Sample Tag in VBA Excel?

    cjcoronel24

      Hey All,

       

      I want to pull a PI Tag in VBA Excel and add the Timestamp and value into a nx2 array with column 1 being the time stamp and column 2 being the value.  The Pi Tag will be sampled at every 5 seconds so this will have to be stored in a really long array.  Does anyone have an idea how to do this.

        • Re: How to Pull a Sample Tag in VBA Excel?
          gregor

          Hello Christopher,

           

          You could use PI DataLink to pull your 5 second samples to Excel.

          What do you refer to with nx2 array? Is that an array of cells? If so, this is what PI DataLink creates when you pull PI data.

          You mention that the array will have to be "long". What's the time period you envision to query?

            • Re: How to Pull a Sample Tag in VBA Excel?
              cjcoronel24

              Thanks for your response, I rather have it in VBA since I wish to make an automated program and rather not have all values display in excel and then mapping them to arrays. It all depends on how many days has elapsed since the last updated I envision a week tops so 7 x24x60x12 array

               

               

              Sent from my iPhone

                • Re: How to Pull a Sample Tag in VBA Excel?
                  Rick Davin

                  Could you clarify that you really want 5 second data versus 5 minutes?  Your original post stated 5 seconds.  The formula 7x24x60x12 suggests 5 minutes.  If it is 5 minutes, perhaps you could edit your original post to reflect that.

                    • Re: How to Pull a Sample Tag in VBA Excel?
                      cjcoronel24

                      Hey Rick,

                       

                      It's 5 second data I am needing. Let me break down the formula listed below for clarity

                      7days x 24hours/day x 60min/hour x 60 seconds / min x 1 sample/ 5 seconds.

                       

                      The last two values 60/5 equals 12 which is I am getting 12 samples per minute since the sample rate is 5 seconds.

                       

                      Hope this clarifies your question.

                       

                      However I feel that your question is missing the overall goal of my post, to be able to pull a pi point in VBA regardless of sampling rate.

                       

                      Please let me know if you have any guidance.

                       

                      Thanks,

                       

                      Chris

                       

                      Sent from my iPhone

                        • Re: How to Pull a Sample Tag in VBA Excel?
                          Rick Davin

                          Sorry.  You're right about the 5 seconds.  Mental error on my part.  I blame it on watching the Olympics while reading forums.

                           

                          Since you are using Excel VBA, you are limited to the legacy, COM-heavy PISDK, which is expected to be deprecated in the future.

                           

                          Since you desire a N*2 array, it will have to be a Variant to accommodate Timestamps, floating points values, as well as possible System Digital States.

                           

                          The following code example may point you in the right direction:

                           

                          Option Explicit
                          
                          Public Function LoadPIValuesToArray() As Variant()
                          
                              Const ArrSize As Long = 7& * 24& * 60& * 12&
                              Dim myArray(1 To ArrSize, 1 To 2) As Variant
                              
                              Dim piserver As PISDK.Server
                              Dim pipoint As PISDK.pipoint
                              Dim pivalues As PISDK.pivalues
                          
                              Set piserver = Servers.DefaultServer
                              If Not piserver.Connected Then
                                  piserver.Open
                              End If
                          
                              Set pipoint = piserver.PIPoints("Sinusoid")
                          
                              Set pivalues = pipoint.Data.InterpolatedValues("*-7d", "*", ArrSize)
                          
                              Dim i As Long
                          
                              For i = 1 To ArrSize
                                  myArray(i, 1) = pivalues(i).TimeStamp
                                  myArray(i, 2) = pivalues(i).Value
                              Next i
                          
                              LoadPIValuesToArray = myArray
                              
                          End Function