13 Replies Latest reply on Feb 14, 2011 5:53 PM by charlie@osisoft.com

    Write Value to PI Issue using VBA in Excel 2003


      I have a report built in Excel 2003 that I am using it to write values to PI.  Currently I would like to use the SDK to write to PI.  When I execute the code it attempts to write the values to PI and I get the variable PISDKCommon.PIErrors.Count as 0 meaning that it should have written successfully but when I check the archives the value isn't there.


      I also tried using a PIPutValX and it was successful in writing to PI.


      Currently I am doing this on a Citrix Server.  Is there any ports or security or anything that need to be opened to write to PI in VBA using the SDK vs. writing using PIPutValX? 


      Here is my code that I am using to write with the SDK.  My column headings are Select, Tag, Time, Value, and Result.  I also have a cell for the PI Server Name named  PIServerName.  If it's helpful I can send the Excel file that I am working on.

      'code requires references to the:
      ' PISDK 1.3 Type Library
      ' PISDK Common 1.0 Type Library
      'references are set under the Tools menu
      Private Sub cmdSendtoPI_Click()
         Dim t                   'loop variables
         Dim result
         t = 2
         Dim rngTag As Range
         Dim rngTimestamp As Range
         Dim rngValue As Range
         Dim rngResult As Range
         Dim rngSelect As Range
         Dim PIServerName As String               'server name
         Dim PIServer As PISDK.Server             'server object
         'Dim PItags As PISDK.PointList            'tag collection for well
         Dim PItag As PISDK.PIPoint               'single tag
         Dim PItag_values As PISDK.PIValues       'value collection for tag
         Dim PItag_errors As PISDKCommon.PIErrors 'errors caused by update
         Dim PItag_error As PISDKCommon.PIError   'single error
         Dim nvValueAttributes As New NamedValues '
         Dim PItag_value As PISDK.PIValue         'single value set
         On Error GoTo Error_Handler
         'get server name from spreadsheet using named range
         PIServerName = Application.Names("PIServerName").RefersToRange.Value
         'create server variable (generates pseSERVNOTFOUND error if wrong name)
         Set PIServer = Servers(PIServerName)
         'loop through each row, stopping when no more tag names are found in column 2
         Do While (Len(Cells(t, 2)) > 0)
            'store reference to row
            Set rngSelect = Cells(t, 1)
            Set rngTag = Cells(t, 2)
            Set rngTimestamp = Cells(t, 3)
            Set rngValue = Cells(t, 4)
            Set rngResult = Cells(t, 5)
            Set PItag_values = New PIValues    'initialize values collection
            PItag_values.ReadOnly = False      'allow adding of items
            If (UCase(rngSelect) = "X") Then
               'get reference to tag name from ColOffset + 1
               'returns error psePOINTNOTEXIST if point not on server
               'doesn't change value of PItag from previous loop when an error occurs.
               'if PItag isn't set to Nothing at end of loop, values from error tag will be written to previous tag.
               Set PItag = PIServer.PIPoints(rngTag)
               'check if PItag was returned by server
               If PItag Is Nothing Then
                  'indicate that tag is not on server
                  rngResult = "tag not found"
                  'read value into PItag_values collection
                  'store formated timestamp and value
                  'NOTE!! cannot pass result of Range() without using string conversion function CStr()
                  Set PItag_value = PItag_values.Add(CStr(Format(rngTimestamp, "dd-mmm-yy hh:mm:ss")) _
                                                      , CStr(rngValue), nvValueAttributes)
                  'update values and check for errors
                  If PItag_values.Count > 0 Then
                     Set PItag_errors = PItag.Data.UpdateValues(PItag_values, dmReplaceDuplicates)
                     If PItag_errors.Count = 0 Then
                        rngResult = "Value Written"
                        For Each PItag_error In PItag_errors
                           rngResult = PItag_error.Cause
                        Next PItag_error
                     End If
                  End If
               End If
               'Application.Run "PIPutValX", Cells(t, 2), Cells(t, 4), Cells(t, 3), Cells(1, 7), Cells(t, 5)
               rngResult = "Not selected"
            End If
            'Prepare for next loop pass
            Set PItag_values = Nothing    'destroy value collection
            Set PItag = Nothing           'destroy current tag (needed for IsNothing test for invalid tags)
            t = t + 1
         'clear objects
         Set PIServer = Nothing
         Set PItag = Nothing
         Set PItag_values = Nothing
      Exit Sub
      Select Case Err.Number
       Case pseSERVNOTFOUND
         MsgBox "Specified server could not be found."
       Case psePOINTNOTEXIST
         'point does not exist on server, continue executing code.
         Resume Next
       Case pseWRITEERROR
         'one or more points not deleted, nothing needs to be done
         Resume Next
       Case Else
         MsgBox Err.Number & ": " & Err.Description
      End Select
      End Sub


        • Re: Write Value to PI Issue using VBA in Excel 2003
          Daniel Takara



          Is there a registered archive on the PI Server for the period to which you are trying to write values? This is a possible situation where you would not get any error on the client side when trying to write the values, but the values would not be written in the archive.

            • Re: Write Value to PI Issue using VBA in Excel 2003
              Ahmad Fattahi



              In addition to checking what Daniel suggested, you might want to try writing the same value(s) using another tool such as PI DataLink or any other PI SDK tool, both from the same node or other nodes to see what happens. That should rule out a lot of possibilities and narrow the issue down.


              By the way, did you look at the SDK logs on the PI Server? Can you see any trace of the connection? Any errors?

                • Re: Write Value to PI Issue using VBA in Excel 2003

                  Another possibility is that you are writing to a collective.  The PISDK always writes to the primary unless the secondary has been configured to accept PISDK values.  So the symptom would show up as a successful write to the primary, but looking at the secondary, you would not see the value.

                    • Re: Write Value to PI Issue using VBA in Excel 2003

                      I found out that somewhere along the way the timestamp is being changed from 02-Feb-2011 8:00:00 to 11-Feb-02 08:00:00.  So the value is being sent to PI at the correct time and somewhere along the way the day and year are being switched.  We think its being caused by Citrix or some network set-up because if we write the values from the PI server or the node then they write successfully in the archive.  If anyone is interested I have a screenshot of the Server and SDK logs.

                        • Re: Write Value to PI Issue using VBA in Excel 2003
                          Ahmad Fattahi

                          Have you checked the Date/Time settings on the box? They could have a role in changing the order.

                            • Re: Write Value to PI Issue using VBA in Excel 2003

                              It works through Citrix if the server date setting is changed to:  mm-dd-yy.


                              Would someone be able to provide a basic roadmap of what happens to the data when writing to PI using PIPutValX and using SDK.

                                • Re: Write Value to PI Issue using VBA in Excel 2003
                                  Ahmad Fattahi

                                  A very good overview of the data flow in PI System can be found here. In very short terms the events go through Snapshot and Event Queue before reaching the archive files.

                                    • Re: Write Value to PI Issue using VBA in Excel 2003

                                      I found that changing the date format to dd-mmm-yyyy hh:mm:ss would write to PI every time.


                                      I had to change:



                                      Set PItag_value = PItag_values.Add(CStr(Format(rngTimestamp, "dd-mmm-yy hh:mm:ss")), CStr(rngValue), nvValueAttributes) 



                                      To this:

                                        Set PItag_value = PItag_values.Add(CStr(Format(rngTimestamp, "dd-mmm-yyyy hh:mm:ss")), CStr(rngValue), nvValueAttributes) 




                                        • Re: Write Value to PI Issue using VBA in Excel 2003

                                          One optimization you could try is using PITimeServer.PITimeFormat to convert the values.  If you work exclusively in PI time formats, the LocaleIndependent=true setting will prevent locale specific (mis)interpretations of timestamps.  Also, the TimeZoneInfo property could be useful for showing the equivalent of the client/server switch in DataLink (by using Server.PITimeZoneInfo).

                                          Dim piTmFmt as new PITimeFormat
                                          ' PI Time format is synonomous with locale independent time
                                          piTmFmt.LocaleIndependent = True
                                          ' You will need to figure out the source time zone from some cell
                                          If bSrvrTime Then
                                            piTmFmt.TimeZoneInfo = PIServer.PITimeZoneInfo
                                          End If
                                          Do While ...
                                             piTmFmt.InputString = rngTimestamp
                                             ' Passing seconds is the most efficient 
                                             Set PItag_value = PItag_values.Add(piTmFmt.UTCSeconds, _
                                                                                        CStr(rngValue), nvValueAttributes) 


                                            • Re: Write Value to PI Issue using VBA in Excel 2003

                                              Ahmad:  Is there any more information about how the data gets from the Client to the Snapshot?  I understand the Snapshot > Evene Queue > Archive but I don't understand how it goes from the Client > Snapshot.

                                                • Re: Write Value to PI Issue using VBA in Excel 2003
                                                  Ahmad Fattahi



                                                  Here is a more elaborate description of what happens when you have a PINS node (a node with PI SDK, PI Network Manager, PI Message Subsystem) and send something to the Snapshot subsystem on the PI Server. In this case PI SDK lets pinetmgr subsystems on the PINS node and the PI Server to take care of the communication. PI SDK uses PINet3 protocol. Note that this assumes there is no buffering on the way; if there is one, the buffer subsystem will sit after the PI3 protocol below. Also, note that PI API uses another protocol called PI2.

                                                  • PINS
                                                    • A PINS node refers to a Windows system with PI SDK installed and running the pinetmgr and pimsgss subsystems. A PI server node is not considered a PINS node.
                                                    • SDK Connections talk directly to the pinetmgr specified in the pisubsys.cfg file. By default this is set to the local named pipes listener and should not be modified without good reason.
                                                    • The default connection established on a PINS node from an application to pinetmgr is called "Session 0".
                                                  • PINet3
                                                    • PI SDK uses the PINet3 Protocol to talk with PI. 
                                                  • RPC Tables
                                                    • The pinetmgr on the PINS node is called the "router". On the PI server pinetmgr is called the "resolver". The router only keeps track of PINS RPCs (pimsgss, pibufss).
                                                    • Once a PI SDK connection is attempted to the PI server, the resolver will return a cached RPC table to the router that is passed on to the client PI SDK application's session.
                                                  • Authentication
                                                    • The following three authentication methods are normally attempted in the following order and must be completed within the connection timeout specified for the PI server.
                                                      1. Trust
                                                      2. Default User
                                                      3. Login Prompt