At various times my colleagues and I have encountered extended processing times populating a range in an Excel spreadsheet with timestamps and PI Interpolated Values using either a VBA loop to write a value to each row, or using PI Datalink. This tool can speed things up a lot. This sub-routine builds an array of data, and defines a range in the spreadsheet. Then using what is perhaps a little-known tool in VBA one can set the value of the range equal to the array. This populates the range in a second. Here is a sample of the code we found works well:

You will first need to add the following "references" in your VBA Code. This is done by simply navigating to [Tools]=>[References]. This launches the "References - VBA Project" dialog box. Here you scroll and then check "PI-SDK Dialogs", "PISDK 1.3 Type Library",  "PISDKCommon 1.0 Type Library", and "PITimeServer 1.0 Type Library".

 

Sub FillRangeWithPIValues()

 

    '# DECLARE VARIABLES TO FILL A RANGE BY TRANSFERRING AN ARRAY OF DATA:

    Dim CellsDown As Long

    Dim TempValuesArray() As Double

    Dim TempTimeStampsArray() As Date

    Dim CurrVal As Double

    Dim myPIServer As Server

    Dim piPointName As String

    Dim piPointArrayOfValues As PIValues

    Dim piPointValue As PIValue

    Dim piPointTimeStamp As String

    Dim piPointEngUnits As String

    Dim piStartTime As New PITime

    Dim piEndTime As New PITime

    Dim rowCount As Integer

    Dim arrayCount As Integer

    Dim columnCount As Integer

    Dim valuesRange As Range

    Dim timeStampsRange As Range

  

    '# SET DIMENSIONS OF THE RANGE AND CLEAR THE SHEET:

    CellsDown = 1440                                'The number of minutes in a day

    Cells.Clear                                     'This clears everything in the sheet

  

    '# REDIMENSION TEMPORARY ARRAY(S):

    ReDim TempValuesArray(1 To CellsDown, 1 To 1)

    ReDim TempTimeStampsArray(1 To CellsDown, 1 To 1)

  

    '# CONNECT TO THE PI SERVER:

    Set myPIServer = Servers.DefaultServer          'Optional: Set myPIServer = Servers("NAMED_PI_SERVER")

    If myPIServer.Connected = False Then

        myPIServer.Open

    End If

  

    '# SET START TIME AND END TIME:

    piStartTime.LocalDate = Now()                   'Sets start time to now in UTCSeconds

    piEndTime.LocalDate = Now()                     'Sets end time to now in UTCSeconds

    piStartTime = piStartTime.UTCSeconds - 86400    'Sets revised start time to now minus 86400 seconds (1 day)

  

    '# GET PI POINT INTERPOLATED VALUES AND ENGINEERING UNITS:

    piPointName = myPIServer.PIPoints("CDT158").PointAttributes("tag")

    Set piPointArrayOfValues = myPIServer.PIPoints(piPointName).Data.InterpolatedValues(piStartTime.UTCSeconds, piEndTime.UTCSeconds, 1440)

    '  NOTE: when using this function the third paramater is the time span divider. So if gathering data for one hour and you code '60'

    '  you will get interpolated values for each of sixty minutes in an hour. If gathering for one day 1440 will get you interpolated values

    '  for each minute in a day

  

    piPointEngUnits = myPIServer.PIPoints(piPointName).PointAttributes("engunits")

  

    '# LOAD PI POINT NAME IN TARGET CELL AND FORMAT IT:

    Worksheets("Sheet1").Activate

    ActiveSheet.Range("A1").Activate

    With ActiveCell

        .Value = piPointName

        .Font.Bold = True

        .Font.Name = "Lucida Console"

    End With

  

    '# LOOP TO BUILD TEMPORARY ARRAY:

    arrayCount = 1

    columnCount = 1

  

    For Each piPointValue In piPointArrayOfValues

        CurrVal = piPointValue.Value

        piPointTimeStamp = piPointValue.TimeStamp.LocalDate

        TempValuesArray(arrayCount, columnCount) = CurrVal

        TempTimeStampsArray(arrayCount, columnCount) = piPointTimeStamp

        arrayCount = arrayCount + 1

    Next

  

    '# FILL 'timeStampsRange' WITH PI TIMESTAMPS:

    Set timeStampsRange = Sheet1.Range("A2")

    Set timeStampsRange = timeStampsRange.Resize(UBound(TempTimeStampsArray), 1)

    timeStampsRange.Value = TempTimeStampsArray     'The 'magic' happens here

    With timeStampsRange

        .NumberFormat = "dd-mmm-yy hh:mm:ss"        '~ reformats from UTC seconds to user friendly timestamp

    End With

  

    '# FILL 'valuesRange' WITH PI VALUES:

    Set valuesRange = Sheet1.Range("B2")

    Set valuesRange = valuesRange.Resize(UBound(TempValuesArray), 1)

    valuesRange.Value = TempValuesArray             'The 'magic' happens here

  

    '# FILL 'engUnitsRange' WITH PI POINT UNITS OF MEASURE (A.K.A. ENGUNITS):

    Dim engUnitsRange As Range

    Set engUnitsRange = Sheet1.Range("C2")

    Set engUnitsRange = engUnitsRange.Resize(UBound(TempValuesArray), 1)

    engUnitsRange.Value = piPointEngUnits

  

End Sub