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.
How To Update SQL Tables from Excel
Navigate to the SQL Spreads tab within Excel and enter Design mode. On the right, a list of databases will be visible. Choose the specific database you are working with and select an SQL table to update directly from Excel. Utilize the Columns tab to refine and customize the presentation of your table within Excel.
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.
1. 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.
2. 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.
3. 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.
Frequently asked questions about SQL tables in Excel
How to refresh data from SQL in Excel?
Navigate to Data > Queries & Connections > Connections tab. Right-click on a query from the list, and then choose Properties. In the Connection Properties dialog box, go to the Usage tab. Under Refresh control, check the box that says "Refresh data when opening the file."
How to update a query table in Excel?
Choose Data > Connections & Properties > Queries tab. Right-click on the query, and then click Edit. Once you've made the necessary changes in the Power Query Editor, go to File > Close & Load to save your modifications.
