Using SQL in Excel

SQL in Excel Connection Setup

These steps will setup a SQL connection in Excel that can be used with multiple workbooks. The steps will also cover using this SQL connection to populate a worksheet.

  1. Open your new or existing Excel workbook
  2. Click the Data tab of the Excel ribbon
  3. Click Get External Data, select From Other Sources, and then select From SQL Server
    • This will open the Data Connection Wizard
  4. Enter the Server name
    • The server name can be found using SQL Server Management Studio
      • Open the desired connection in the Object Explorer
      • Right-click on the server and select ‘Properties’ to see the server name
  5. Enter the Log on credentials and click Next
  6. Select the Database
  7. (Option) Connect to a specific table
    • When selected, you can pick one table from the window
  8. (Option) Enable selection of multiple tables
    • When selected, check boxes will appear next to each table in the window
    • (Option) Import relationships between selected tables
      • This option allows you to use existing table relationships as opposed to building them manually
  9. Click Next
  10. Change the File Name path by clicking the Browse button
    • If your Excel file is hosted on a network share, then save the connection file to the same folder
  11. Click Finish
    • This will open the Import Data menu
  12. Select how you want to view the imported data in your workbook
  13. Select a worksheet you want to hold the data
  14. Click OK
    • The table(s) will be queried and the data populated into Excel
  15. You can update the table by clicking Refresh All on the Data tab

Modify the SQL Connection to Use a SQL Query

Using the the steps above nets you a table will all rows of data. These next steps will change the SQL query generated to one that you write.

  1. Open the Excel file
  2. Click the Data tab
  3. Click Connections
    • This will open the Workbook Connections window
  4. Select the desired connection and click the Properties button
    • If your connection does not show up, then click the Add button
    • Select the connection from the list
      • If the connection cannot be found using any of the drop-down menu options, then click the Browse button and locate the file manually
  5. The Usage tab has a couple of options regarding Refresh control
    • You can refresh the data every X minutes, when opening the file, and/or clicking Refresh All
  6. Change the selected Command Type from Table to SQL
  7. Enter the query in Command text
    • EXAMPLE: SELECT * FROM [AdventureWorks2012].[HumanResources].[Employee] (NOLOCK)
  8. Click OK
    • Saving the changes will save the connection settings to the Excel file and does not update the SQL connection file

Update Queries via Macro

After an Excel file is setup to run a query, that query can be modified via Macros. See my guide to Macros.

' Declarations
	Dim Workbook As String 'Stores the name of the workbook
	Dim connection1 As String 'Stores the name of the connection
' Initialize values
	Workbook = "SQL_Example.xlsm"
	connection1 = "ActiveEmployees"
' Update SQL Query
	With ActiveWorkbook.Connections(connection1). OLEDBConnection
	.CommandText = "SELECT * FROM [AdventureWorks2012].[HumanResources].[Employee] (NOLOCK)" & _
	“WHERE [CurrentFlag] = 1 AND [JobTitle] <> ‘Chief Executive Officer’”
	End With
	ActiveWorkbook.Connections(connection1).Refresh

Further Reading

Connect a SQL Server database to your workbook

  • Guide from Microsoft