8 Replies Latest reply on May 2, 2014 4:15 PM by Marcos Vainer Loeff

    Excel / Datalink element relative from Processbook

    tgreen

       

       

      Hello all!

       

      i'm trying to open a excel spreadsheet that links with all the data within the PI system, I have approximately 200 hundred meters in the system and when i open up the report it goes to the default template in excel that i have created,

       

      My question is ... How can I pass the active display element (constantly changing) to the generated data link report?  This would then give every meter its own individual report,

       

      I'm thinking VB in the excel sheet? Just not sure where to start,

       

       

       

      Thanks Guys,

       

       

        • Re: Excel / Datalink element relative from Processbook
          Marcos Vainer Loeff

          Hi Trevor,

           

          IMHO, the easier solution would be to send the active display element path to a PI Point of the PI Data Archive. This PI Point does not need to archive values only snapshots. When you open the report on Excel, it will first read the value of this tag and then it will show the report of the active display element. You could send the value to the PI AF Server but you will have to use AF Wrapper which makes everything more complex.

            • Re: Excel / Datalink element relative from Processbook
              tgreen

              Could I get some help with writing the VB code for sending the display element to the Pi Tag? I've been searching for some help but I'm finding it hard to get any answers!

               

              I created a Lab Tag in PI AF to send it to, is that what i need?

               

              Much Appreciated!

                • Re: Excel / Datalink element relative from Processbook

                  Hello Trevor,

                   

                  I am a little unsure because you say that you've created a Lab Tag in PI AF but assume you've created a Lab tag within the PI Data Archive (PI Server). Please see the Manual Data Entry example at vCampus Library -> Legacy DevNet Content -> PI Data Link Tools on how to update a PI Tag with data from a Microsoft Excel Sheet.

                    • Re: Excel / Datalink element relative from Processbook
                      tgreen

                      Hey Gregor,

                       

                      Its not so much writing from excel, but i'm trying to pass the processbook element of interest of the active display to the excel sheet, from what i've gathered so far its simply a matter a building a PI tag, then sending the element to it.  This makes the tag dynamic and always changing and would change my datalink report depending on what PB page i'm on,

                       

                      Writing to a PI tag in PB seems to be where were held up, once we have it into a point we shouldn't have a problem getting it into datalink,

                       

                      Thanks again!

                        • Re: Excel / Datalink element relative from Processbook
                          skwan

                          Trevor:

                           

                          Have you considered the asset relative feature of DataLink (2013 or greater)?  In essence, within the Excel spreadsheet, you can have a drop down of all your meters, assuming they are all from the same AF element template.  By selecting the meter of interest from this drop down, the DataLInk sheet would show the data corresponding to the selected meter.

                            • Re: Excel / Datalink element relative from Processbook
                              tgreen

                              Hey Steve,

                               

                              I've used this feature and its good, but still requires you to manually select the meter #,  the method i'm trying to achieve would match the processbook meter page with the data link report upon opening (without having to open the drop down).  So the Tag value drives the report element,

                               

                              Thanks again!

                                • Re: Excel / Datalink element relative from Processbook

                                  Hello Trevor,

                                   

                                  First of all, please accept my apology. I was just looking at your latest post without reading the initial one nor Marcos reply when referring you to the Manual Data Entry example.

                                   

                                  My understanding is that you are talking about an Element Relative Display where the user is able to chose the context by the selecting Elements in the TreeView. You like to offer the ability to pass the context over to Microsoft Excel and automatically build / update a report based on the context. Is my understanding right?

                                   

                                  There's no built in functionality with ProcessBook / DataLink offering that functionality out of the box but I believe what you are trying to accomplish could be possible. An 'easy' solution could be reading the context from the TreeView, copy it to the clipboard when the user clicks a button and offer another button in Excel allowing to paste the content to update the report. OLE Automation might be another option.

                                   

                                  There are a few things I am unsure about. Do you expect the report in Excel updating with every update of the ProcessBook display?

                                   

                                  When showing data in a Trend symbol, PI ProcessBook is doing a so called PlotValues call that doesn't return all the values falling into the selected period but only those allowing to render a characteristic trend. Would you expect the user sees the same events (value + timestamp) in Excel?

                                    • Re: Excel / Datalink element relative from Processbook
                                      Marcos Vainer Loeff

                                      Hi Trevor,

                                       

                                      The code snippet below is an example of sending values to the PI Data Archive in Excel through VBA/PISDK. There are some objects related to Excel cells that you should swtich to PI ProcessBook objects. You can use it as a reference to send values to the PI Data Archive using PISDK within VBA code from PI ProcessBook.

                                       

                                       

                                       
                                      Sub Button1_Click()
                                      
                                      
                                      Dim MyPIServer As PISDK.Server
                                      Dim MyPIServers As PISDK.Servers
                                      Dim Tag As PISDK.PIPoint
                                      Dim Timestamp As New PITime
                                      Dim TimestampDate As Date
                                      Dim TimestampString As String
                                      Dim Value As Variant
                                      Dim piTagErrors As PISDKCommon.PIErrors
                                      Dim pivalues As PISDK.pivalues
                                      Dim i As Integer
                                      Dim NumVal As Integer
                                      NumVal = Range("NumVal").Value
                                      
                                      i = 5
                                      
                                      Set MyPIServers = PISDK.Servers
                                      Set MyPIServer = MyPIServers(Range("PISERVERNAME").Value)
                                      MyPIServer.Open
                                      Set Tag = MyPIServer.PIPoints(Range("TAGNAME").Value)
                                      
                                      While i < (NumVal + 5)
                                      TimestampString = Format((Range("D" + LTrim(Str(i))).Value), "dd/mm/yyyy hh:mm:ss")
                                      TimestampDate = CDate(TimestampString)
                                      Timestamp.LocalDate = TimestampDate
                                      
                                      Value = Range("C" + LTrim(Str(i))).Value
                                      Tag.Data.UpdateValue Value, Timestamp, dmInsertDuplicates, Nothing
                                      i = i + 1
                                      Wend
                                      
                                      MyPIServer.Close
                                      
                                      End Sub
                                      

                                       

                                       

                                      Let me know if you are still facing issues with that,