The executable is available via the following link: PlodWare-SQLite-OLE 2020-02-29
Video(s) on the program are at the end of this page.
Well, this application is my (current) pièce de résistance.
It consists of a embedded SQL engine, for which the entire praise and respect belongs to the SQLite team with whom I have no association, and a GUI that I wrote that wraps around the engine.
My pride and joy is that the GUI can access applications that support the Object Linking and Embedding technology developed by Microsoft.
Key features of the application are:
- No installation necessary
- No use of the registry
- No phone home
- Uses the SQLite embedded database engine
- Is a single file
- Supports scripting so that command sequences can be taken from a file
- Import/export between database tables and Comma Separated Variable files
- Supports control of OLE servers e.g. Excel, Word, PowerPoint, Outlook, Internet Explorer, and…
- Has some built-in commands to simplify interfacing with a Excel application
Initially I wrote a simple Windows shell/GUI around the SQLite engine.
Then I realized that I needed to exchange data with applications such as Excel, but I decided against accessing the raw Excel files. So I decided to support Comma Separated Variable files as the basis for data exchange with Excel.
But whilst CSV worked, I wanted to be able to output data from the SQL database and format it. Rather than exporting CVS from the DB, then importing the CSV into Excel and then formatting, I wanted a more direct route.
I knew about the OLE concept, but thought it might me too complicated for me to use, well my PlodWare programming effort is only a hobby! But then I decided to try out the Win32 API CreateObject, and behold I could start an instance of Excel from my GUI. I have expanded the GUI to provide a limited interpreter capability, broadly similar to VBA, that lets me write command sequences in my GUI and execute the commands in an OLE server.
I must say that writing such programs is a hobby, so there are some restrictions on functionality. As and when the restrictions really annoy me, then I might consider upgrading the program to overcome the restrictions.
There are restrictions in SQLite-OLE. For example, SQLite-OLE does not access any application related library file.
So, in the following example, it is up to you to know that myExcel has a visible component and that visible accepts a value, and that the FileDialog takes a single value as its input.
newscript dim myExcel = createobject ("Excel.Application") myExcel.visible=1 dim fd = myExcel.FileDialog(3) fd.Show message = fd.InitialFileName
Sorry, but SQLite-OLE does not provide any significant support in providing names or values of the OLE application being manipulated.
|msoFileDialogFilePicker||3||File picker dialog box.|
|msoFileDialogFolderPicker||4||Folder picker dialog box.|
|msoFileDialogOpen||1||Open dialog box.|
|msoFileDialogSaveAs||2||Save As dialog box.|
On the other hand, it is (in my un-humble opinion) a useful tool that lets me have the power of SQL coupled with data exchange to Excel, and a certain VBA-like macro ability in a single application that is less than 3 Mbyte in size.
Here is a 30 minute introduction on using SQLite-OLE.