bmartins

Send bulk data to PI from an Excel spreadsheet

Discussion created by bmartins on Dec 4, 2013
Latest reply on Dec 5, 2013 by Gregor

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

 

 

Outcomes