External Data Source: insert entire row

Question

I have an Excel Query using a SQL server database as the data source.  I have added 6 extra columns to the right of the returned data set for users to fill in (selecting values from drop-down lists).

The users will have the ability to refresh the data set if required and I have turned on the 'refresh data when opening the file' option.

When the data is refreshed I would like any new rows to be inserted thus moving down the entire row (including the 6 extra columns) so that the data in the 6 extra columns remains aligned with the relevant row.

The "insert entire rows for new data ..." option does not  do what I require, as it leaves the data in the extra columns stationary (in the same row number as it was entered) and does now keep it aligned with its 'source' row.

I need a similar function when rows are deleted in the SQL database, that is, when refreshed the entire row is deleted, including the 6 extra columns to the right of the external data range.

Is there a way for me to do this?

PS. Operating System: Windows Server 2008 & Citrix Published Application.

Answer 1

When you refresh the SQL data, new data will be returned in whatever order is determined by your SQL query. All links to the data you have added in Excel will be lost. Sorry, that's just the way it works.

There are a couple of ways around this.

One simple thing you could try is to include a unique identifier in the SQL data, eg ClientId. Then add a ClientId column to the Excel data, and add the six columns next to that. You would then need a macro to match the SQL ClientId to the Excel ClientId once the SQL data is refreshed.

Or, if you have admin rights on the SQL Server, enable write-back to the server table and include the extra columns there.

---

Ed Ferrero

www.edferrero.com

Answer 2

As long as there is a primary key, I wrote a sub routine that can help:

https://gist.github.com/tstone2077/0bff1ff28a0ffe7c7a07#file-vbaexternaldatasyncrows

It basically copies all the data into a separate sheet, then refreshes the external data, then updates the extra data based on a primary key lookup.

Answer 3

Thanks Ed!  I'm liking your first option - adding an Excel ClientID column.

If it isn't too much trouble, could I please ask for some further help.  While I have dabbled in VB I am not real familiar with it and was wondering if you could please give me an example of the vb macro that will match the SQL ClientID with the Excel CilentID once the data is refreshed.

Once again, many thanks.