1. Excel 2016 Odbc Driver

Excel 2016 has issues (much more substantial in October 2015, but still present in November 2016). It's really not ready for prime time.

Excel 2011 is a better bet, as things stand. If you're stuck with Excel 2016, update to the latest (3.52.12, as of July 2016), create your DSN (System DSN recommended; User DSN may work for you) in iODBC Administrator.app (found in /Applications/iODBC/), and then use it with Excel.

(, produced by, is the open source ODBC driver manager Apple has bundled into OS X since 10.2, but Apple remains several years behind the current iODBC version.).

Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection. Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone. Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database. Again, all the previous warnings about locking apply.

Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it. There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it.

You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros. Name the Macro, and then click Create. Per our support engineer: We have Various ways to connect to a SQL server: - Using ADODB SQL ODBC provider with DSN - Using ADODB SQL ODBCprovider without DSN - ADO SQL OLEDB provider for authenticated connections In below code example, I've used the OLEDB provider for SQL using a trusted connection. Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone.

Using VBA to Get At SQL Server Data If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database. Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it.

There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it. You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database.

Here's the process to create your own macro to connect to SQL Server programmatically: 1. Click on Tools, then Macro, and then Macros. Name the Macro, and then click Create. A little late to help this situation but for anyone else with a similar problem of having the ODBC Data Sources dialog box open every time they execute code to return query data. I've found this issue is due to the fact that the QueryTable 'Save Password' has not been set to 'True' via code, or manually you can set this in the Connection Properties dialog box. As long as you are supplying the password with code and have the 'Save Password' property set to True (or checked in the dialog box) then the ODBC Data Sources dialog box should only appear the first time you execute the code, and sometimes it doesn't display even on the first time as long as you have the ODBC connection set-up on your machine. '-' Dim qt As QueryTable ' Set up the SQL Statement sqlstring = 'select aufname, aulname from authors' ' Set up the connection string, reference an ODBC connection ' Leave the name and password blank for NT authentication connstring = 'ODBC;DSN=pubs;UID=;PWD=;Database=pubs' ' Now implement the connection, run the query With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range('A1'), Sql:=sqlstring).Refresh End With '-' simple and concise.

Excel 2016 Odbc Driver

Yes there are other ways; but your example was nicely done.