SaveToDB User Interface

SaveToDB User Interface

User Interface

Example of the Actions menu

The main SaveToDB interface elements are located on the SaveToDB ribbon tab.

The interface elements are being adapted to the active data table having a database, web or text file query.

You can change query parameters, query objects, table views, and other database related features without any coding.

However, the real power of the add-in is uncovered when the user interface is customized by database developers.

The action menu, context menu, query list, parameters and initial table views are fully configurable on the server-side.

For example, the screenshot above shows the ribbon elements and the formatted table that can be loaded from a database in an empty workbook!

Using Microsoft Excel with the SaveToDB add-in, database developers can quickly implement database applications that deliver a feature-rich desktop experience to users, with users' full freedom of the Microsoft Excel use.

Save Menu

SaveToDB Save menu
Save
The button saves table changes back to a database and reloads the data.
The button is enabled if the changes can be saved.
Check SQL
The button shows the SQL code that is used to save changes.
This code can be used to save the changes or troubleshoot issues using tools like SQL Server Management Studio.
The button is enabled if the changes can be saved.
Filter Changed Rows
The menu allows filtering new and changed rows.
This feature works for the tables that support saving changes (only for those tables the add-in tracks changes).
For tables saved by merge only filtering new rows is available.
Merge
The button merges table data back to a database (inserts new rows and updates existing ones) and reloads the data.
The button is enabled if the data can be merged.
Check Merge SQL
The button shows the SQL code that is used to merge data.
The button is enabled if the data can be merged.
Save Worksheet Tables
The button saves changes of all worksheet tables back to a database and reloads the data.
The button is enabled if the changes can be saved.
Save Workbook Tables...
The button displays the Save Data Changes dialog box.
See below.
Save Table Data As...
The button allows saving Excel table data to a CSV file.
Only filtered row and visible column data are saved.
Save Query Table As...
The button allows saving query data to a CSV file.
The data are loaded from a data source, not from an Excel table.

Required Conditions for Saving Changes

Changes of database tables can be saved to a database without any coding.

But,

  • A user must have INSERT, UPDATE and DELETE permissions on the table.
  • Excel table row numbers must be turned on.

To turn on row numbers, run the Excel Data, Connections, Properties dialog box and check Include row numbers.

Alternatively, run the SaveToDB, Reload, Configure Query dialog box.

Changes of Excel table data loaded from views, stored procedures, SQL codes, HTTP queries and text files can be saved to a database if

  • The feature is configured in a database.
  • A user has permissions to INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE objects.
  • Excel table row numbers are turned on.

The changes can be saved into a base database table without coding or into multiple tables using custom stored procedures or SQL codes.

See Configuring saving changes.

Save Data Changes Dialog Box

The Save Workbook Tables button displays the Save Data Changes dialog box.

Example of Save Workbook Tables form
Save
The button saves changes and reloads data of the selected tables.
Cancel
The button stops saving and closes the dialog box when the saving is not running.

Reload Menu

SaveToDB Reload menu
Reload
The button reloads data and minimum server configuration of the active table like the Microsoft Excel Refresh action.
Using this button is the fastest way to reload data.
If the table data changes can be saved, then the save query dialog box is displayed.
Reload Data and Configuration
The button reloads data and full server configuration of the active table.
If the table data changes can be saved, then the save query dialog box is displayed.
Reload Worksheet Tables
The button reloads data and full server configuration of all worksheet tables.
If the table data changes can be saved, then the save query dialog box is displayed.
Reload Workbook Tables...
The button displays the Reload Table Data dialog box.
See below.
Configure Connection...
The button displays the Connection Wizard to configure active table properties.
Configure Parameters...
The button displays the Query Parameters dialog box.
The Query Parameters group dialog box launcher can also be used if the group is visible.

Reload Table Data Dialog Box

The Reload Workbook Tables button displays the Reload Table Data dialog box.

Example of Reload Workbook Tables form
Load
The button starts loading data of the selected tables.
If a table has unsaved changes, then the save query dialog box is displayed before loading.
Cancel
The button stops loading and closes the dialog box when the loading is not running.

Actions Menu

Example of the Actions menu

The Actions menu contains the items that are configured as event handlers with the Actions event type.

The menu can be used to execute different tasks. For example:

  • Executing database procedures to update data in a batch mode;
  • Executing macros;
  • Executing Windows Shell or CMD commands to load, update, or verify text data files;
  • Opening related websites and online documentation;
  • Adding, changing, or deleting items of database directories;
  • Executing drill-down queries;
  • Printing documents in HTML formats;
  • Generating document numbers;
  • Resetting query parameters and clearing form data.

Technically, the menu allows:

  • Executing stored procedures and SQL codes;
  • Executing macros, and Windows Shell and CMD commands;
  • Executing HTTP and text file queries;
  • Selecting data from database tables and views.

The menu adapts to the active table query.
If there is one table on a worksheet, the table is used by default. This behavior differs from the Context menu behavior.

If a menu item query has parameters, the Query Parameters dialog box is shown, by default, to allow users to change parameters and to confirm the action.

See Configuring event handlers.

Queries

Example of a query list

The query list allows changing underlying database objects of Excel data tables.

This is the simplest way to connect to a new database object.

This feature allows having fewer worksheets in a workbook.

Also, new database objects come available in Excel immediately after reloading the query list without any additional work.

The SaveToDB add-in contains the default query list that shows all database objects available to connect.

Also, you may use database schemas as query lists.

Database developers may configure query list to show objects by business areas.

You link Excel tables and query lists using Connection Wizard.

You may quickly change it using the query list group dialog box launcher.

Query List Translation

By default, the query list displays native database object names as shown above.

Database developers can configure data translation.

In this case, the query list displays translated object names:

Example of a translated query list

Just can select Data Language in the Options dialog box.

Query Group Elements

Example of the SaveToDB ribbon
Query
The list displays the database object of the active table and allows changing it.
The field is disabled if the Enable the ribbon query list option in the Connection Wizard is turned off.
Reload Query List
The button reloads query list objects, configuration, and data translation.
The field is disabled if the Enable the ribbon query list option in the Connection Wizard is turned off.
Dialog Box Launcher
The query group dialog box launcher displays the Connection Wizard that allows users to change the query list object, to select a new database object, and to configure the Enable the ribbon query list option.
Example of the translated SaveToDB Configure Query form

Parameters

Example of ribbon parameters

The Parameters group displays:

  • Selected fields of tables and views.
  • Stored procedure parameters.
  • Web query parameters.
  • Text query parameters.

When the query parameter is changed, the SaveToDB add-in changes the active query with new values and reloads the data.

Only the six selected parameters are displayed on the ribbon.

You may change other parameters using the Query Parameters dialog box that is displayed by the group dialog box launcher.

SaveToDB stores user's values of parameters and shows them in the parameter value lists.

You may define the stored value limit using the Options dialog box.

By default, SaveToDB displays native database names of the fields and parameters.

The parameter names can be translated into a business language. See Configuring data translation.

Fields of Tables and Views

Example of applied WHERE ribbon parameters

For database tables and views, the ribbon displays fields that are selected by a user or predefined by an application developer.

Use the Data, Reload, Configure Parameters dialog box for the first time.

When the Parameters group is displayed, you may use the group dialog box launcher to configure parameters.

Example of the query parameters form for views

Check the fields that should be selected in the S (Select) field.

Check the fields that should be used in the WHERE clause in the W (Where) field.

Database developers can configure what fields should be displayed. See Configuring parameter values.

By default, the parameter value lists display unique column values.

Parameters of Stored Procedures and SQL Codes

Example of ribbon parameters

Stored procedure and SQL code parameters are detected automatically, and the first six parameters are placed on the ribbon.

The Query Parameters dialog box can be used to change any number of stored procedure parameters.

Example of the query parameters form for stored procedures

By default, a user can specify any parameter value of a proper data type.

Database developers can specify queries that are used for populating parameter values. See Configuring parameter values.

In such case, the parameter values are predefined, and users can select values from the lists only.

Otherwise, users can edit parameter values themselves using the '...' button shown above.

The button shows dialog boxes like the following:

Example of Parameter Value Editor form

Web Query Parameters

Example of HTTP query ribbon parameters

The add-in detects URL parameters automatically.

You may configure parameters using the Connection Wizard.

The real URL of the example above is

http://finance.yahoo.com/q/hp?s=GOOG+Historical+Prices

It has the "s" parameter with the "+Historical+Prices" suffix to the stock symbol.

During the connection, the URL was configured as

http://finance.yahoo.com/q/hp?s={Symbol=GOOG}+Historical+Prices

Symbol is a new parameter with the default value equal GOOG.

As a result, a user can change the query parameter using stock symbols only.

If the query has many parameters, you may use the Query Parameters dialog box.

Example of the query parameters form for web queries

The SaveToDB add-in reloads web data and preserves table formatting and structure.

So, you may insert your formula columns, apply formatting, filter values and so on.

Text Query Parameters

You may use ribbon parameters to change file names and code pages of text file queries.

See Configuring parameter values.

Sorting Rules

The parameter values populated by stored procedures or SQL codes are displayed in the order defined by the procedures or SQL codes.

Other parameter values, manual and populated by views, are being sorted automatically with the following rules:

  • Text values are being sorted from A to Z.
  • Date and time values are being sorted from newest to oldest.
  • Other values are being sorted from smallest to largest.

Views

Example of HTTP query connected via SaveToDB
View List
The list shows the active table view and allows applying a new view from stored views of the active Excel table.
The field is disabled if there is no any active Excel table.
Copy and Insert Selected Rows
The button copies and inserts the selected table rows.
This is a unique feature as this action cannot be done using the native Excel if the table has filtered rows.
The action can be undone (Ctrl-Z).
Show All Rows and Columns
The button shows all manually hidden columns and rows hidden by auto-filters.
Instead of the use of this button, you may create a table view to show required columns and rows, for example, named as "All".
Save Table View

The button shows a dialog box to save the current Excel table view.

SaveToDB Save Table View form
Manage Table Views
The button launches the Table Views dialog box used to edit table views of the active Excel table.
Views Group Launcher Button
The button launches the Table Views dialog box used to edit table views of the active Excel table.

Table Views Dialog Box

The Table Views dialog box allows performing different actions with saved Excel table views in the Table View list and the Excel context menu:

  • Changing the order of views in menus;
  • Renaming a view;
  • Deleting a view;
  • Deleting all views.
SaveToDB Table Views form

You may change the customized Excel table using the Customized Object list.

Important! The actions cannot be undone.

If you delete an important view, you can recreate it or reopen the entire workbook.

Working with Table Views

Use the Save Table View button to save named Excel table views, and then use the view list to apply the views.

For example, the following table has several previously saved views:

Example of the table view drop-down list

Just use one click to apply the saved view:

Example of using table views - applied Up in Volume filter

Saved Elements

Excel Table views include the following elements:

  • A set of visible columns;
  • A set of applied auto-filters;
  • Sort order;
  • Table totals.

These elements are saved for each named Excel table view and applied back to a table.

Active Excel Table

An Excel table is a specially marked region of cells on a sheet, early named as "Lists".

All database queries are inserted as Excel tables. Use the Insert, Table Excel command to create tables from regular Excel cells.

The add-in works with Excel tables only, not with regular cells formatted as a table.

A sheet can contain many Excel tables. SaveToDB adapts to the Excel table that contains the active cell.

If a sheet contains a single Excel table only, this table is active by default.

Wizards Menu

SaveToDB Wizards
Connection Wizard
The Connection Wizard allows connecting to database objects (table, views, and stored procedures), to web data sources (web pages and web services including OData), or to text files.
After the connection, the query list can be used to change the active database or OData object. So, one sheet can be used to work with multiple databases or OData objects.
Pivot Table Connection Wizard
The Pivot Table Connection Wizard allows creating pivot tables connected to database objects (table, views, and stored procedures), to web data sources (web pages and web services including OData), or to text files.
The connection process is the same as for Connection Wizard.
If an underlying database object allows saving changes, you may insert a connected Excel table and insert a pivot table based on the connected table.
In this case, you may use pivot table as an editor for the underlying table.
See details in Working with Pivot Tables.
Publish Wizard
The Publish Wizard allows creating a database table, exporting the Excel table data, and inserting a new Excel table connected to the created database table. After publishing data, the regular functionality reload-edit-save can be used.
Other users can connect to the published table too. This is the fastest way to get the real multi-user work with data in Microsoft Excel.
After the publishing, the Table Format Wizard can be used to save table formats and formulas in a database to use by other users.
The Publish Wizard works with Excel tables early named "Lists". To create an Excel table, select a region and click the Insert, Table menu item.
Data Merge Wizard
The Data Merge Wizard allows merging data loaded from any data source into a database table.
New rows are inserted, and existing rows are updated during the merging. Only common Excel and database table columns are used.
Excel formula columns can be used to calculate data to save to a database.
Change Connection Wizard
The Change Connection Wizard allows changing connection data (server, database, login, and password) for a group of tables.
Application Installer
The Application Installer allows developers to install, check, or uninstall SaveToDB Framework on all supported platforms.
SaveToDB Framework allows customizing SaveToDB features on the server-side.
Database administrator permission is required to perform wizard operations.
Application Workbooks
The Application Workbooks wizard allows developers to generate a workbook used to customize SaveToDB features on the server-side.
The generated workbook contains SaveToDB Framework configuration tables. Just edit and save the configuration data.
In facts, Excel application customization can be made in Microsoft Excel. It is useful as the results can be viewed in Excel immediately.
Table Format Wizard
The Table Format Wizard allows saving, loading, and clearing Excel table formats of database objects in a database.
When the format is saved in a database, other users can load the same format including regular formats, conditional formatting, formulas, etc.
Window Wizard
The Window Wizard allows adding and arranging related windows.
You may easily implement master-detail interfaces in Excel 2007 and Excel 2010.
However, this feature is not applicable for Excel 2013 and Excel 2016.
See Working with Windows.
Form Wizard
The Form Wizard allows creating cursors and form fields.
You may easily create edit forms and master-detail interfaces.
Pivot Wizard
The Pivot Wizard contains useful commands for working with pivot tables.

See complete wizard descriptions in the Articles chapter:

Options Menu

SaveToDB Options menu
Options
The button displays the Options dialog box described below.
Excel Options
The button displays the Excel Options tab of the Options dialog box.
Hide Advanced Menu Items
The button turns on or off displaying advanced menu items.
Show Task Panes
The button displays task panes used to show images.
Show Cell Editor Task Pane
The button displays the task pane used to edit multiline cell values.
Show List Editor Task Pane
The button displays the tasks pane used to select cell values from validation lists in an easy way.

Options Dialog Box

Common Options

SaveToDB Options form - Common options
Interface language
The field allows changing the SaveToDB interface language.
The field contains only available languages.
You may add an interface language yourself. The language file contains about 600 lines to translate.
If you would like to translate the add-in, please contact us.
Default data language
The field allows changing the default language of the database data.
This feature is used to show business names in different languages instead of native database object and column names.
This feature requires configuring data translation in a database.
This workbook data language
The field allows changing the active workbook language of the database data.
This setting is stored in a workbook instead of Windows Registry.
Use this option to prepare the workbook before distribution.
Connection timeout
The field is used to change the server connection timeout.
You may increase this timeout for busy servers and slow lines, and decrease it to get connection errors faster.
Command timeout
The field is used to change the timeout of command execution.
You may increase this timeout if a large amount of database data is updated.
Generate single-line SQL statements for Microsoft SQL Server
Microsoft SQL Server supports inserting and updating data from subqueries.
The SaveToDB add-in may generate SQL codes using this feature. The code is more efficient, but it is not user-friendly.
You may check this checkbox to generate the user-friendly code.

Excel Options

SaveToDB Options form - Excel options
Show the Open URL menu at the context menu
The field controls showing the Open URL menu in the Excel context menu.
Show the Table Views at the context menu
The field controls showing of the Table Views menu in the Excel context menu.
Auto-activate table related windows
The field controls the mode the auto-activating related windows.
This feature allows activating related windows when you select another sheet in the main Excel window and hiding related windows of the previously active sheet.
This feature is also used to implement master-detail interfaces in the SaveToDB add-in.
See Working with windows.
Auto-arrange workbook windows
The field controls the window auto-arrange mode.
You may resize or move Excel windows, and the SaveToDB add-in arranges them.
See Working with windows.
Hide workbook windows in the Windows Taskbar
The field controls auto-hiding of child windows in the Windows Taskbar.
This feature can be useful when workbooks contain many windows.
Launch the calendar on double-click
The field controls launching the calendar control on the double-click.
Use the Calendar to input dates into Excel cells.
See Working with the calendar.
Change auto-filters using the row over tables
The field controls the use of rows over the tables to change table auto-filters.
This feature is very useful if you frequently change table auto-filters.
See Working with auto-filters.
Open the last workbook at startup
The field controls auto-opening of the last opened workbook at the Excel startup.
This feature is very useful for developers.
You may press Shift to disable opening a workbook during startup.

Ribbon Options

SaveToDB Options form - Ribbon options
Hide Advanced Menu Items
The field controls showing advanced menu items.
Show the Information group at the ribbon
Uncheck this field to hide the Information group on the ribbon to free the ribbon place.
Limit of parameter values
The field defines the limit of stored parameter values.
The maximum value is 1000.
See Parameters.

Developer Options

SaveToDB Options form - Developer options
Add SaveToDB Data Sheets
The button adds hidden sheets that are used to store SaveToDB add-in data.
These sheets are required to support a complete set of SaveToDB features.
The sheets can be added through the Connection Wizard also.
Remove SaveToDB Data Sheets
The button deletes SaveToDB data sheets in the active workbook.
Use this button if you want to remove the SaveToDB add-in from a computer or to remove the SaveToDB data from a workbook.
This button does not delete data sheets with table views as the companion TableViews add-in can be installed separately.
Use the Table Views dialog box to remove these data sheets.
Show SaveToDB Data Sheets
The button shows the hidden SaveToDB data sheets of the active workbook.
You can unhide the sheets manually using the Visible sheet property in the Visual Basic Editor (Alt-F11).
Hide SaveToDB Data Sheets
The button hides the SaveToDB data sheets of the active workbook.
Clean SaveToDB Data Sheets
The button deletes SaveToDB configuration data except for the core information about database objects.
Use this option to clean the workbook before distribution.
Reload workbook tables and query lists after the cleaning.
Open Installation Folder
The button opens the SaveToDB installation folder.
The Languages subfolder contains interface translation files.

Help Menu

SaveToDB Help menu
Help
The button displays the local help.
Online Help
The button opens the online help.
You may leave your comment or ask a question on the help pages.
Technical Support
The button opens a support page on the SaveToDB website.
SaveToDB Website
The button opens the home page on the SaveToDB website.
Workbook Information
The button displays the Workbook Information dialog box described below.
Register Product
The button opens the Register Product wizard.
The button is disabled if the SaveToDB Enterprise edition is already registered.
Purchase SaveToDB
The button opens a web page where you may purchase the SaveToDB Personal or Enterprise edition.
Check Updates
The button opens the web page to check and download product updates.
About SaveToDB
The button displays the About SaveToDB dialog box.

Workbook Information

The Workbook Information dialog box shows different information about loaded server-side configurations.

Loaded Configurations

SaveToDB Workbook Information - Loaded Configurations

The table shows all loaded database configurations.

Data Tables

SaveToDB Workbook Information - Data Tables

The table shows all database connected tables of the active workbook.

Database Objects

SaveToDB Workbook Information - Database Objects

The table shows database object information that is loaded using query list and data translation configuration views.

See Configuring Query Lists and Configuring Data Translation.

Event Handlers

SaveToDB Workbook Information - Event Handlers

The table shows loaded server-side event handlers.

See Configuring event handlers.

Workbook Definition

SaveToDB Workbook Information - Workbook Definition

The table shows the active workbook definition that you may paste into the DEFINITION column of the workbook configuration table to allows users to create workbooks using the Application Workbooks wizard.

Context Menu

Example of the context menu

The SaveToDB Drill-Down group contains the items that are configured as event handlers with the ContextMenu event type.

The menu can be used to execute different tasks. For example:

  • Executing database procedures to update context data in a batch mode;
  • Executing context macros;
  • Opening context related websites and searches;
  • Creating emails;
  • Adding, changing, or deleting items of database directories;
  • Executing drill-down queries.

Technically, the menu allows:

  • Executing stored procedures and SQL codes;
  • Executing macros, and Windows Shell and CMD commands;
  • Executing HTTP and text file queries;
  • Selecting data from database tables and views.

Even if a menu item query has parameters, the Query Parameters dialog box is not shown by default. This behavior can be configured.

See Configuring event handlers.

Table Views Context Menu

Example of the Table Views context menu

The Table Views menu allows changing the active table view.

Use the Save Table View button in the Table Views group to save the Excel table views.