Create Backwards Compatible SQL Server data connection in Excel 2016

1
votes

I've recently been upgraded to Excel 2016.

However I'm now finding that data connections I create to our SQL Server database cannot be refreshed by users with Excel 2013.

Is this a known issue? Is it something I can do anything about?

I am now creating the connection as follows:

  1. Data / Get Data / From Database / From SQL Server Database
  2. Entering server and database details
  3. Clicking Advanced then entering SQL code into the SQL statement box
  4. Clicking OK

Is there another way which will enable those XL2013 users to refresh the query, perhaps?

Thanks in advance. C

1
sql-serverexcelcompatibilityexcel-2016data-connections
Do they receive an error when refreshing, or just nothing at all? Also, this question may get a bit more traction on superuser.com since it's not really programming related (I see it's been out here for a few months which is like eternity in SO time). – JNevill
It's an error message but I will need to check to remind myself what it says. I wasn't aware of superuser.com so I'll take a look here too - thank you! – ChrisD
Re the error message... "Initialisation of the Data Source failed. Check the database server or contact your database administrator..." etc. Followed by "The following data range failed to refresh:...". If the user has Excel 2016 then these error messages do not happen. Similarly, if I create the connection on an older version of Excel then all is fine. – ChrisD

1 Answers

1
votes

I found an answer, in case anyone's interested. There is an option in Excel 2016 (Office 365 subscribers only - see note below) to show the legacy import wizards.

So, if you know your users have older versions of Excel you can enable legacy wizards (see below) and set up data connections that way.

enter image description here