Configuring Event Handlers

Configuring Event Handlers

Preface

SaveToDB allows configuring Microsoft Excel applications using database objects.

You may configure:

  • Processing Excel cell Change events using SQL codes or stored procedures.
  • Opening drill-down queries in a popup window, web browser or on Excel sheets on DoubleClick events.
  • Refreshing related queries on SelectionChange events.
  • Executing various actions from the Excel context menu or the SaveToDB Actions menu.

Using database handlers for Excel event handling you may:

  • Change database data when Excel cells have been changed.
  • Protect selected columns, rows, or cells from changes.
  • Implement drill-down queries.
  • Implement master-detail interfaces.

Using the Excel context menu and SaveToDB Actions menu you may:

  • Execute database queries including stored procedures and SQL codes.
  • Open HTTP-queries in a web-browser, popup window, or on Excel sheets.
  • Open text file data in a popup window or on Excel sheets.
  • Execute Excel macros.
  • Execute Windows Shell and CMD commands.
  • Refresh data validation lists on specified sheets.
  • Change parameters of Excel data table queries (reset parameters, generate new document numbers, etc.).

Menu items can be translated into a business language using the object name translation feature.

Event handler parameters can be translated using the parameter name translation feature.

Event handler parameter values can be populated using the configuring parameter values feature.

Event Handler Configuration View

SaveToDB reads the event handler configuration from tables* and views that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_SCHEMA
  6. HANDLER_NAME
  7. HANDLER_TYPE
  8. HANDLER_CODE
  9. TARGET_WORKSHEET
  10. MENU_ORDER
  11. EDIT_PARAMETERS

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<DB object schema>
or <Object schema>
See table 2.1
<Object name>
See table 2.1
[<Column name>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
ConvertFormulas (v7.2)
DoNotConvertFormulas (v7.2)
DoNotSelect (v6.2)
DoNotSave (v6.2)
DoNotChange (v6.2)
ProtectRows (v6.9)
Formula (v6.2)
FormulaValue (v6.2)
ValidationList (v6.2)
SelectionList (v6.2)
<Handler schema>
See table 2.2
<Handler name>
See table 2.2
<Handler type>
See table 2.2
<Handler code>
See table 2.2
[<Output target>]
See table 2.3
[<Item order in
ContextMenu or
Actions menu>]
[1 or 0]
Table 2.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
<Object schema><SQL code name>
<Object schema><HTTP-query name>
<Object schema><text-query name>
Table 2.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
<Procedure schema><Procedure name>[PROCEDURE] 
<Function schema><Function name>FUNCTION 
<Table schema><Table name>TABLE 
<View schema><View name>VIEW 
<Object schema><SQL code name>CODE<SQL code>
<Object schema><HTTP-query name>HTTP<HTTP-query>
<Object schema><text-query name>TEXT<File name>[;CodePage=<Code page>]
 MenuSeparator[MENUSEPARATOR] 
<Object schema><Menu item name>MACRO<Excel macro> [<Parameters>]
<Object schema><Menu item name>CMD<Windows Shell or CMD command>
 <Handler name>RANGE (v7.1)<Range name or address>
 <Handler name>VALUES (v7.1)<Values>
<Object schema><Menu item name>REFRESH 
Table 2.3
TARGET_WORKSHEET
<Sheet name> [<Window position>]
_new [<Window position>]
_self
_none
_popup
_browser
_taskpane
_reload
_transpose
_transposeauto
_saveas [<File name>[;CodePage=<Code page>]]
<Sheet list for REFRESH>
Table 2.4
Window Position
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

Event Handler Configuration View 3.x - 4.x

SaveToDB also reads the event handler configuration from the views* in the previous format that contain the following fields (without HANDLER_CODE):

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_SCHEMA
  6. HANDLER_NAME
  7. HANDLER_TYPE
  8. TARGET_WORKSHEET
  9. MENU_ORDER
  10. EDIT_PARAMETERS

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
<DB object schema>
or <Object type>
See table 3.1
<Object name>
See table 3.1
[<Column name>]Actions
Change
ContextMenu
DoubleClick
SelectionChange
<Handler schema>
See table 3.2
<Handler name>
See table 3.2
<Handler type>
See table 3.2
[<Output target>]
See table 3.3
[<Item order in
ContextMenu or
Actions menu>]
[1 or 0]
Table 3.1
TABLE_SCHEMATABLE_NAME
<Table schema><Table name>
<View schema><View name>
<Procedure schema><Procedure name>
CODE<SQL code name>
HTTP<HTTP-query name>
TEXT<text-query name>
Table 3.2
HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPE
<Procedure schema><Procedure name>[PROCEDURE]
<Function schema><Function name>FUNCTION
<Table schema><Table name>TABLE
<View schema><View name>VIEW
<SQL code><SQL code name>CODE
<HTTP-query><HTTP-query name>HTTP
<File name>[;CodePage=<Code page>]<text-query name>TEXT
MenuSeparator[MENUSEPARATOR]
<Excel macro> [<Parameters>]<Menu item name>MACRO
<Windows Shell or CMD command><Menu item name>CMD
<Menu item name>REFRESH
Table 3.3
TARGET_WORKSHEET
<Sheet name> [<Window position>]
_new [<Window position>]
_self
_popup
_browser
_reload
_transpose
_transposeauto
_saveas [<File name>[;CodePage=<Code page>]]
<Sheet list for REFRESH>
Table 3.4
Window Position
_TopWindow
_LeftWindow
_RightWindow
_BottomWindow

TABLE_SCHEMA is ignored on Microsoft SQL Server Compact and SQLite.

There are two differences with the SaveToDB 5.0 configuration format:

  • Custom codes from HANDLER_SCHEMA are moved to HANDLER_CODE.
    As a result, custom handlers may have different object schemas in HANDLER_SCHEMA. These schemas are used in other configuration tables (translations, parameters).
  • TABLE_SCHEMA can contain different object schemas specified in the query list configuration table but not only predefined schemas (CODE, HTTP, TEXT).

Event Handler Configuration View 1.x - 2.x

SaveToDB also reads the event handler configuration from the views in the SaveToDB 1.x-2.x format with the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. EVENT_NAME
  5. HANDLER_NAME
  6. TARGET_WORKSHEET

However, this format does not allow configuring SaveToDB 3.x features.

Event Handler Configuration View Fields

Mandatory TABLE_SCHEMA and TABLE_NAME specify an Excel data table query object that is being handled. See Table 2.1 or Query List configuration.

Optional COLUMN_NAME specifies a name of a column that is being handled. If the value is NULL, the handler is used for the entire table.

Possible values of the EVENT_NAME field:

EVENT_NAMEDescription
ActionsAn item of the SaveToDB Actions menu
ContextMenuAn item of the Microsoft Excel context menu
ChangeA cell change event handler
DoubleClickA DoubleClick event handler
SelectionChangeA SelectionChange event handler
DoNotSelect (v6.2)A handler prevents selecting a column in the Data Connection Wizard.
DoNotSave (v6.2)A handler prevents saving column values to a database.
DoNotChange (v6.2)A handler prevents changing column values.
ProtectRows (v6.9)A handler prevents adding and deleting rows.
Formula (v6.2)A handler replaces column values with an Excel formula specified in the HANDLER_CODE field.
Formula Value (v6.2)A handler replaces column values with a value calculated by a formula specified in the HANDLER_CODE field.
You may use Excel formulas and special formulas =DomainUserName() and =UserName().
Validation List (v6.2)A handler loads values from a specified handler object to the hidden SaveToDB_Lists worksheet and creates a validation list for the specified column.
Use Reload, Reload Data and Configuration to reload validation lists.

* Event types are available in SaveToDB 6.2 or higher.

Possible values of the HANDLER_SCHEMA, HANDLER_NAME, HANDLER_TYPE, and HANDLER_CODE fields:

HANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODEDescription
<Procedure schema><Procedure name>[PROCEDURE] Executing a database stored procedure
<Function schema><Function name>FUNCTION Executing a database function
<Table schema><Table name>TABLE Selecting all data from a database table
<View schema><View name>VIEW Selecting all data from a database view
<Object schema><SQL code name>CODE<SQL code>Executing an SQL code
<Object schema><HTTP-query name>HTTP<HTTP-query>Opening an HTTP-query
<Object schema><text-query name>TEXT<File name>[;CodePage=<Code page>]Opening data from a text file
 MenuSeparator[MENUSEPARATOR] Inserting a menu separator
<Object schema><Menu item name>MACRO<Excel macro> [<Parameters>]Executing an Excel macro
<Object schema><Menu item name>CMD<Windows Shell or CMD command>Executing a Windows Shell or CMD command
<Object schema><Menu item name>REFRESH Refreshing Excel queries on specified sheets

The SaveToDB 3.x-4.x format has no HANDLER_CODE field. The object codes can be stored in the HANDLER_SCHEMA field.

The SaveToDB 1.x-2.x format with the single HANDLER_NAME field is also supported:

HANDLER_NAMEDescription
<Schema>.<Name>Executing a database stored procedure
<HTTP-query>Opening an HTTP-query. The HTTP-query format: [<Handler Name>=]<URL>

Possible values of the TARGET_WORKSHEET field:

TARGER_WORKSHEETDescription
<Sheet name> [<Window position>]The result is inserted on the specified sheet. The sheet is created if not exists.
_new [<Window position>]The result is inserted into a new table on a new sheet. The new table is used for further requests.
_selfThe result replaces the active table query.
_noneThe result is ignored and any window is not created.
_msgboxThe result is outputted into a standard MsgBox window (SaveToDB 6.9 or higher).
_popupThe result is outputted into a popup data window.
_browserThe result is outputted into the default web browser (for HTTP) or the internal browser (for others).
_taskpaneThe result is outputted on task panes. This is a new SaveToDB 5.0 feature.
_reloadThe result is used to set active query parameters. The query is reloaded with the new values.
Return new parameter values in the first row in the columns with parameter names.
_transposeThe first result row is transposed to a name and value table in _popup or _taskpane modes.
_transposeautoThe result row is transposed to a name and value table if the result contains one row only.
_saveas [<File name>[;CodePage=<Code page>]
[;Format=CSV][;Separator=;/,/Tab]]
The result is exported to a CSV file.
Only the CSV format is supported. Available separators: comma, semicolon, and tab.
<Sheet list>Excel queries on the specified sheets are refreshed in the REFRESH handler mode.
NULLHTTP-queries are opened in the default web browser.
For database output:
if the result is a data table, then the data popup window is used;
if the result is a single value, then the internal SaveToDB web browser is used.

The TARGET_WORKSHEET field can contain an optional window position (with a leading space) in addition to the <Sheet name> or _new values:

Window PositionDescription
_RightWindowThe result is opened in the right Excel window.
_TopWindowThe result is opened in the top Excel window.
_LeftWindowThe result is opened in the left Excel window.
_BottomWindowThe result is opened in the bottom Excel window.

Using related windows for SelectionChange handlers allows implementing master-detail interfaces.

The window is activated with the following rules:

  1. The target sheet does not exist and is created for the first time.
  2. The target sheet exists and the active window is normal, not maximized.

You may maximize the active window to prevent refreshing detail data in related windows.
Then you may restore the window to return to the master-detail mode.

The MENU_ORDER field allows sorting menu items. Use integer values.

The EDIT_PARAMETERS field allows changing the default behavior for launching menu items:

  • Context menu items are executed without parameter value confirmation.
  • Actions menu items are executed using the Edit Parameter Values dialog box.

The behavior is different as the context menu handlers have table row values in any case and the Actions menu handlers may have not.

Configuration View Example

There is an example of the configuration view (in SaveToDB 4.x format):

Example comments:

  • All the rules are applied to the xls10.uspCalendar stored procedure.
  • The event handlers are defined for all columns of the table as the COLUMN_NAME field is empty (not shown).
  • The context menu handlers are ordered (1, 2, and 3) and are divided by MenuSeparator (2).
  • The same uspExcelEvent_DoubleClick_xls11_uspCalendar stored procedure is used to handle multiple events (Actions, DoubleClick, and SelectionChange).
  • The results of the SelectionChange event handler are inserted on a sheet in the right window.
  • The results of the Google Search handler are opened in a web browser as an HTTP-query.
    The HTTP-query is specified in the HANDLER_CODE field, and the HTTP type is specified in the HANDLER_TYPE field.

Event Handler Parameters

Event handler parameters are populated from the following sources:

Value SourceComment
A table column of the same nameSource object column names without translation are used.
A ribbon parameter of the same nameRibbon parameters are shown for queries inserted into Excel tables.
Ribbon parameters are being synchronized with Excel named cells. Parameters with predefined names (see below) are not shown on the ribbon.
An Excel named cell of the same nameExcel named cell value
An active query parameter of the same nameFor example, event handlers of EXEC dbo.uspCustomers @ManagerID=101 can use a value of the ManagerID parameter.
Predefined parameters:
WorkbookName, workbook_nameActive workbook name without directory
WorkbookPath, workbook_pathActive workbook directory. The value is empty for new workbooks.
SheetName, sheet_nameActive sheet name
DataLanguage, data_languageData language code selected using SaveToDB Options
TableName, table_nameActive query object
EventName, event_nameEvent name as defined in the EVENT_NAME field
ColumnName, column_nameActive column name
CellValue, cell_valueActive cell value. Use text type for this parameter and convert the value to a required data type in SQL code.
CellAddress, cell_addressActive cell address
CellFormula, cell_formulaActive cell formula in the Range.Formula format
ChangedCellCount, changed_cell_countNumber of changed cells
ChangedCellIndex, changed_cell_indexNumber of command in the group of change cell handler commands
TransactionID, transaction_idUnique GUID of a group of executed commands.
TransactionStartTime, transaction_start_timeUTC start time of executing a group of commands.
WindowsUserName, windows_user_nameUser's Windows login
SaveToDbVersion, savetodb_versionSaveToDB version

Parameter value sources are shown in the priority order from highest to lowest.

You may implement universal handlers using the TableName, ColumnName, and CellValue parameters. The CellValue parameter must be the varchar type and should be converted to a destination type in the SQL code.

Item Names of Context Menu and Actions Menu

Handlers are specified in the HANLDER_SCHEMA and HANDLER_NAME fields.

You may translate handler names to multiple languages using the TRANSLATED_NAME field of the object name translation configuration.

Also, handler names can include parameter values specified as {ParameterName}.

For example, a handler configuration view contains the following handlers to open websites and profile URLs using the values of the Website and ProfileURL columns:

EVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
ContextMenuhttp32Open website - {Website}HTTP{Website}
ContextMenuhttp32Open profile - {ProfileURL}HTTP{ProfileURL}

You may translate handler names to multiple languages using the following object name translation configuration:

TABLE_SCHEMATABLE_NAMELANGUAGE_NAMETRANSLATED_NAMETRANSLATED_DESCTRANSLATED_COMMENT
http32Open website - {Website}enOpen website - {Website}  
http32Open profile - {ProfileURL}enOpen profile - {ProfileURL}  
http32Open website - {Website}esAbrir el sitio - {Website}  
http32Open profile - {ProfileURL}esAbrir el perfil - {ProfileURL}  
http32Open website - {Website}frOuvrir le site - {Website}  
http32Open profile - {ProfileURL}frOuvrir le profil - {ProfileURL}  
http32Open website - {Website}itApre il site - {Website}  
http32Open profile - {ProfileURL}itApre il profilo - {ProfileURL}  

Of course, base handler names themselves can be in any language.

However, be aware that TABLE_SCHEMA and TABLE_NAME values are used in multiple configuration views, and it is desired to have them stable.
Translated names can be changed as you need in one translation table only.

The example specifies the http32 schema name. SaveToDB 4.x and below can use only the predefined schemas for non-database objects named as HTTP, CODE, or TEXT.

Using SQL Code as Event Handlers

The SQL code can be specified in the HANDLER_CODE field.

The SQL code can contain parameters in the following formats:

  • @ParameterName for Microsoft SQL Server and Microsoft SQL Server Compact.
  • :ParameterName for Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, and PostgreSQL.

For example:

SELECT * FROM dbo.StockTradeHistory th WHERE th.Symbol = @Symbol

HTTP Queries as Event Handlers

The HTTP-query can be specified in the HANDLER_CODE field.

SaveToDB supports the standard scheme of URL parameters: ?ParameterName1=Value1&ParameterName2=Value2...

However, in the most cases, it is not suitable as the event handlers get parameter values from the table columns with the same name.

So, the HTTP-query parameters can be redefined in the following formats: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22{Symbol=YHOO}%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys

The example URL contains the Symbol parameter with the default value equal YHOO.

SaveToDB allows connecting to web pages and web services including OData web services.

SaveToDB supports the following data formats: XML, JSON, HTML, and CSV.

To insert table data on a sheet, the web data are parsed by smart algorithms that do not require customizing.

If a web page is not parsed correctly, you may send us a request. We will try to change the algorithms.

The SaveToDB add-in allows connecting to web data sources that require authorization.

SaveToDB supports the following authorization methods: Windows, Forms, Basic, OAuth 1.0, and OAuth 2.0.

SaveToDB supports the following OAuth providers: Google, Facebook, LinkedIn, Twitter, Microsoft Azure Marketplace, Windows Live, Yahoo, and Yahoo API Key.

You may use HTTP-queries in the Actions menu to show complete documentation about the active Excel table query.

You may use HTTP-queries to open websites or web pages defined in Excel table cells.

For example, if a table contains the Website and ProfileURL fields then you may create the following handlers to open website and profile URLs:

EVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
ContextMenuhttp32Open website - {Website}HTTP{Website}
ContextMenuhttp32Open profile URL - {ProfileURL}HTTP{ProfileURL}

The menu items are added only for non-empty fields.

If the Website field does not contain http:// then specify the handler as http://{Website}.

Text File Queries

The file name and the code page can be specified in the HANDER_CODE field in the following format:

<File name>[;CodePage=<Code page>]
For example:
Contacts.csv;CodePage=65001

The file name can contain a relative path to the workbook directory.
In this case, the file can be moved with the workbook to another place with the same connection string.

Queries can contain parameters in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

{FileName=Contacts.csv};CodePage={CodePage=65001}

In this case, the values of the FileName and CodePage parameters are populated from the context and can be changed by a user.

Running Macros

You may configure SaveToDB to launch Excel macros. This way gives additional benefits:

  • All possible user actions are located in two menus: the Excel context menu and the SaveToDB Actions menu.
  • Macros are linked to specific queries and are available immediately after connecting data.
  • Macros can have input parameters from different value sources as described above.

Macros can be located in active workbooks or in separate macro workbooks that can be updated independently.

Specify macros in the HANDLER_CODE field in the following format:

<Excel macro> [<Parameter1>[, <Parameter2>[, ...]]

Parameters can use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

For example:

SayHello "World"
SayHello {Name=World}
Sheet1.SayHello {FirstName}, {LastName}
MacroWorkbook.xlsm!SayHello {FirstName}, {LastName}

Note that a macro sheet name is an internal Excel name, not a user visible name.
You may check macro names using the Excel dialog box for launching macros (Alt-F8).

Important! SaveToDB does not check macros. Run macros from trusted sources only!

Running Windows Shell and CMD Commands

SaveToDB allows loading and refreshing data from text files in the following formats: XML, JSON, HTML, and CSV.

You may use Windows Shell and CMD commands for loading, preparing, or checking such files.

Specify commands in the HANDLER_CODE field in the following format:

<Command> [<Parameter1>[ <Parameter2>[ ...]]

Parameters can use context values in the following format: {ParameterName=DefaultValue} or simply {ParameterName}

Command examples:

{FileName}
notepad.exe {FileName}
dir *.*
cmd /c dir {Mask=*.*}
cmd /k dir {Mask=*.*}
sayhello.cmd {FirstName}
mailto:{Email}&subject=Thanks for the connection&body=Hello {FirstName},%0A

The latest example shows how to launch a new email using the mailto: command.
The mailto: handlers are added to menus for non-empty recipients only.

The cmd /c mode is used to execute a command and to close the window after execution, and the cmd /k mode is used to leave the window opened.

The working directory is the active workbook directory if the workbook has been already saved.

Important! SaveToDB does not check commands. Run commands from trusted sources only!

Refreshing Queries using REFRESH Handlers

Microsoft Excel allows validating user input. See Data, Data Tools, Data Validation.

Values from database queries can be used as validation lists.

For example, a contact category list can be used for validation of category column values in a contact list.

In addition, such validation lists should be updated regularly and consistently.

You may use the REFRESH handler added to the Actions menu. Specify desired sheets with the validation lists in the TARGET_WORKSHEET field.

It is a good practice to place the validation lists on one or two hidden sheets.

Checking and Debugging Configuration

Use the Workbook Information dialog box to check the loaded configurations.

Creating Database Configuration Objects

SaveToDB Framework allows developers to add ready configuration objects quickly to any database on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.