4 Replies Latest reply on Apr 5, 2016 8:51 AM by pthivierge

    Run Compressed Data Codes Multiple Times VBA

    Stefanie

      Hi, I have a series of compressed data code. Now I need to write a VBA to run these codes with different start dates. How to do so?

       

      Please help, Thanks!!!

        • Re: Run Compressed Data Codes Multiple Times VBA
          pthivierge

          Hello Stefanie,

           

          I did not have a lot of information so I had to guess a little bit to understand what you need.  Please provide more information if the following code does not help.

          In this sample, you will need to adjust the code in CollectCompressedData method to fit your need:

           

          Option Explicit
          
          
          Public mPIServer As PISDK.server
          
          
          Public Function CollectCompressedData()
          
          
              Dim values As PIValues
              ConnectPIDataArchive "megatron"
          
          
              ' here you can pass anything
              Set values = GetRecordedValues("sinusoid", "2016-03-30", "2016-03-31")
          
          
              ' do what you need with the data here
              Dim archiveValue As PIValue
              For Each archiveValue In values
                  Debug.Print archiveValue.value & " " & archiveValue.TimeStamp.LocalDate
              Next
          
          
          End Function
          
          
          Public Function ConnectPIDataArchive(Optional serverName As String) As Boolean
          
          
              If serverName <> "" Then
                  Set mPIServer = PISDK.Servers.item(serverName)
              Else
                  Set mPIServer = PISDK.Servers.DefaultServer
              End If
                
          
          
              If mPIServer Is Nothing Then ConnectPIDataArchive = False: Exit Function
          
          
              If mPIServer.Connected Then mPIServer.Close
          
          
              mPIServer.Open
          
          
              Debug.Print "PI Server Connected"
          
          
              ConnectPIDataArchive = mPIServer.Connected
          
          
          End Function
          
          
          
          
          
          
          '---------------------------------------------------------------------------------------
          ' Procedure : GetRecordedValues
          ' Purpose   : to get values that are stored in the archives
          '---------------------------------------------------------------------------------------
          '
          Public Function GetRecordedValues(ByVal tagName As String, startTime As String, endTime As String) As PIValues
          
          
              Dim archiveValues As PIValues
          
          
              Set archiveValues = mPIServer.PIPoints(tagName).data.RecordedValues(startTime, endTime, btAuto)
          
          
              Set GetRecordedValues = archiveValues
          
          
          ' debug
          '    Dim archiveValue As PIValue
          '    For Each archiveValue In archiveValues
          '        Debug.Print archiveValue.value & " " & archiveValue.TimeStamp.LocalDate
          '    Next
          End Function
          

           

          Under tools, references, you will need to add references to the PI SDK objects.

           

          Let us know if you have questions.

          1 of 1 people found this helpful
          • Re: Run Compressed Data Codes Multiple Times VBA
            pthivierge

            Hello Stefanie,

             

            Did the proposed code helped?

              • Re: Run Compressed Data Codes Multiple Times VBA
                Stefanie

                Hi Patrice. Thank you very much! I was wondering if for example I have a temperature tag, is it possible to feed in a time range and get the temperature range between those times in excel?

                  • Re: Run Compressed Data Codes Multiple Times VBA
                    pthivierge

                    Hello Stefanie,

                     

                    Yes this is possible.

                     

                    You can do that out of the box with PI DataLink:

                     

                    Or with VBA you can simply add a filter expression to the GetRecordedValue Method.  I am providing you the differences with the code provided in my previous post:

                    In the CollectcompressedData function, add another parameter to take the filter expression:

                    Public Function CollectCompressedData()
                    ...
                        Set values = GetRecordedValues("sinusoid", "2016-03-30", "2016-03-31", "'sinusoid'>30 and 'sinusoid'<90")
                    ...    
                    End Function
                    

                     

                    Modify the GetRecordedValues function to take the new parameter and pass it to the recorded values call:

                    Public Function GetRecordedValues(ByVal tagName As String, startTime As String, endTime As String, filterExp As String) As PIValues
                    ...
                        Set archiveValues = mPIServer.PIPoints(tagName).data.RecordedValues(startTime, endTime, btAuto, filterExp, fvRemoveFiltered)
                    ...
                    End Function
                    

                     

                    You have some examples how to build Filter Expressions here: Expressions examples.

                     

                    My recommendation

                    if you are not comfortable with code, I would highly recommend that you use PI Datalink instead, it integrates well with Excel and should be easier to use.

                    We do have an online course to learn it, and available here: Creating Basic Reports with PI DataLink

                     

                    A note about AF SDK

                    Please not that when using the PI SDK, you cannot have access to AF Attribues neither assets, AF SDK could do that but it is compatible with .NET only and because you are using VBA, PI SDK is the easy path to make quick automation with the PI Data Archive.

                    We do not usually recommend PI SDK anymore and AF SDK should be used instead, except for specific situations like this one, where AF SDK cannot be used directly. ( Background: Deprecating the PI SDK ).