7 Replies Latest reply on Mar 12, 2015 5:18 AM by Eugene Lee

    Formatting and adding traces in datalink via VBA

    lwalsh

      Hi,  after a lot of fiddling around I have managed to create a trend and add a trace from a Pi tag using Excel VBA and datalink.

      However, I have not been able to work out how to set the time range and format the Trace (scale positions, line colour etc.).

      I am also struggling with how to add a trace based on data in excel rather than a Pi Tag.

       

      If anyone can assist it would be greatly appreciated!!

       

      Cheers

       

      Luke

        • Re: Formatting and adding traces in datalink via VBA
          Eugene Lee

          Hi,

           

          You need to get hold of your Trend object and play with the TrendConfig after you have added it to your worksheet. You can do this by using PITrend.GetConfiguration. Then use methods such as

           

          TrendConfig.VerticalAxis.VerticalScales.SingleScale.Min = pbwScaleAbsolute
          TrendConfig.VerticalAxis.VerticalScales.SingleScale.AbsoluteMin = -2
          
          TrendConfig.VerticalAxis.VerticalScales.SingleScale.Max = pbwScaleAbsolute
          TrendConfig.VerticalAxis.VerticalScales.SingleScale.AbsoluteMax = 10
          

           

          Finally,


          Use PITrend.SetConfiguration(TrendConfig) to set the settings.

            • Re: Formatting and adding traces in datalink via VBA
              lwalsh

              Thanks for the response Eugene,

               

              I am only new (and self taught)  to Pi and VBA so am probably missing something obvious but i get a a runtime error "Object doesn't support this property or method" with the getconfiguration. Where am i going wrong?

               

              Also can you set the time range through the trend config?

               

              Do you know how to add a trace based on data in an excel worksheet?

               

              Thanks

              Function SetObjects(FF, SL, DASCode)

                  Dim CTnd As PBSymLib.Trend
                  Dim CObj As Excel.OLEObject
                  Dim TrObj As Object
                 
                  Application.ScreenUpdating = False
                  Set CObj = Worksheets(1).OLEObjects.Add("PIDisplayType")
                 
                 
                  CObj.ShapeRange.LockAspectRatio = msoFalse
                  CObj.Top = Range("A1")
                  CObj.Left = Range("A1")
                  CObj.Width = 1248
                  CObj.Height = 680
                 
                  Set TrObj = CObj.Object
                 
                  Set CTnd = TrObj.Symbols.Add(pbSymbolTrend)
                
                 
                  Call ConfigureTrend(CTnd, FF, SL, DASCode)
                 
                 
              End Function


              Function ConfigureTrend(CTnd, FF, SL, DASCode)

                  Dim TndConfig As TrendConfig
                 
                  Application.ScreenUpdating = True
                 
                  CTnd.AddTrace (SL & DASCode & FF)
                  CTnd.AddTrace (SL & DASCode & ".Flow Rate")
                       
                  Set TndConfig = CTnd.GetConfiguration
                  TndConfig.PlotArea.Fill.Color = vbRed
                       
                  CTnd.Top = 15000
                  CTnd.Left = -15000
                  CTnd.Height = 2385
                  CTnd.Width = 4405
                 
                  CTnd.BackgroundColor = RGB(192, 192, 194)
                 
                  CTnd.MultipleScales = True

                 
              End Function

                • Re: Formatting and adding traces in datalink via VBA
                  Eugene Lee

                  Hi Luke, apologies for the late reply as I was on vacation. Ah I see that you are using the PB Trend instead of DL Trend. Originally, I thought that you are using DL Trend. If you are using PB Trend, you can set the start and end time this way.

                   

                      Dim CTnd As PBSymLib.Trend
                      Dim CObj As Excel.OLEObject
                      Dim TrObj As Object
                    
                      Application.ScreenUpdating = False
                      Set CObj = Worksheets(1).OLEObjects.Add("PIDisplayType")
                    
                    
                      CObj.ShapeRange.LockAspectRatio = msoFalse
                      CObj.Top = Range("A1")
                      CObj.Left = Range("A1")
                      CObj.Width = 1248
                      CObj.Height = 680
                    
                      Set TrObj = CObj.Object
                    
                      Set CTnd = TrObj.Symbols.Add(pbSymbolTrend)
                      CTnd.AddTrace ("\\eugenevs\cdt158")
                      ctnd.SetStartAndEndTime("*-10s","*")
                      Application.ScreenUpdating = True
                  
                  

                   

                  This will set the Trace start time to 10s before the current time and the Trace end time to the current time.

                   

                  Regarding your last question, if you want to add a trace based on data in Excel. The best way would be to configure an ODBC dataset using the Excel spreadsheet as the source. Here is a KB article for you to refer to!

                   

                  https://techsupport.osisoft.com/Troubleshooting/KB/KB00178

                   

                  Thanks!

                    • Re: Formatting and adding traces in datalink via VBA
                      lwalsh


                      Thanks Eugene,

                       

                      I have tried that but get the error "expected an =" for that line.

                      I didn't know that i could use the DL trend, it sounds like that should be the way to go (assuming they dont remove it as it is legacy). How can i add a trend & trace based on the DL trend?

                       

                      Cheers

                        • Re: Formatting and adding traces in datalink via VBA
                          Eugene Lee

                          Hi Luke,

                           

                          Please change

                           

                          ctnd.SetStartAndEndTime("*-10s","*")

                           

                          to

                           

                          CTnd.SetStartAndEndTime "*-10s", "*"

                           

                          Also, I certainly wouldn't recommend the DL Trend as it is legacy. So we really don't encourage it.

                            • Re: Formatting and adding traces in datalink via VBA
                              lwalsh

                              Thank you so much for your assistance Eugene,

                               

                              I have tried that in the past and I was assuming that it hadn't worked but it appears it is just very slow!

                              I changed it to *-10s and the trend updated after a delay of 15 seconds or so but when i change it to show the start of the financial year until now it draws the trend (set to 8h) the code finishes but it doesn't update the longer range for over 10 minutes!

                              Is there a way to set the start and end time prior to it drawing the trend? would this be quicker?

                               

                              Also using the PB trend how can i access the line colour, scale location type properties.

                               

                              Cheers

                                • Re: Formatting and adding traces in datalink via VBA
                                  Eugene Lee

                                  No problems Luke!

                                   

                                  The method to set the start and end time of a trend is using SetStartAndEndTime as mentioned above. The SetStartAndEndTime method sets the persisted time range for the trend. This method provides the same functionality as setting the time range by using the Trend Definition dialog box in PI ProcessBook.

                                   

                                  For line colour, scale, location, type properties can be set by accessing the TrendFormat of the Trend using GetFormat. An example that will change the first trace to red colour:

                                   

                                  Set aFormat = trend.GetFormat
                                  Set trndElmnts = aFormat.Elements
                                  trndElmnts(pbPen1).Color = 255 'Red colour
                                  trend.SetFormat aFormat
                                  

                                   

                                  The VBA reference guide for Processbook will also give you more details regarding this too about the other properties that you can set such as LineStyle and LineWidth etc.

                                   

                                  https://techsupport.osisoft.com/Downloads/File/e42957ba-86ad-469d-9538-b5bd8fb73a62