[Calc, Base, Basic] Logging DDE to sheet or database

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc, Base, Basic] Logging DDE to sheet or database

Post by Villeroy »

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.
 Edit: More known issues 
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.
Attachments
DDE_Logger.zip
2 templates, 1 demo database, 1 demo source sheet and 1 documentation file
(105.47 KiB) Downloaded 397 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply