16 Replies Latest reply on Aug 7, 2017 9:51 PM by Ramkumar

    PI Trend for only selected tags

    formerpigeek

      I have to create an excel file in which I have a list of tags. The user selects one or more of the tags by checking the boxes before them. The user then clicks a button to display the trend. The PI  displays only the trends for the tags the user selected.
      How this can be done?

        • Re: PI Trend for only selected tags
          andreas

          Hi Rakhk,

           

          the following code creates a new sheet and inserts a trend:

          Private Sub Test()
             
              Dim ooOleObjs As OLEObjects
              Set ooOleObjs = Me.OLEObjects
              Dim etWizard As ExcelTrendWizard
              Dim strTagname As String
              Dim strStartTime As String
              Dim strEndTime As String

              Set etWizard = ooOleObjs.Add("PIXLTWIZ.ExcelTrendWizard").Object
             
              strTagname = "CDT158"
              strStartTime = "*-8h"
              strEndTime = "*"

              etWizard.PIAddQuerySpec "ANDREASPC", strTagname, strStartTime, strEndTime
             
              strTagname = "SINUSOID"
              strStartTime = "*-8h"
              strEndTime = "*"

              etWizard.PIAddQuerySpec "ANDREASPC", strTagname, strStartTime, strEndTime
             
              etWizard.InsertTrend , "$F$2:$K$12"
             
          End Sub

          Note that you need to set a reference to ExcelTrendWizard in the VBA project.

           

          You can find the documentation in the PIPC\Help directory:

          • PITRENDAUTO.HLP - the PI Trend Control Programmer's Reference
          • PITRENDXL.HLP - the Insert Trend Wizard Reference

          Hope this helps,

            • Re: PI Trend for only selected tags
              formerpigeek

              Thanks for the quick reply Andreas.

               

              I am getting an error from this line of code

               

              Set ooOleObjs = Me.OLEObjects

               

              The erros says "Invalid use of Me keyword". Can you tell me what should be the problem?

               

              If you could send me a working excel file, that would be great.

               

              Cheers,

               

              Kapil.

                • Re: PI Trend for only selected tags

                  Maybe it's a matter of which version of Excel you are using...  can you try this instead:

                  Set ooOleObjs = ActiveSheet.OLEObjects

                    • Re: PI Trend for only selected tags
                      formerpigeek

                      Thanks a lot ...It worked ..I was using Microsoft Excel 2003...

                        • Re: PI Trend for only selected tags
                          formerpigeek

                          Its ok to use InsertTrend....How do I delete an existing trend?...This is necessary as the new trends will stay on top of the old ones and increase the file size. Another problem I am facing is the Trend background is always the default grey. How do I change it to white in my VBA code.. How do I change the trace color and line style?...Please reply...

                            • Re: PI Trend for only selected tags

                              To manipulate a Trend object you need to retrieve the Trend object's TrendFormat.  Once you have the TrendFormat object you can manipulate the Elements, e.g. BackgroundColour.

                               

                              Let's look at an example:

                              Dim TheTrend As Trend
                              Set TheTrend = ThisDisplay.Symbols("Trend1") 'Set to the Symbol name for your trend

                              Dim TF As TrendFormat
                              Set TF = TheTrend.GetFormat ' Retrieve the TrendFormat

                                 TF.Elements(pbBackGround).Color = pbWhite ' Change Background color
                                 TF.SetFormat TF 'Make sure you set the format!

                              Set TF = Nothing

                              Set TheTrend = Nothing

                               

                              Assuming you understand that, each trace on a trend is defined as a Pen so access the relevant Pen object to change the LineWidth or Color property.


                              TF.Elements(pbPen1).Color = pbRed
                              TF.Elements(pbPen1).LineWidth = pbThin

                              Don't forget you can change your color preferences under "Tools --> Preferences" in ProcessBook.

                               

                              Rhys.

                                • Re: PI Trend for only selected tags
                                  cjrancur

                                  Hi Rhys,

                                   

                                  I used your code for setting symbols today.  I found that a small typing correction was needed.  Here is the mistyped line:

                                   

                                              TF.SetFormat TF 'Make sure you set the format!

                                  It works with this minor correction.

                                   

                                             TheTrend.SetFormat TF 'Make sure you set the format!

                                   

                                  Thank you for the example.  I would not have known that the setformat method was needed after the element properties were assigned, without this post.  This post is still useful today, nearly 2 years after it was written.

                                   

                                  Carrie

                                   

                                   

                                • Re: PI Trend for only selected tags
                                  andreas

                                  Kapil,

                                   

                                  here are some lines to delete all the PI Trends in your active sheet:

                                  ' The collection of objects
                                  Dim ooOleObjs As OLEObjects
                                  ooOleObjs = ActiveSheet.OLEObjects
                                   
                                  ' A single object
                                  Dim ooOleObj As OLEObject

                                  ' Loop through the collection of objects
                                  ForEach ooOleObj In ooOleObjs

                                      ' Store the name of the object
                                      Dim strObjName AsString
                                      strObjName = ooOleObj.Name

                                      ' Check for ExcelTrendWizard objects and delete
                                      If (Left(strObjName, 16) = "ExcelTrendWizard") Then
                                          ooOleObjs(ooOleObj.Index).Delete()
                                      EndIf
                                      ' Check for PITrend objects and delete
                                      If (Left(strObjName, 7) = "PITrend") Then
                                          ooOleObjs(ooOleObj.Index).Delete()
                                      EndIf
                                      ' Check for PIArchiveData objects and delete
                                      If (Left(strObjName, 13) = "PIArchiveData") Then
                                          ooOleObjs(ooOleObj.Index).Delete()
                                      EndIf
                                   
                                  Next

                                  and some more for changing the background:

                                  ' Loop through the collection of objects
                                  ForEach ooOleObj In ooOleObjs
                                   
                                      ' Store the name of the object
                                      Dim strObjName AsString
                                      strObjName = ooOleObj.Name
                                   
                                      ' If it is a Trend ...
                                      If (Left(strObjName, 7) = "PITrend") Then
                                          ooOleObj.Activate()
                                          Dim ptTrend As PITrend
                                          Dim tcTrendConfig As TrendConfig

                                          ptTrend = ooOleObj.Object

                                          ' Get the configuration
                                          tcTrendConfig = ptTrend.GetConfiguration

                                          ' Get the Fill for the PlotArea
                                          Dim fFill As Fill
                                          fFill = tcTrendConfig.PlotArea.Fill
                                          fFill.Color = vbWhite

                                          ' Get the Fill for the TrendArea
                                          fFill = tcTrendConfig.TrendArea.Fill
                                          fFill.Color = vbWhite

                                          ' Get the Fill for the Legend
                                          fFill = tcTrendConfig.Legends.Item(1).Fill
                                          fFill.Color = vbWhite

                                          ' Set the configuration
                                          ptTrend.SetConfiguration(tcTrendConfig)
                                      EndIf

                                  Next

                                  hope this helps!

                                    • Re: PI Trend for only selected tags
                                      formerpigeek

                                      @ Rhys : Thank you for the reply

                                       

                                      @ Andreas: There is a syntax error for the second line of this code

                                      If (Left(strObjName, 16) = "ExcelTrendWizard") Then
                                              ooOleObjs(ooOleObj.Index).Delete()
                                          EndIf

                                      Also what is that number 16.

                                        • Re: PI Trend for only selected tags
                                          andreas

                                          Hi Kapil,

                                           

                                          the 16 is the number of characters from the left - the object gets a name like ExcelTrendWizard1, ExcelTrendWizard2, etc. when it is created. To find the ExcelTrendWizard objects I simply compate the first 16 chracters.

                                           

                                          Not sure why you get a syntaxt error in the delete line - maybe again an Excel version issue. I'll check if I can test on Excel 2003.

                                           

                                          regards,

                                            • Re: PI Trend for only selected tags
                                              formerpigeek

                                              Hi Andreas.

                                               

                                              The following code worked for me in Excel 2003

                                              Sub deletemoretrends()
                                                  Dim ooOleObjs As OLEObjects
                                                  Dim ooOleObj As OLEObject
                                                  Set ooOleObjs = Me.OLEObjects
                                                  For Each ooOleObj In ooOleObjs
                                                      ' Store the name of the object
                                                      Dim strObjName As String
                                                      strObjName = ooOleObj.Name
                                                          
                                                      ' Check for ExcelTrendWizard objects and delete
                                                      If (Left(strObjName,7) = "PITrend") Then
                                                          ooOleObj.Delete
                                                      End If
                                                  Next
                                              End Sub

                                              Thanks for your help.

                                • Re: PI Trend for only selected tags
                                  Swift

                                  Hi,

                                   

                                  I could not find the ExcelTrendWizard in the vba editor inside the process book. I could not find the Help files too. Any idea on how to add the reference?

                                    • Re: PI Trend for only selected tags
                                      gregor

                                      Hello Bagya,

                                       

                                      What version of Excel are you running?

                                      What PI ProcessBook version is installed?

                                      • Re: PI Trend for only selected tags
                                        Kenji Hashimoto

                                        PITrendXL is in Legacy PI DataLink. It works with 32bit Excel.

                                        For installing legacy PI DataLink, we need to change ini file. See PI Live Library.

                                        https://livelibrary.osisoft.com/LiveLibrary/content/en/datalink-v6/GUID-1BB38FFD-08EA-45F1-832F-B738C904E62A

                                        It says,

                                        >To modify the setup.ini file, follow the example in the file.

                                        We can open the setup.ini file itself from installation unzipped folder.

                                        It shows following as comment out.

                                        ; If the legacy DataLink Add-in is to be enabled during installation add the

                                        ;   LEGACY_ADDIN_ENABLED=3 argument to the command line.  E.g.

                                        ; 13 = REBOOT=Suppress ALLUSERS=1 INSTALL_LEGACY_ADDIN=1 LEGACY_ADDIN_ENABLED=3

                                         

                                        So please try to change

                                        13 = REBOOT=Suppress ALLUSERS=1

                                        to

                                        13 = REBOOT=Suppress ALLUSERS=1 INSTALL_LEGACY_ADDIN=1 LEGACY_ADDIN_ENABLED=3

                                        If you have installed PI DataLink already, you can uninstall and reinstall it.

                                         

                                        After this installation, you can use PITrendXL.xla from

                                        File > Option > Add in > Excel Addin > Settings

                                        Add reference as %PIHOME%\Excel\PITrendXL.xla from "Reference" button.

                                         

                                        Open Visual Basic Editor > Tools > Add reference

                                        You should be able to find "Excel Trend Wizard" checkbox.

                                        After that, I could run the Andreas's code and could see trend. (I changed the server name as "localhost")

                                        However, since it is legacy PI DataLink function and in the future, there are possibilities that it will not work correctly.

                                        So I don't recommend to use it.

                                        You can use PI Vision, PI ProcessBook to see trends.

                                        If you want to see trend from excel, spakelines also can be used. (It shows only line though)

                                        Use sparklines to show data trends - Excel

                                        Or you can add standard excel line graphs.

                                         

                                        If you will have more questions, please let us know.

                                        1 of 1 people found this helpful
                                      • Re: PI Trend for only selected tags
                                        Ramkumar

                                        Hi Andreas,

                                         

                                        What will the code be to add the trend to the same worksheet (instead of a new one?) Also what will be the right code to plot on multiple scales?

                                         

                                        Thanks.