11 Replies Latest reply on Jan 12, 2015 3:58 AM by Eugene Lee

    How to use PI Datalink to enter value in the PI System?

    rickyrescar

      I have been digging up lately on how to use Datalink as a data entry tool for the PI System. I learned that you can use the Piputval() function. I just don't know how where to start. I designed already forms from the DEVELOPER tab from excel. Can anyone give me the codes. I also want to understand the process thanks!

        • Re: how to use Datalink to enter value in the PI System?
          bshang

          Hi Ricky,

           

          The KB article 1501OSI8 ("How do you send or write data to the PI Server from an Excel Spreadsheet") may also be helpful.

          • Re: how to use Datalink to enter value in the PI System?
            cjrancur

            We used to enter all our lab data to PI this way, but then Excel became something of a LIMS system with PI.  The caution is that these kind of systems need constant updates from a programming standpoint, when analyses change or specifications are updated.  Be careful about scope and future maintenance requirements when you are getting started with something like this. There are commencial LIMS systems available that can provide the maintenance needed. I don't know if that is what you are doing, but be careful and consider ongoing future maintenance requirements before you get into this. Also consider training requirements for the people entering data.  For instance, there were lab people who resisted computerization (many years ago), who claimed that the system "did not work" when Lotus Spreadsheet fields were configured with too many display digits, so the numbers were "wrong" in their estimation. That led to people refusing to enter data, and some Union grievances. It would have been worse if some prankster configured a date field where a number entry should have been. And yes, now it is easier to lock Excel fields, but we were doing this in the mid-90's, initially using Lotus 5, before Excel. We moved to having our IT group, rather than Engineering, maintain a SQL data entry system, with an ACE program that sends SQL data to PI after several pre-processing steps that graded the lab results into good, so-so, or failed product quality result categories. The PI Manual data logger is another option, but it did not meet or quality sorting requirements. What sort of data is being entered in this case.  Does my experience apply?

            • Re: how to use Datalink to enter value in the PI System?
              mtracy

              I use this to fetch weather data via an Excel hyperlink and write to the PI Server

              'This subroutine writess vlaues to pi server

                 Dim numoftags As Integer
                
                 'The following four variables are arguments to PIPutVal()
                 Dim sTagname As String  'Tagname
                 Dim stime As String     'Timestamp
                 Dim valueCell As Range  'Cell reference containing value to be written
                 Dim resultCell As Range 'Cell reference to hold result
                 Dim sServer3 As String   'server name
                 Dim apierr As Long
                 Dim rowstr As String
                 Dim buf As String
                 Dim Timeval As Double
                
                 Dim macroResult As Variant
                
                 Dim timeCell As Range 'Cell reference to hold time of putval
                
                 numoftags = 12  'we have twelve tags, from cell b9 to d 12
                
                 'timestamp is in CELL(2,2)
                
                 If (IsNumeric(Worksheets("TOTAL").Cells(2, 2))) Then
                     Timeval = Worksheets("TOTAL").Cells(2, 2).Value
                     stime = Format(Timeval)
                 Else
                     stime = Worksheets("TOTAL").Cells(2, 2).Text
                 End If


                 sServer3 = "OSI-PI"
                
                 i = 0
                
                 While i < numoftags
                
                 For k = 2 To 4
                
                  For j = 3 To 6
                 
                    'resultCell is in columns 2 thru 4, rows 14 thru 17
                   
                    Set resultCell = Worksheets("TOTAL").Cells(j + 11, k)
                   
                    'valueCell is in column k
                    Set valueCell = Worksheets("TOTAL").Cells(j, k)
                   
                    'tagname is in column 3 (C)
                    sTagname = Worksheets("TOTAL").Cells(j + 6, k).Text
                   
                    'use the timestamp entered by user
                    stime = Worksheets("TOTAL").Cells(2, 2).Text
                   
                    'Call the PIPutVal() macro function
                    'Note that we pass just a , for the PIServer argument; i.e., we
                    'are using the default PIServer
                   
                   
                    'macroResult = Application.Run("PIPutValx", sTagname, valueCell, stime, sServer3, resultCell)
                   
                    macroResult = Application.Run("PIPutVal", sTagname, valueCell, stime, sServer3, resultCell)
                   
                    'move down to the row number
                    i = i + 1
                   
                    Next j
                   
                  Next k
                   
                 Wend

              • Re: how to use Datalink to enter value in the PI System?
                s199146

                Here is one way of doing it and works on Visual Studio (.Net programming) as well.

                 

                1) Go to the VBA window in Excel (Alt + F11)

                2) Under Tools and references, add a reference to "PISDK 1.3 Type Library" and "PISDKCommon 1.0 Type Library" (or newer versions if available)

                 

                3) To write to PI:

                 

                Public Function writetoPI(tag As String, val As Double) As String
                    On Error GoTo ErrHandler
                    Dim srv As Server
                    Dim pt As PIPoint
                    Dim piErr As PIError
                  
                    If srv Is Nothing Then
                        Set srv = PISDK.Servers.Item(PIServerName)
                        srv.Open
                    End If
                      
                    Set pt = srv.PIPoints.Item(tag)
                    pt.Data.UpdateValue val, "*", dmReplaceDuplicates
                    writetoPI = "OK"
                    Exit Function
                  
                ErrHandler:
                    writetoPI = Err.Description
                End Function
                
                
                
                

                 

                4) To read from PI:

                 

                Public Function readfromPI(tag As String) As Double
                    On Error Resume Next
                    Dim srv As Server
                    Dim pt As PIPoint
                    Dim pival As New PIValue
                  
                    If srv Is Nothing Then
                        Set srv = PISDK.Servers.Item(Sheets("Manual Entry").Cells(1, 3))
                        srv.Open
                    End If
                      
                    Set pt = srv.PIPoints(tag)
                    Set pival = pt.Data.Snapshot
                  
                    If IsNumeric(pival.Value) Then
                        readfromPI = pival.Value
                    Else
                        readfromPI = 0
                    End If
                End Function
                

                 

                 

                You can declare your server object (srv) as a module variable and leave the connection open if you're going to be calling it multiple times. There are also ways to write to PI multiple values (bulk) with one single call. The code above is just a start.

                • Re: how to use Datalink to enter value in the PI System?
                  bpalomo

                  I enclose a simple example; My recomendation Excel Manual input is used for few values dayly for tag.

                  I you need save in PI a lot ot values by tag is necesary a automathic interface with a .net, asp, VB6 C++

                  Option Explicit
                  Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
                  
                  Sub Kiwi()                                                                                                ' comments:    this is the code when "Graber Datos" is press. Macro excel
                  'This subroutine is called by the <Send above values> button on the
                  'left hand side of the "Manual_Input_PI-Aridos_ConCon" worksheet
                         Dim response As Integer
                         response = MsgBox("Recuerde asignar fecha correcta. ", vbOKCancel, "Confirmación de Datos")
                         If response = 1 Then
                            response = 0
                            response = MsgBox("Datos Ingresados Correctos  ???", vbOKCancel, "Confirmación de Datos")
                            If response = 1 Then
                               Dim i As Integer
                               Dim numoftags As Integer
                               'The following four variables are arguments to PIPutVal()
                               Dim sTagname As String  'Tagname
                               Dim stime As String     'Timestamp
                               Dim sServer As String   'PI server name
                               Dim valueCell As Range  'Cell reference containing value to be written
                               Dim resultCell As Range 'Cell reference to hold result
                               Dim apierr As Long
                               Dim rowstr As String
                               Dim buf As String
                               Dim macroResult As Variant
                               Dim timeCell As Range 'Cell reference to hold time of putval
                               i = 0
                               numoftags = 13 'we have three tags, from cell A3 to A15
                               While i < numoftags
                                     Worksheets("PutVal").Cells(i + 3, 7) = " " ' limpia la columna 7
                                     i = i + 1
                               Wend
                               i = 0
                               'server is in F2
                               'sServer = Worksheets("PutVal").Cells(2, 5).Text
                               sServer = "pi-lacalera" ' Worksheets("PutVal").Cells(2, 5).Text   alternativa a la linea previa, pasa el servidor enm duro
                               While i < numoftags
                                     'resultCell is in column 5 (E)
                                    Set resultCell = Worksheets("PutVal").Cells(i + 3, 7) ' muestra el resultado en la col E resultado
                                     'valueCell is in column 4 (D)
                                     Set valueCell = Worksheets("PutVal").Cells(i + 3, 4)
                                     'tagname is in column 3 (C)
                                     sTagname = Worksheets("PutVal").Cells(i + 3, 1).Text
                                     'timestamp is in column 2 (B)
                                     stime = Worksheets("PutVal").Cells(1, 6).Text & " 09:00:00:00"
                                    'Call the PIPutVal() macro function
                                    'Note that we pass just a , for the PIServer argument; i.e., we
                                    'are using the default PIServer
                                     macroResult = Application.Run("PIPutVal", sTagname, valueCell, stime, sServer, resultCell)
                                     'move down to the row number
                                      i = i + 1
                                  Wend
                     
                            End If
                            MsgBox " Datos Actualizados en PI-System; Favor validar"
                         End If
                  End Sub
                  

                  2015-01-08 18_06_39-.png

                   

                   

                  2015-01-08 18_05_30-Microsoft Excel - Manual_Input_PI-Aridos_Rancagua.png