Automation via SQL

The QueryStorm preprocessor can be used to set up automation for a SQL query by allowing users to specify:

• when the query should be executed
• where any query results should be written to

Example

Suppose we want to populate an Excel table with sales orders for a given date. The date should be specified in a cell named orderDate. Whenever the value of the date cell changes, we want the script to re-execute and output its results into the table.

Here's how we might do that:

{handler (orderDate)}

-- output results into the 'orders' table
{@orders}
select
    *
from
    Sales.SalesOrderHeader soh
where
    OrderDate = @orderDate -- read the orderDate cell's value

In order to initialize the app, the script needs to be saved (Ctrl+S), and the project built (Ctrl+Shift+B). When the build completes, the workbook app is saved into the workbook and the runtime loads and activates it.

Outputting query results

If the query returns data from the database (rather than writing data to the database), the query results can be written into the workbook by adding an output directive above it. In the example above, the {@orders} output directive is used to send the results of the query into an Excel table called orders.

The syntax of the output directive is very simple: {@table_name}

The output directive should be placed above the select query whose results it should output. If there are multiple select queries in the script, each of them can have its own output directive, so multiple tables can be updated from the same script.

Specifying triggers

To enable a query to be executed automatically, it must be declared as an event handler. The preprocessor syntax for declaring an event handler is: {handler (eventsList)}. The event list is a comma-separated list of events that should trigger the execution of the command.

Reacting to range changes

For each named cell, an event with the same name is fired each time the cell value changes. In the above example, the orderDate event is specified as the only trigger, meaning that the command will execute every time the orderDate cell's value changes.

Reacting to ActiveX button clicks

To handle the click of an ActiveX button, we should use the following syntax: {handler (sheetName!buttonName)}, for example {handler (Sheet1!CommandButton1)}.

Reacting to VBA events

Arbitrarily named events can also be sent from VBA and used to trigger the execution of commands. To send an event from VBA, use the QueryStorm.Runtime.API class:

CreateObject("QueryStorm.Runtime.API").SendEvent("myEvent")

The event can be handled using the preprocessor like so: {handler (myEvent)}

Scripts can handle multiple events. Event names should be separated by a comma:

{handler (myEvent, orderDate, Sheet1!CommandButton1)}

{@orders}
select
    ...

Accessing workbook tables and variables

To allow the script to access Excel tables, the tables must be included when configuring the script via the "Connect" dialog.

This will ensure the table is copied into a temp table before running the query.

All cells with assigned names are visible inside scripts as parameters. The code in the example above uses a cell called orderDate as a parameter.

Async event handlers

Event handlers can also be asynchronous, meaning that they will not block Excel while they executing. To declare an async handler, simply use the async keyword, for example:

{async handler (Sheet1!CommandButton1)}
...