O-O Classes for Data Acquisition directly into MS-Excel

[kranenborg]

Presented here are two O-O classes that provide a high-level ZBasic interface for exporting/importing data directly to/from MS-Excel using the Parallax PLX-DAQ macro and the serial COM port. In this way a quick-and-dirty method is available for exporting and importing arrays of data (real-time or stored data after a datalogging session) to and from tables in an MS-Excel worksheet in a user-defined format. Individual worksheet cells (text or numerical data) can be managed as well. No programming at the PC side is required. As a result, simple annotated graphs can be produced directly from logged data, and even a worksheet-based interactive user interface can be built with very little effort.

PLX-DAQ (Copyright: Parallax Inc.) is a VBA-script running in MS-Excel that monitors incoming data on a selected COM port. It interprets a number of predefined keywords in order to place data in - or get data from - a worksheet or to alter information on the control window that is maintained by the script. Furthermore some simple input from the control window (checkboxes) can be read and sent back to the microcontroller, thusly allowing the user to influence program execution flow at the microcontroller side. PLX_DAQ was previously known as SelmaDAQ and is freeware.

The ZBasic interface with PLX-DAQ consists of two main classes:
- PLX_DAQ_Controller, to configure and use the controller
- PLX_DAQ_Table (and its subclass variant PLX_DAQ_Matrix) for directly managing tables and matrices in Excel.

The public methods in the PLX_DAQ_Table class (or its PLX_DAQ_Matrix subclass variant) manage a datastream with PLX_DAQ compatible keywords for generating, manipulating and reading of an annotated table on the MS-Excel worksheet in a predefined fixed format. ZBASIC arrays (type: Single) with numerical data can be directly transformed into MS-Excel annotated tables and vice-versa. Table annotation can be generated automatically. Several tables can be managed at the same time by creating multiple objects. The PLX_DAQ_Table and PLX_DAQ_Matrix classes provide the following methods:

* Preparing a MS-Excel worksheet
- ClearSheet
* Manipulating individual worksheet cells (strings):
- WriteCell
- ReadCell
* defining a table (properties only, not actual content):
- SetTableProperties
- ChangeTablePosition
* writing/reading table content
- WriteTable (from a 2D Single array)
- WriteTableRecord (row, from a 1D Single array)
- Rewind (for use with WriteTableRecord and ReadTableRecord)
- ReadTable (into a 2D Single array)
- ReadTableRecord (row, into a 1D Single array)
- Duplicate (copy a table to another place)
- ClearTableData (clear table numerical contents)

The combination of the Controller and Table/Matrix classes allows an MS-Excel based worksheet to be used as a graphical user interface between the PC and the ZBASIC application. The simple application included here generates an annotated matrix from ZBASIC-generated data in MS-Excel and allows some simple manipulations (clear, duplicate) via the PLX-DAQ controller window which is configured by the ZBasic program as well. The example code is extensively documented. Also included is a screengrab of the application (the tables and all text are generated by the example program, only the graph was added later on by myself)

In order to run the example a PLX-DAQ Excelfile is included here (which contains everything you need, it includes already a graph definition tailored to the example). Take care of the following:
- You have to install the PLX-DAQ package at your computer first (available via the Parallax website: www.parallax.com/downloads/plx-daq). The installation contains a detailed help file (in the Parallax program folder of the Windows Start Menu), but it is not needed to understand the PLX-DAQ commands to be able to use the ZBASIC classes, unless you want to extend the latter.
- You have to specify the serial COM port number (the same one that is used by the programming editor, but only one program at a time can have access to it)
- Select the proper baud rate (19200)
- Generally you will unceck the option "Reset at connect" but for running the enclosed test program it is easiest to check this option
- After programming close the comm port in the Zbasic editor (Options menu), before opening the connection in PLX-DAQ.

The code described here has been developed on a bare ZX328L without any external I/O connections. PLX-DAQ was run successfully in MS-Excel 2003/2007/2010/2013.

I am very open to any comments regarding usefullness and quality of the program and its documentation!

Best regards,
Jurjen

[kranenborg]

It appeared that the code above did contain an error in the PLX_DAQ_Matrix class definition, so I replaced the atttachments in the above post with the correct ones, the example now describing the generation of an annotated matrix in MS-Excel

In the attachment below the communication between ZBASIC and MS-Excel in terms of PLX-DAQ commands that generate the example case above is shown for illustration.

/Jurjen

[kranenborg]

I have added a remark in the first post mentioning that the PLX-DAQ package (from the Parallax website) needs to be installed first in order to be able to use the MS-Excel macro (and the included example), this was not entirely obvious from the text and I ran into the issue after I obtained a new computer

/Jurjen

[stevech]

PLX-DAQ from National Instruments is said to be freeware.

http://www.parallax.com/tabid/393/Default.aspx

[kranenborg]

Although PLX-DAQ now has been moved to the downloads section of the Parallax website (www.parallax.com/downloads/plx-daq and no on-line help anymore), the above link by Stevech still works since it redirects properly. The PLX-DAQ installation has a help file itself (available under the All Programs section of the Windows Start Menu under Parallax), but this is not really needed when using the ZBasic plx_daq classes since their methods provide all basic functionality.

I have also verified that the example works with the new MS-Excel 2013 release.

/Jurjen

[kranenborg]

PLQ-DAQ is now available (upgrade to Windows 10 - likely compatible with W11) from the following link:
https://www.parallax.com/package/plx-da ... ft%20Excel

Regards,
Jurjen