QueryStorm's VBA API¶
QueryStorm provides an API that you can consume from VBA. The API is rather simple and currently only allows running and modifying embedded queries.
You can download the sample workbook from here!
Working with embedded queries¶
Embedded queries and scripts are accessed via the
GetQuery(path) method. The returned query object has the following methods:
//gets the contents of the embedded query string GetContents(); //sets the contents of the embedded query void SetContents(string value); //Runs the query asynchronously void RunAsync(string successCallback, string failCallback); //Runs the query synchronously Recordset Run();
Running a query synchronously¶
When running the query synchronously, the results can be used in the next line of VBA code, but Excel will not be responsive to user input while the query is running.
Sub Button1_Click() 'get the root API object Set api = Application.COMAddIns("QueryStorm").Object 'get the embedded query we want to run Set q = api.GetQuery("Query1") 'Run the embedded query Set data = Query.Run() '...do something with data if necessary... End Sub
Running a query asynchronously¶
Queries can be run asyncronously using the
RunAsync method. The method starts the background execution of the query and immediately returns. If you need to process the results, you can pass in the names of the two VBA subroutines that should be called upon successful/failed execution of the query. If the callbacks are not in a module (e.g. they're in a sheet) they need to be qualified, e.g.
Sheet1.Boo. You can pass in
Nothing or "" as a callback if you don't want to process success or failure.
Sub Button1_Click() 'get the root API object Set api = Application.COMAddIns("QueryStorm").Object 'get the embedded query we want to run Set q = api.GetQuery("Query1") 'Run the embedded query, specifying the success and error callbacks. Call q.Run("Yay", "Boo") End Sub ' success callback Sub Yay(data As Variant) Set result = data.Fields.Item(0) MsgBox ("The result is " & result) End Sub ' fail callback Sub Boo(message As String) MsgBox ("Error: " & message) End Sub
successCallback subroutine can have zero parameters if it doesn't care about the results. Alternatively, it can specify one parameter of type
Recordset, which will be used to pass in the query results.
failCallback subroutine must specify one
String parameter - the error message.
Since embedded queries specify the engine configuration themselves, nothing about the connection needs to be specified.