The Excel add-in for DeltaV Reporter includes support for writing a custom VBA program that accesses historical data read from the DeltaV Continuous Historian or Event Chronicle database using DeltaV Reporter. This topic describes the VBA objects, methods, and properties provided for using a custom VBA program with DeltaV Reporter. The DeltaV software includes a sample VBA program for writing historical data.
VBA access to DeltaV Reporter is achieved by connecting to a DeltaV Continuous Historian server with the Connect method of the built-in DeltaVCHAddIn object.
To use the DeltaVCHAddIn object, you first must establish a reference in VBA to the DeltaV.Historian.Excel.AddIn.tlb type library file, as described in the following steps:
If the Available References list in the References - VBAProject dialog does not include a check box for DeltaV_Historian_Excel_AddIn, click Browse, navigate to the C:DeltaV\bin folder, and then open the DeltaV.Historian.Excel.AddIn.tlb type library file.
Connect method
The Connect method connects to the specified DeltaV Continuous Historian server in order to write data samples. It returns a DeltaV_Historian_HDE_XLA.Connection object.
expression.Connect(connection)
expression — A required expression that returns a DeltaV_Historian_HDE_XLA.Connection object.
connection — A required string, containing the name of the DeltaV Continuous Historian server to connect to.
None.
Example
The following sample code illustrates how to access the DeltaVCHAddIn object after establishing a reference in VBA to the DeltaV.Historian.Excel.AddIn.tlb type library file:
Dim HDE As DeltaV_Historian_Excel_AddIn.DeltaVCHAddIn
Set HDE = Application.COMAddIns.Item("DeltaV.ContinuousHistorian").Object
The following sample code illustrates how to connect to the DeltaV Continuous Historian server by using the Connect method after accessing the DeltaVCHAddIn object:
Dim cnn As DeltaV_Historian_Excel_AddIn.Connection
Set cnn = HDE.Connect("localhost")
The Connection object provides a method through which it is possible to write a number of manually gathered data samples to the server. Writing samples in this way is treated as an atomic operation, meaning that it will either be 100% successful; or it will fail completely without any data being recorded in the DeltaV Continuous Historian database. The Connection object provides a property through which a collection of errors relating to the most recent write operation can be accessed.
WriteSamples method
The WriteSamples method writes data samples to a DeltaV Continuous Historian server. It returns the number of samples written. No data are stored in the server unless all of the data samples can be written without error.
expression.WriteSamples(timeMode, range)
expression — A required expression that returns a DeltaV_Historian_HDE_XLA.Connection object.
timeMode — A required string that specifies how time stamps are interpreted. Valid values are:
Local — Time stamps are treated as being in the time zone of the client PC. They are converted to GMT for storage in the DeltaV Continuous Historian database. Conversion to GMT takes into consideration whether the time stamp falls within daylight savings time.
GMT — No conversion is necessary since all samples are stored by the DeltaV Continuous Historian with GMT time stamps.
GMT±"hh:mm" — The time stamp is adjusted accordingly. Daylight savings time has no effect. For example, if GMT–"04:00" is specified, the times in the spreadsheet relate to a region that is four hours behind GMT. Therefore, four hours is added to each time stamp as the samples are stored in the database.
range — A required rectangular range of cells where each row contains data for a single data sample to be written. Cells in each row must be interpretable in order as:
Entirely blank rows are ignored and cells are considered blank if they are empty (i.e. ISBLANK = TRUE) or if they contain only spaces. If the range includes the optional fourth or fifth columns, all cells in those columns must contain explicit values.
If the operation fails, the VBA Err object contains the error number for the first object in the Errors collection. The values of the Number and Description properties of the first Error object in the Errors collection will match the values of the Number and Description properties of the VBA Err object.
If there is no valid Connection object, the VBA Err object is the only source for information about DeltaV Reporter errors.
Example
Sub WriteSamplesExample(dvch As DeltaVCHAddIn, _
connectionString As String, timeModeString As String, _
dataRange As Range)
On Error GoTo ERR_HANDLER
Dim cnn As Connection
Set cnn = dvch.Connect(connectionString)
Dim nSamples As Integer
nSamples = cnn.WriteSamples(timeModeString, dataRange)
MsgBox "Done. " & nSamples & " written by " & cnn.DeltaVUsername
Exit Sub
ERR_HANDLER:
If cnn Is Nothing Then
''' No Connection object, so no Errors collection.
''' The standard VBA Err object has details of the error.
MsgBox "Err = 0x" & Hex(Err.Number) & " " & Err.Description
Else
''' We have a Connection object, so we can get the Errors
''' collection. The standard VBA Err object also has the
''' Number and Description of the first Error in the
''' collection.
MsgBox "Number of errors = " & cnn.Errors.Count & "." & _
"VBA Err = 0x" & Hex(Err.Number) & " " & _
Err.Description
End If
End Sub
Errors property — Returns a DeltaV_Historian_HDE_XLA.Errors object that allows you to examine errors relating to the previous WriteSamples operation.
DeltaVUserName property — Returns the name of the currently logged in DeltaV user on the node previously used to obtain a connection. If no user is logged onto DeltaV on that node, an empty string is returned.
A collection of Error objects that represents all the errors relating to the previous WriteSamples operation performed with the Connection object.
In addition to its Clear method, the Errors collection also includes standard methods and properties relating to collections, supporting use of the collection in VBA looping constructs such as For Each… Next.
Clear method
The Clear method removes all the Error objects from the Errors collection. When an error occurs (during a WriteSamples operation, for example), the Errors collection is cleared, then filled with Error objects based on the new error.
Clear()
Example
Dim errors As DeltaV_Historian_Excel_AddIn.Errors
Set errors = cnn.Errors
If errors.Count > 0 Then
MsgBox errors.Count & " errors occurred."
End If
Dim error As DeltaV_Historian_Excel_AddIn.Error
For Each error In errors
MsgBox error.Description
Next
The Error object provides properties relating to an error that occurred during a WriteSamples operation.
Number property — Returns a Long value that identifies the error. Use the Number property to determine which error occurred.
Description property — Returns a String value that contains a description of an Error object. Use the Description property to obtain a short description of the error that you can display to alert the user.
RowNumber property — Returns a Long value to indicate the number of the row associated with the error. A return value of 1 indicates the first row in a range. A return value of 0 indicates that the error relates to the operation as a whole.
Example
Sub RowNumberExample(ByVal cnn As Connection, _
ByVal connectionString As String, _
ByVal timeModeString As String, _
ByVal dataRange As Range)
On Error GoTo ERR_HANDLER
Dim nSamples As Integer
nSamples = cnn.WriteSamples(timeModeString, dataRange)
MsgBox("Done. " & nSamples & " written")
Exit Sub
ERR_HANDLER:
Dim errors As DeltaV_Historian_Excel_AddIn.Errors
errors = cnn.Errors
If errors(0).Row > 0 Then
MsgBox("Error caused by sample at " & _
dataRange.Cells(errors(0).RowNumber, 1).Value & _
" (" & timeModeString & ")")
End If
End Sub
ColumnNumber property — Returns a Long value to indicate the number of the column associated with the error. A return value of 1 indicates the first column in a range. A return value of 0 indicates that the error relates to the operation as a whole. You can use the ColumnNumber property in conjunction with the RowNumber property to determine the precise cell within the data range that relates to the error.
Example
Sub ColumnNumberExample(ByVal cnn As Connection, _
ByVal connectionString As String, _
ByVal timeModeString As String, ByVal dataRange As Range)
On Error GoTo ERR_HANDLER
Dim nSamples As Integer
nSamples = cnn.WriteSamples(timeModeString, dataRange)
MsgBox("Done. " & nSamples & " written")
Exit Sub
ERR_HANDLER:
Dim errors As DeltaV_Historian_Excel_AddIn.Errors
errors = cnn.Errors
Dim E As DeltaV_Historian_Excel_AddIn.Error
For Each E In cnn.Errors
If (E.RowNumber > 0 And E.ColumnNumber > 0) Then
''' This error relates to a single cell in the
''' dataRange.
Dim badCell As range
badCell = dataRange.Cells(E.RowNumber, E.ColumnNumber)
''' Color the offending cell red...
badCell.Interior.ColorIndex = 3
End If
Next
End Sub
Source property — Returns a String value that contains a description of the error and the name of the object or application that originally generated the error.
HelpFile property — Returns a String value that evaluates to a fully resolved path to a help file associated with an Error object. If there is no specified help file, the HelpFile property returns a zero-length string.
HelpContext property — Returns a Long value that identifies the context ID for a topic in a help file. If a help file is specified in the HelpFile property, you can use the HelpContext property to display the topic associated with an Error object. Otherwise, the HelpContext property returns 0.