Our Blog

Microsoft Excel 2010 Guide - Getting External Data

This quick guide will demonstrate how to pull in data from a Microsoft SQL Server Table or View into a Microsoft Excel 2010 worksheet.

The guide will be using a Sage ERP Accpac sample company database called "SAMINC" - however these steps may be applied to any database.

Step 1: Selecting an External Data Source

  • Select the "Data" tab in the Excel ribbon toolbar
  • Select the "Get External Data" toolbar button -> "From Other Sources" -> "From SQL Server"
[img-center]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/01-get-external-data.png[/img-center]

Step 2: Data Connection Wizard

Step 1 above will display the Data Connection Wizard dialog window:

  • "Server name:" - Can by a friendly name or an IP address to the SQL server.
  • "Log on credentials:" - Your database administrator will need to provide you with an user name and password for authentication. For security reasons, it is not recommended that you use the system administrator account "sa" - but rather a user account with limited "SELECT" privileges to specific database tables.
[img-center]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/02-data-connection-wizard.png[/img-center]

Step 3: Data Connection Wizard - Selecting the Table or View

Clicking the "Next" button in step 2 above will display the "Select Database and Table" dialog window. If you cannot see your database or a particular table or view in this screen - notify your database administrator to ensure the user credentials entered in step 2 above have appropriate permissions to access these database objects.

The drop-down list will display a list of all databases available to the authenticated user. Selecting a database here will refresh the list below the drop-down list; displaying all available tables and views.

In this case I have selected the database called "SAMINC" and a custom SQL view that I had created called "vCustomers". [img-center]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/03-data-connection-wizard.png[/img-center]

Step 4: Data Connection Wizard - Save Data Connection File and Finish

Clicking the "Next" button in step 3 above will display the "Save Data Connection File and Finish" dialog window.

The main thing to consider here is the first field: "File Name:". By default, Excel will save the connection information into your local profile account - which in most cases would be somewhere like "C:\Users\{your profile name}\Documents\My Data Sources\{yourconnection}.odc". If this Excel file is to be shared among other members of your team - consider saving the file to a network share that is accessible to the other team members.

In all matters of data-related security, I always prefer to not save password credentials when asked the question. Excel does not encrypt the password if you choose to have Excel save your password in a file. Choosing not to save the password simply means that you will be required to enter your SQL user account password each time you open your Excel file and refresh its data source. [img-center]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/04-data-connection-wizard.png[/img-center]

Click "Finish" and you will be brought back to the Excel worksheet with your data. [img-center]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/05-excel-data.png[/img-center]

Step 5: Refreshing the Data

You can control when Excel should refresh the data from its source to be a manual process where the user needs to click on a toolbar button to refresh or you can configure it to refresh on document open or refresh every "n" minutes.

Go back into the "Data" tab in the office ribbon and click on the "Refresh All" button. "Refresh" or "Refresh All" will force a refresh of the data. To setup an automated refresh, click "Connection Properties" (as shown in the image below).

[img-left]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/06-refresh-data.png[/img-left]

As you can see, the connection properties allows you specify various refresh options.

[img-left]http://www.ebsol.com.au/blog-resources/microsoft-excel-2010-guide-getting-external-data/07-connection-properties.png[/img-left]
Tags: , , , , ,

This is a unique website which will require a more modern browser to work! Please upgrade today!