[Calc, Base, Basic] Logging DDE to sheet or database
Posted: Sun Feb 05, 2017 8:49 am
The full documentation can be found in the attached zip archive as a more readable text document
Abstract
This is about two spreadsheet templates with Basic macros logging incoming DDE data either to a spreadsheet list or to a registered data source. The templates are customizable. You can move around cells and rename sheets as long as range names remain intact. The macro code reads configuration data from named cells and cell ranges. A third spreadsheet document served me as an example source for testing. It may serve you as a first demo.
A self-contained database document serves as demo database for the database logging.
Known issues
The database log does not work with dBase connections in LibreOffice. The database log has been tested successfully with embedded and external HSQL in both office suites and with dBase in OpenOffice4.
Mind that the spreadsheet log constantly writes data into a spreadsheet which consumes memory and needs manual saving from time to time. At one log row every 3 seconds you hit the row limit after 36 days of logging. It is definitively safer to store your data in a database.
After changing the DDE source it may be (sometimes?always?) nessecary to clean up the DDE links. Edit>Links... break all links. Then hit Ctrl+Shift+F9 or reload the document.
MySQL should work out of the box if If the ANSI_QUOTES SQL mode is enabled. If not, you can change the following line ...
Const cQuote = """" [4 double-quotes]
... to ...
Const cQuote = "`" [a backtick within double-quotes]
I deduced this info from the MySQL documentation. I have nothing to test right now.
For detailed instructions read the documentation file and don't hesitate to ask questions in the Calc forum. If you have questions about the code, use the Macro/API forum.
Abstract
This is about two spreadsheet templates with Basic macros logging incoming DDE data either to a spreadsheet list or to a registered data source. The templates are customizable. You can move around cells and rename sheets as long as range names remain intact. The macro code reads configuration data from named cells and cell ranges. A third spreadsheet document served me as an example source for testing. It may serve you as a first demo.
A self-contained database document serves as demo database for the database logging.
Known issues
The database log does not work with dBase connections in LibreOffice. The database log has been tested successfully with embedded and external HSQL in both office suites and with dBase in OpenOffice4.
Mind that the spreadsheet log constantly writes data into a spreadsheet which consumes memory and needs manual saving from time to time. At one log row every 3 seconds you hit the row limit after 36 days of logging. It is definitively safer to store your data in a database.
| Edit: More known issues |
MySQL should work out of the box if If the ANSI_QUOTES SQL mode is enabled. If not, you can change the following line ...
Const cQuote = """" [4 double-quotes]
... to ...
Const cQuote = "`" [a backtick within double-quotes]
I deduced this info from the MySQL documentation. I have nothing to test right now.
For detailed instructions read the documentation file and don't hesitate to ask questions in the Calc forum. If you have questions about the code, use the Macro/API forum.