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

Excel Macros Primer

About Excel Macros

The macro code language for most Office programs, including Excel, is Visual Basic for Applications (VBA). You may have recorded macros in Excel by stepping through actions that the program saves for you. When you record a macro, Excel records the VBA code describing your actions in a module attached to the workbook.
http://office.microsoft.com/en-us/training/introduction-to-terms-RZ001150634.aspx?section=3

Downloading Excel Macros

Try Office 365 Home
Office 365 gives you the power and flexibility to get things done from virtually anywhere.
http://office.microsoft.com/en-us/try/

Starting Excel Macros

IMPORTANT: This feature isn’t available in Office on a Windows RT PC.
Excel provides several ways to run a macro. A macro is an action or a set of actions that you can use to automate tasks.
http://office.microsoft.com/en-us/excel-help/run-a-macro-HA102748985.aspx

  1. Open Excel
  2. Do one of the following:
    1. Click File, click new, select New Workbook
    2. Click File, click open, select your macro-enabled workbook
  3. Click the View tab
  4. The Macros button should appear on the right side of the Ribbon(top menu bar).

Hello World!

Recording

  1. Click the Macros button on the View tab
  2. Select Record Macro…
  3. Click OK
  4. Type Hello World! in cell A1
  5. Click the Macros button again and select Stop Recording

Editing

  1. Click the Macros button on the View tab
  2. Select View Macros
  3. Select your macro from the list
  4. Click the Edit button

Your code should look something like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Hello World!"

End Sub
"Hello World!"

Playback

  1. Clear the contents of cell A1
  2. Click the Macros button on the View tab
  3. Select View Macros
  4. Select your macro from the list
  5. Click the Run button
"Hello World!"

Variables

You often have to store values when you perform calculations with Visual Basic. For example, you might want to calculate several values, compare them, and perform different operations on them, depending on the result of the comparison. You have to retain the values if you want to compare them.
http://msdn.microsoft.com/en-us/library/cd6hcy37.aspx

Sub Macro1()
 Dim message As String
 message = "Hello World!"
 Range("A1").FormulaR1C1 = message
End Sub
"Hello World!"

Functions

All executable code must be inside a procedure. Each procedure, in turn, is declared within a class, a structure, or a module that is referred to as the containing class, structure, or module.
To return a value to the calling code, use a Function procedure; otherwise, use a Sub procedure.
http://msdn.microsoft.com/en-us/library/sect4ck6.aspx

Sub Macro1()
    ' Call the function
    writeMessage
End Sub

Sub writeMessage()
    Dim message As String
        message = "Hello World!"
    Range("A1").FormulaR1C1 = message
End Sub
"Hello World!"

Passing Variables to Functions

In this example, the function writeMessage will accept a value and declare it as a string.

Sub Macro1()
    writeMessage ("Hello World!")
End Sub

Sub writeMessage(message As String)
    Range("A1").FormulaR1C1 = message
End Sub
"Hello World!"

Error Handling

In Microsoft Excel, you can use the On Error statement in a Microsoft Visual Basic for Applications macro (Sub procedure) to trap errors and direct procedure flow to the error-handling statements in a procedure. By using error handling, you make your macros and your application easier to use by intercepting run-time errors before the user sees them.
http://support.microsoft.com/kb/141571

Sub Macro1()
    ' Run the Error handler "ErrHandler" when an error occurs.
    On Error GoTo Errhandler

    writeMessage ("Hello World!")

    ' Disable the error handler.
      On Error GoTo 0

    ' Exit the macro so that the error handler is not executed.
      Exit Sub

Errhandler:
      ' If an error occurs, display a message and end the macro.
      MsgBox "An error has occurred. The macro will end."

End Sub

Sub writeMessage(message As String)
    Range("A1").FormulaR1C1 = message
End Sub
"Hello World!"

Further Reading

Get in the Loop with Excel Macros

  • Microsoft’s training course about Excel Macros

Excel VBA Tutorial

  • Excel Easy’s VBA (Visual Basic for Applications) guide