5 Replies Latest reply on Oct 10, 2016 9:08 PM by FernandoReyes

    Simple Code for VBA with PI2010 V4.2.1

    FernandoReyes

      Hi, I recently started using PI for data extraction, I have tried to use VBA codes for it but with failed results.

       

      Sub RetrieveCompressedData()
          Dim result_range As Range
          Dim tag, stime, etime, PIServer, boundarytype As String
          Dim interval As String
          Dim pi_formula As String
            
          tag = "WF-UY-009-1171-Y3-A-009:50227"
          stime = "*-1h"
          etime = "*"
          interval = "10m"
          PIServer = "wonesepip01"
          boundarytype = "1"
           pi_formula = "=PISampDat(" & tag & ";" & stime & ";" & etime & ";" & interval & ";" & boundarytype & ";" & Chr(34) & PIServer & Chr(34) & ")"

       

                        
          Set result_range = Range("A1:B1")
          result_range.ClearContents
          result_range.FormulaArray = pi_formula
          result_range.CurrentRegion.Select
          Selection.Columns(1).NumberFormat = "dd-mmm-yyyy hh:mm:ss"
          Call dlresize

      End Sub

       

      Tried to use that code with no results.

       

      hope someone can guide me.

       

      Chers

      Fernando

        • Re: Simple Code for VBA with PI2010 V4.2.1
          stang

          Hello Fernando,

           

          What exactly is the problem you are experiencing? The cell populates but the formula does not resolve? Or something else?

          I am unable to test on your DataLink version but on the version available to me the reason it doesn't work is because of syntax errors. In addition to changing the range to just one cell (A1 instead of A1:B1), these changes made it work for me:

           

          pi_formula = "=PISampDat(" & Chr(34) & tag & Chr(34) & "," & Chr(34) & stime & Chr(34) & "," & Chr(34) & etime & Chr(34) & "," & Chr(34) & interval & Chr(34) & "," & boundarytype & "," & Chr(34) & PIServer & Chr(34) & ")"

           

          Please also tell us more about your use case, perhaps we can suggest a better way than building DataLink formulas from VBA.