Introduction

In this article, we will talk about SaveToDB Excel Add-In for Microsoft Excel.

The add-in has powerful import-export features for Microsoft Excel 2007-2016 and Microsoft SQL Server 2000-2014:

  • Importing SQL Server data to Excel using the Data Connection Wizard.
  • Exporting Excel data to SQL Server using the Publish Wizard.
  • Using Excel as a database table editor.
  • Updating SQL Server data via handling Excel events using stored procedures.
  • Updating SQL Server data using custom INSERT, UPDATE and DELETE stored procedures.

The first three features do not require any configuration and are ready to use right after install.

For example, you can connect to a database table, edit the data, and save the changes to the database immediately, without any coding.

For the last two features, you should develop configuration views and stored procedures.

However, only the server-side development is used. No coding in Microsoft Excel is required.

It is important to say that SaveToDB extends the behaviour of a native Excel QueryTable object.
So users can make what they do early and use new features to update database data.

The goal of the SaveToDB Excel Add-In is to empower database developers to deliver feature-rich database applications with Microsoft Excel as a client using only database development skills.

See some screenshots of Microsoft Excel use as a client to SQL Azure database:

SaveToDB example of budget form of SQL Azure application SaveToDB example of stock analysis form of SQL Azure application SaveToDB example of tasks form of SQL Azure application

Table of Contents

SaveToDB Import-Export Wizards

Excel-SQL Server Import-Export Wizards

To top

Importing SQL Server Data to Microsoft Excel

SaveToDB Add-In contains a powerful Data Connection Wizard.

You can easily connect and reconnect to any database object including stored procedures.

The result of the SaveToDB connection wizard is a native Excel ListObject with QueryTable connected to a database table, view, or stored procedure.

Connect to Database Wizard - Connect to database Connect to Database Wizard - Select default Query List and database object

To top

Exporting Excel Data to SQL Server using Publish Wizard

The SaveToDB add-in contains a powerful Publish Wizard.

Using the Wizard you can:

  1. Easily design a new database table based on an Excel data table.
  2. Create a new database table and export the source table data.
  3. Create a new Excel data table connected to the created database table.

Also, the wizard inserts formula columns from the source table and applies the source table formats.

After publishing, you can edit data in Microsoft Excel and save data changes back to a database.

This is the simplest way to implement a multi-user work with data stored in SQL Server or SQL Azure in Excel.

Example of Exporting Excel Data to SQL Server using Publish Wizard

The source Excel data table:

Publish Wizard - Source Table

Step 1. Connect to a server and a database

Publish Wizard - Wizard steps Publish Wizard - Connect to database

Step 2. Design the destination database table
Step 3. Define table's schema and name

Publish Wizard - Design Destination Table Publish Wizard - Define Schema and Name of Destination Table

Step 4. Verify and execute the script
Step 5. Verify the script results

Publish Wizard - Verify and Execute Script Publish Wizard - Verify Script Results

Step 6. Finish the wizard and get a new Excel table connected to the created database table

Publish Wizard - New table

Now you may edit data and save data changes back to a database.

To top

Updating SQL Server Data Using Default Features

If an Excel query table is connected to a database table, you may edit the data and save changes back to a database without any coding. Just click the Save button on the ribbon.

The SaveToDB add-in tracks data changes and generates the required INSERT, UPDATE and DELETE commands to update a database table.

This feature works with database tables and editable views only.

You should implement the SQL code to save changes of data from views or stored procedures.

To top

Updating SQL Server Data Using Stored Procedures

You can configure stored procedures to update a database.

Particular server-side views are used for configuring.

Excel to SQL Server Export Procedures Configuration

In the example view you can see that:

  • xls.uspBudget_insert is used to insert new data from an Excel table connected to the xls.uspBudget_select procedure.
  • xls.uspBudget_update is used to update the changed data.
  • xls.uspBudget_delete is used to delete the deleted data.

SaveToDB automatically determines procedure parameters and calls the procedures for appropriate changes.

You can implement and modify any business logic using stored procedures.

Users should just click the Save button to save changes.

To top

Updating SQL Server Data Using Event Handlers

You can use stored procedures as Excel event handlers.

Below the example of the Role Members table. The table gets data from the dbo05.uspRoleMemberTable stored procedure.

Database Management Example - Use of stored procedures as Excel event handlers

Using a special configuration view, you can assign stored procedures to the Change, Double-Click or Context Menu events of any data table.

Use of stored procedures as Excel event handlers configuration view

You can see that the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable stored procedure is used to handle the Change event of the dbo05.uspRoleMemberTable stored procedure.

The code of the dbo05.uspExcelEvent_Change_dbo05_uspRoleMemberTable procedure:

CREATE PROCEDURE [dbo05].[uspExcelEvent_Change_dbo05_uspRoleMemberTable]
    @User nvarchar(128)
    , @ColumnName nvarchar(128)
    , @CellValue nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON

    IF @CellValue IS NULL
        EXEC sp_droprolemember @rolename =  @ColumnName, @membername = @User
    ELSE
        EXEC sp_addrolemember @rolename =  @ColumnName, @membername = @User

END

The code is very simple. The procedure has parameters:

  1. The value of the User column of the current data row.
  2. The name of the changed cell column.
  3. The value of the modified cell.

SaveToDB automatically determines procedure parameters and calls the procedure with proper values.

You can use in procedures any data from the base data table and the predefined values:

  • @SheetName
  • @DatabaseName
  • @TableName
  • @ColumnName
  • @EventName
  • @CellValue

This is a very powerful feature.
The example implements the Role Members editor using Microsoft Excel with a dozen of SQL lines.

To top

Conclusion

You may easily export Excel data to a new database table using the Publish Wizard of the SaveToDB add-in.

You may easily use Microsoft Excel as a database table editor.

You may easily transform Microsoft Excel to a feature-rich database client using server-side views and stored procedures

To top

Download

SaveToDB Add-In for Microsoft Excel
SaveToDB Add-In for Microsoft Excel (Trial 30 days)
Allows using Microsoft Excel as a database client
Version: 6.10 | 11/02/2016 | 20.9MB | Homepage | Article 1 | Article 2
Download

Related Articles

Use Excel as a DB front-end
with the SaveToDB add-in

Use Excel as a DB front-end with the SaveToDB add-in