As he explained to me, the structure and operation of those speadsheets, I found that one of the bottlenecks, in some of the processes, was data aquisition, because very frequently his users have to produce printed reports, using data extracted from databases, and manually insert the information contained in those reports into the spreadsheets. That's not really a good practice because, despite having to enter data twice (in the database app and into the Excel spreadsheet), it could lead to typing errors and thus generating wrong information.
To minimize that problem, I introduced him to PocketFox, in order he could easyly extract data from databases, via the SQL Exec function of the ODBC Manager, and export the extracted data to Excel spreadsheets. Doing so, he just had to change his spreadsheets to link to the SQL Exec's generated Excel spreadsheets, eliminate the data entry code (VBA) thus avoiding the typing errors and speeding up the process. He liked the idea and we set up a meeting in order he could show me the spreadsheets and I could show him the operation of PocketFox.
That seemed fine, but something was bugging me. Thought it was not a good idea to put PocketFox in the hands of people not qualified to operate it (PocketFox has many "power user" functionalities). My friend would have no problem in doing that (he is a "power user"), but people at his clients, responsible for the spreadsheets operation, certainly would face problems (or even create them).
Then I had the idea of creating a "batch" program that would interpret a series of commands, designed to execute the tasks needed to connect to an ODBC datasource, extract data from databases using SQL commands, like PocketFox SQL Exec function does, and exporting to Excel files (as many other file formats). All this without any operator intervention.
For that purpose I decided to create such program, that I called FireFly. Thinking a bit further understood that the development of FireFly could be faster, and easier to debug, if I implemented the designed functions in PocketFox first, as one of its many functionalities, as a procedure that could, latter, after being implemented and tested, be called by FireFly. So that's what I did.
So, FireFly is nothing more than a "shell" that calls that PocketFox procedure (and some others as well).
I defined a set of "@" commands in order they could be easyly interpreted by the PocketFox/FireFly scripting routine. The syntax of those commands are very simple and straightforward.
FireFly can be executed in two different modes. One mode is to activate it passing a filename identifying the script to be executed as a parameter (batch mode). The other one is by passing no parameters at all, in order to execute FireFly interactivelly. This latter mode presents all functionalities needed to create/modify a script and test it, before releasing to the final users, that will activate FireFly in the batch mode.
The batch mode is useful because it doesn't requires user intervention to run. The interactive mode is useful because the scripts developer doesn't need PocketFox to edit and test his scripts.
Both FireFly and PocketFox have an option ('Show Script Syntax') that presents the commands syntax available for that specific version of the program being executed. It also shows many examples of command usage.
FireFly doesn't need, or mantain, any additional files for its operation. FireFly doesn’t have to be installed, all you need to run it is its executable and the VFP 9 SP2 Runtime. And FireFly is free! (you can use it at your own risk)
PocketFox/ExecScript source code is available here. Fernando Alvares, December 29, 2012
ExecScript name was changed to FireFly. Fernando Alvares, January 11, 2013
FireFly 1.3.3061 (BETA) ready for download. Many new functionalities were added, like FTP commands and a new scripting language (PSL). Fernando Alvares, January 21, 2013