16  SQL in Excel: Setup

16.1 Running SQL in Microsoft Excel

You can run SQL queries in the Microsoft Excel environment using multiple options. However, all connection-based approaches include several steps and require some understanding of database connection engines.

We will use the macro-based approach mentioned in the first section. Note that running SQL queries in Excel is not very convenient for complex queries.

Note that Excel requires SQL query table names to include a $ sign and be enclosed in [].

Here is an example: SELECT * FROM [tblStudent$]

16.1.1 Use Excel VBA Macro

  • Download the zipped Excel file containing the VBA macro.

  • Note, the file uses 64-bit driver (OLEDB). The macro will not work in 32-bit machine (it will require appropriate modifications).

  • Note: You must enable the macro by clicking the Enable Content button before using the workbook.

  • Include your data in each sheet and name those sheets (example: tblStudent)

  • Instructions are in sheet Options

Enable Content

  • Option 1:

    • Step 1; Click the ‘Run SQL’ command button.
    • Step 2: Enter the SQL statement. Example: SELECT * FROM [tblStudent$]
    • Step 3: Results will be always displayed in “Option1_Output” sheet at A1. It will clear previous results.
    • Copy and paste the previous query results if you wish to check them later.
    • Note: Check that all cells are blank in the range where outputs will be displayed. It may otherwise show #SPILL! error.

Step 1: Run SQL command button

Step 2: Enter the SQL statement

Step 3: Check the results in the Option1_Output worksheet

  • Option 2:

    • Step 1: Select any cell
    • Step 2: Enter the function fnSQL with the SQL statement argument. Example: =fnSQL("SELECT * FROM [tblStudent$]")
    • Step 3: Press ENTER and results will be always displayed on the same cell.

Option 2

Steps: Select a cell and enter fnSQL with the SQL statement

16.1.2 Connect Excel Files

Connect existing Excel files

  • Create manually all connection and run from database properties.

  • Data > Existing Connections > Connection in this workbook or Connection files on the computer

  • Once the data is connected, an Open Database Connectivity (ODBC) connection is created

  • Once the connection is set, go to the properties option in the connected table to run the SQL query.

  • Data > Existing Connections > Properties > Definition > Command Type (select SQL))

16.1.3 Other RDBMS

Connect existing database

  • You can also connect to standard RDBMS.

  • It will set up the ODBC with the database.

  • Once connected, you can send SQL queries to the database using a similar approach as mentioned above.

  • Here is a WikiHow link to set up the connection with MS Access database.