Updating SQL Tables from Excel: A New Approach (Part 1)

In a previous blog article, Ambika presented a neat method to update an SQL table from Excel.  This proved to be useful for many of our customers but could somewhat be painful for those wishing to update many rows. Today, an increasing number of XLCubed customers use Excel as a planning sandbox and often want to writeback a whole set of records back to the source SQL database at once.

In this article we will describe how you can writeback data to SQL and write only the changed values to the source table in one single pass by executing a stored procedure.

Part 1: Preparing the Table

The example table to be updated

We will use a similar example as the one used in the previous article: we want to update the salary and Commission % of Sales Representative.
Below is the query we want to display to the user. Only the last 2 columns “Salary” and “Commission %” should be editable, so any change made in the other columns will simply be ignored.

Defining named ranges

Using name ranges in Excel is a general recommended practice in Excel. Named ranges makes Excel formulas easier and safer and also greatly reduce the hassle of formulae maintenance.
In our example, as the number of rows displayed depends on the choice made in the “Departments” and “Job” slicers, we need a way to make sure we always consider the correct range of cells. The good news is that we won’t have to worry about anything as we will delegate this management to XLCubed: it will update the required ranges of cells as needed depending on the filters applied to the query.

In the table properties window, we click the “Add” button in the “Interaction” section. Then we define the named ranges we need:

This gives us 4 new named ranges that XLCubed will automatically update according to the number of rows in the query.

Clearing edits

We want the user to be able to cancel any edits and revert to the original values.

This is achieved by the “Clear Edits” link in cell $N$4 which uses a standard “XL3Link” formula. It can be easily edited through the right click menu: in this case, the formula writes TRUE to cell $T$5.

=XL3Link(,"Clear Edits",1,XL3Address($T$5),TRUE)

Let’s now unhide the columns next to the grid. We see that $U$5 contains an “XL3RefreshObjects” formula that refers to cell $T$5. As you may have guessed, this means that whenever $T$5 is set to TRUE, the “XL3RefreshObjects” formula will be fired which will then refresh any table or grid in the current sheet, thus reverting all cells of the query to their original value.

Highlighting changed values

Another thing we want is to highlight the cells that changed to make things easier for the user

In order to achieve this, we simply compare the current values displayed in columns N and O to the original ones stored in columns L and M that are hidden. We then use a standard Excel conditional format rule made of a simple Excel formula to highlight the values that are different from the originals.

So now when a user makes an edit, the cell is highlighted:

Our query is ready for the update now. You can read part 2 that explains how only changed rows will be updated in the SQL table.

‍