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

    Send bulk data to PI from an Excel spreadsheet

    bmartins

      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:

       

      http://vcampus.osisoft.com/bloggers_place/b/hanyong/archive/2013/01/24/write-value-to-multiple-tags-or-attributes.aspx

       

      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)
          MyPIServer.Open
          
          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
          Wend
          i = 0
          j = j + 1
         Wend
         
      End Sub
      

       Thanks