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

Creating Extension - 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 forum is not for asking questions about writing your own macros.

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

Postby Villeroy » Sun Feb 05, 2017 8:49 am

The full documentation can be found in the attached zip archive as a more readable text document

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.
2 templates, 1 demo database, 1 demo source sheet and 1 documentation file
(105.47 KiB) Downloaded 68 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests