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. 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 an MS-Excel worksheet in a standardized 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 which 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.

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 is everything you need, it includes already a graph definition tailored to the example). Take care of the following:
- 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.

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