The lab equipment has a RS232 port, which outputs a measurement data every 10 minutes. I would like to use parsed values in forms, formulas and my VBA logic in a Microsoft Excel spreadsheet.
It is assumed that:
You've configured communication parameters (baud rate, number of data bits, flow control etc) in the data logger and can receive any data without communication errors.
You can work in Microsoft Excel, specifically create and use forms.
Note: This tutorial is based on the previous tutorial named "Serial port data and Excel. Real time charting". This example will use the parser settings and the data example from this tutorial. Therefore you may read it before continue.
The parser is ready we've tested it in the previous tutorial. If the parser had been correctly set up, then you should see all parser items names and their values (fig.1) in the DDE server window below.
Fig.1. Serial port data, Excel and DDE. DDE server window.
All parser items are now ready for usage from Microsoft Excel. The variable name is displayed in the "Name" column. You should know the DDE server name and the topic name to retrieve values via DDE. This information exists on the following page (fig.2 pos.1). Please, note that the server name may be different on your PC.
Fig.2. Serial port data, Excel and DDE. DDE server parameters.
Click the "OK" button and close the DDE Server plug-in configuration window and the "OK" button in the options window.
Because we want to draw a chart in a real-time environment then we should prepare an Excel file before the next step. In this example we've created a diagram with 3 plots. Each plot is based on a data from columns A, B or C. We'll place FLOW1, VOLW1 and TEMP1 to a corresponding column and the chart will be automatically updated every time. We'll use last 30 values on the diagram only. In this example, we've placed 3 links to DDE items to cells D21-F21 like: "=testcore|ddesrv!srviFLOW1" (w/o quotes) (fig.3 and 4). As you can see, the server name, the topic name and the variable name are specified in this DDE link.
Fig.3. Serial port data, Excel and DDE. DDE links.
After you've placed the DDE link to the spreadsheet Microsoft Excel will start the data logger and retrieve the data automatically. If the data logger is already started then Microsoft Excel will try to read the data from the existing instance. If the data logger didn't receive and didn't parse the data yet, then Microsoft Excel will display a text about the broken link, but will read a value after successful parsing.
We are now ready for creating a VBA code, which will process the DDE data. This code will copy DDE values to columns, move old values and add rows to the history list (fig.4, 5).
Fig.4. Serial port data, Excel and DDE. Source spreadsheet.
The figure above contains following elements:
VBA code from the Excel file
Private Sub btnClear_Click() lbHistory.Clear 'MsgBox (CStr(Target.Row) + ":" + CStr(Target.Column)) End Sub Public Sub Process_Value(ByVal X_Coord As Long, ByVal ValueName As String, ByVal Value As Variant) Dim TmpValue As Variant Dim LastIndex As Long ' if we've read and non-numeric value, then replace it by ### in the grid If IsNumeric(Value) Then TmpValue = Value Else TmpValue = "####" End If ' search for an empty cell LastIndex = 0 For I = 1 To 30 If Cells(I, X_Coord).Value = "" Then LastIndex = I Exit For End If Next ' if we did not find an empty cell the move old value If LastIndex = 0 Then LastIndex = 30 For I = 2 To 30 Cells(I - 1, X_Coord).Value = Cells(I, X_Coord).Value Next End If ' place new value to the cell Cells(LastIndex, X_Coord).Value = TmpValue ' add new value to the history list lbHistory.AddItem (ValueName + ": " + CStr(TmpValue)) End Sub Private Sub btnClearData_Click() For I = 1 To 30 Cells(I, 1).Value = "" Cells(I, 2).Value = "" Cells(I, 3).Value = "" Next End Sub Private Sub btnClearHistory_Click() lbHistory.Clear End Sub Private Sub Worksheet_Calculate() On Error GoTo ErrHandler Application.EnableEvents = False Call Process_Value(1, "FLOW1", Cells(21, 4).Value) Call Process_Value(2, "VOLW1", Cells(21, 5).Value) Call Process_Value(3, "TEMP1", Cells(21, 6).Value) Application.EnableEvents = True ErrHandler: Application.EnableEvents = True End Sub
The Excel file, which we've created you can download here and use it in your work.
Okay, all settings have been completed and we are ready to capture data to an Excel file.
Connect your device and power it on if necessary. Check to see if you are able to receive a data block from the specified serial port. If the parser had been correctly set up, then you should see all parser items values (Fig.5) in the Excel spreadsheet.
Fig.5 Serial port data, Excel and DDE. Results.
The figure above contains following elements:
The Excel file with all values and plots you can download here.
This method of retrieving data is complex and might prove to be difficult for the unskilled user, but allows you to process the data as you want. Later, you may make the following improvements in this example like: