3 Replies Latest reply on Dec 5, 2013 5:26 AM by Gregor

    Send bulk data to PI from an Excel spreadsheet


      Hi there,


      We have a VBA code in a Excel spreadsheet which is used from our users to send bulk data to PI. They want to increase by 2x the number of tags being sent. Happens that these users are located on another office with a slow network connection.


      So we need to be able to send multiple values all at once, but we couldn't find an available method in the Excel VBA environment. By looking at the following post, we understand that, at least at that moment, there was no option other than using AF SDK 2012, which is not available when using VBA:




      Do you guys know if there is currently any alternative (other library) that could be used on our Excel spreadsheet?


      Here's our current VBA code:



      Sub PUTVAL()
         Dim numoftags As Integer
         Dim stime As String     'Timestamp
         Dim nDays As Integer 'Number of days
      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, j As Integer
         i = 0
         j = 4 'first field column
         While j < 28 'corresponds to the column count (28)
         'server is in E8
          Set MyPIServers = PISDK.Servers
          Set MyPIServer = MyPIServers(Range("PISERVER").Value)
          nDays = Range("NDIAS").Value + 1
          'get number of tags
          numoftags = 743
          While i < numoftags
            'timestamp is in column 2 (B)
            stime = Worksheets("PIMS").Cells(i + 8, 2).Text
            Dim agora As Date
            agora = Now()
            'Timestamp check. Don`t use future info, neither 10 days backwards
            If ((DateDiff("h", stime, agora) > 0) And (DateDiff("d", stime, agora)) <= nDays) Then
            TimestampString = Format(stime, "dd/mm/yyyy hh:mm:ss")
            TimestampDate = CDate(TimestampString)
            timestamp.LocalDate = TimestampDate
            Value = Worksheets("PIMS").Cells(i + 8, j).Value
            Set Tag = MyPIServer.PIPoints(Worksheets("PIMS").Cells(7, j).Value)
            'Check for no data
            If (Value <> "") Then
                  'Call the PIPutVal() macro function
                  Tag.Data.UpdateValue Value, timestamp, dmReplaceDuplicates, Nothing
            End If
            End If 'End Timestamp check
            'move down to the row number
            i = i + 1
          i = 0
          j = j + 1
      End Sub