Can you connect to sql server from Excel?

Question

Hi,

I need to create some reports from a sql server database for end users to view.

Is it possible to hook into sql server from excel?

Update The end user will only click on the file to view the reports, asking them to do more is too much to ask!

Answer 1

Simplest and oldest way is to use ODBC, but with VBScript, anything is possible.

Answer 2

You are probably better off creating a view (or just a query) that presents data the way you want it then using DTS (SQL 2000) or SSIS (SQL 2005) to export the information using the Microsoft Excel ODBC driver

Answer 3

Sure -- in Excel 2007 click the "Data" tab, then "Connections", then click "Browse for more" and select "+NewSqlServerConnection.odc"

Answer 4

Yes, it absolutely is, depends on what version of excel you have. In 2007 if you go under the Data tab and then "Get External Data" you will see many options to connect to various data sources including SQL

Answer 5

If you want to ensure that you have NO technical requirements of your end users, an export process is a much better approach rather than linking directly to the server from the Excel file.

You can save the connection information, but there are ways they can mess it up, and if they can't be trusted to configure it, it would most likely be the best bet to extract the data and give a static copy.

Answer 6

You can use VBA to connect to a database and import the data. The user will only have to open the file. Your VBA code will do the retrieval and formatting of the data.

Answer 7

In 2007 you can indeed go under the Data tab and then "Get External Data". You can gather data from a lot of sources, including SQL Server, a webpage and Access.

After connecting there's an option to renew the data:

• every x minutes
• when opening the Excel sheet

You can even choose to remove the data when closing the Excel sheet.

Answer 8

OLAP CUBES! so good... create a cube and access it through the data tab in the ribbon like everyone else said