How to Configure Excel Front End to SQL Server

The typical user is comfortable working in Microsoft Excel. Why not provide your users with a tool they already know and add to it a connection into your SQL Server environment. The advantage of this approach is their Excel spreadsheet is always up to date with the current data from the back end database. It’s typical for users to put data into Excel but usually it’s a snapshot of the data at a point in time.

This article will show you how simple it is to configure an Excel spreadsheet with a connection to SQL that you can provide to your users.

In this example, we are going to use the Adventure Works sample database that Microsoft ships with SQL Server 2008.

Difficulty: Average

Time Required: 10 Minutes

Here's How

You will need a few pieces of information to setup the Excel to SQL Server connection.
- SQL Server Name - In our example, the SQL Server is MTP\SQLEXPRESS.
- Database Name - Our example, we are using the AdventureWorks database.
- Table or View - We are going after the view Sales.vIndividualCustomer.
Open Excel and create a new workbook.
Click on the Data tab. Locate the “Get External Data” option and click on “From Other Sources” and choose “From SQL Server”. This opens the “Data Connection Wizard”.
Fill in the Server Name. In this example, the server name is “MTP\SQLEXPRESS”. Set the Login Credentials to “Use Windows Authentication”. The other option would be used if your database administrator provided a username and password for your user. Click Next. This brings up the “Data Connection Wizard”.

Select the database (“AdventureWorks” in our example) from the “Select the database that contains the data you want” drop down box. Make sure “Connect to a specific table” is checked. Locate the view (“Sales.vIndividualCustomer” in our example) from the list and select it. Click Finish which brings up the Import Data dialog box.

Check the Table checkbox and choose where you want to put the data (existing worksheet or new worksheet). Click OK which creates an Excel list and imports the entire table into your spreadsheet.
Save your spreadsheet and send to the user. The nice thing about this technique is that your user has access to current data whenever they need it. While the data is saved in the spreadsheet, there is a connection to the SQL Database. Anytime you want to refresh the spreadsheet, right click somewhere in the table and click on “Table” and then “Refresh”. That’s it.

Tips

It's really important that you make sure the user is properly setup in SQL Server. This is the thing that causes issues in most cases using this technique.
Check the number of records that are in the table or view that you are connecting to. If the table has a million records, you might want to filter this down. The last thing you want to do is hang the SQL Server.
On the Connection Properties dialog box, there is an option called "Refresh data when opening the file". Consider checking this option. When this option is checked, the user will always have a fresh set of data when opening the Excel spreadsheet.
Consider using Pivot Tables to summerize the data.