Running SQL Queries within Excel Environment

If you want to run SQL queries within Excel environment without connecting to any database, you can do it with creating the ADODB  connection strings within the workbook. Below we will show you different case/example how you can run the sql queries on worksheet, fixed range, name range etc. 

Example 1 - Running SQL query on static data source or on whole worksheet 

Option Explicit 

Sub example1() 

' declare variables 

Dim cn As ADODB.Connection 

Dim rs As ADODB.Recordset 

Dim i As Long 

Dim strfile As String 

Dim fld As ADODB.Field 

Dim strCon, strsql As String 

i = 1 

' we have to create the connection within this workbook 

strfile = ThisWorkbook.FullName 

' establish the connection 

' set the provider Provider=Microsoft.ACE.OLEDB.12.0 for excel 2007 and above 

'Provider=Microsoft.Jet.OLEDB.4.0 for excel 2003 

' data source ' file path 

'Extended Properties Excel 12.0 ' for excel 2007 and above 

'Extended Properties Excel 8.0 ' for excel 2003 

'"IMEX=1;" tells the driver to always read "intermixed" (numbers, 

dates, strings etc) data columns as text 

'HDR = the data is having header row yes or no. 

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strfile & 

" ;Extended Properties=""Excel 12.0;HDR=YES"";" 

Set cn = CreateObject("ADODB.Connection") 

Set rs = CreateObject("ADODB.Recordset") 

' clear output sheet 

Sheets("Output").UsedRange.Clear 

cn.Open strCon 

' remeber by default it takes header names as field names 

' run a simple select query on fixed range 

' [Data$A1:c49] it will act as a table name  should always be written 

as sheetname followed with dollar sign and 

'then the refrence (note refrence should not have any dollar signs) 

strsql = "SELECT * FROM [Data$A1:c49]" ' u can also try  strsql = 

"SELECT * FROM [Data$]" 

rs.Open strsql, cn 

' control check if it does not return any value 

If rs.EOF = True Then 

MsgBox "No Record Found" 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

Exit Sub 

End If 

' paste header names in first row of output sheet 

For Each fld In rs.Fields 

With Sheets("Output").Cells(1, i) 

    .Value = fld.Name 

    .Interior.Color = RGB(31, 73, 125) 

    .Font.Color = vbWhite 

    .Font.Bold = True 

    i = i + 1 

End With 

Next 

' paste the result in output sheet 

Sheets("Output").Range("A2").CopyFromRecordset rs 

' format output sheet 

With Sheets("Output").UsedRange.Borders 

        .LineStyle = xlContinuous 

        .Weight = xlHairline 

        .ColorIndex = 5 

End With 

' close the connection and recordset 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

End Sub 

============================================================= 

Example 2 - Running SQL query on dynamic data source 

Option Explicit 

Sub example2() 

' declare variables 

Dim cn As ADODB.Connection 

Dim rs As ADODB.Recordset 

Dim i As Long 

Dim strfile As String 

Dim fld As ADODB.Field 

Dim strCon, strsql As String 

i = 1 

' we have to create the connection within this workbook 

strfile = ThisWorkbook.FullName 

' establish the connection 

' set the provider Provider=Microsoft.ACE.OLEDB.12.0 for excel 2007 and above 

'Provider=Microsoft.Jet.OLEDB.4.0 for excel 2003 

' data source ' file path 

'Extended Properties Excel 12.0 ' for excel 2007 and above 

'Extended Properties Excel 8.0 ' for excel 2003 

'HDR = the data is having header row yes or no. 

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strfile & 

" ;Extended Properties=""Excel 12.0;HDR=YES"";" 

Set cn = CreateObject("ADODB.Connection") 

Set rs = CreateObject("ADODB.Recordset") 

' clear output sheet 

Sheets("Output").UsedRange.Clear 

cn.Open strCon 

' remeber by default it takes header names as field names 

' run a simple select query on fixed range 

'"IMEX=1;" tells the driver to always read "intermixed" (numbers, 

dates, strings etc) data columns as text 

' [Data$A1:c" & Sheets("Data").Range("a1048576").End(xlUp).Row & "]" 

- it will make source data dynamic 

strsql = "SELECT * FROM [Data$A1:c" & 

Sheets("Data").Range("a1048576").End(xlUp).Row & "]" 

rs.Open strsql, cn 

' control check if it does not return any value 

If rs.EOF = True Then 

MsgBox "No Record Found" 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

Exit Sub 

End If 

' paste header names in first row of output sheet 

For Each fld In rs.Fields 

With Sheets("Output").Cells(1, i) 

    .Value = fld.Name 

    .Interior.Color = RGB(31, 73, 125) 

    .Font.Color = vbWhite 

    .Font.Bold = True 

    i = i + 1 

End With 

Next 

' paste the result in output sheet 

Sheets("Output").Range("A2").CopyFromRecordset rs 

' format output sheet 

With Sheets("Output").UsedRange.Borders 

        .LineStyle = xlContinuous 

        .Weight = xlHairline 

        .ColorIndex = 5 

End With 

' close the connection and recordset 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

End Sub 

============================================================= 

Example 3 - Running SQL query on picking name range as source data 

Option Explicit 

Sub example3() 

' declare variables 

Dim cn As ADODB.Connection 

Dim rs As ADODB.Recordset 

Dim i As Long 

Dim strfile As String 

Dim fld As ADODB.Field 

Dim strCon, strsql As String 

i = 1 

' we have to create the connection within this workbook 

strfile = ThisWorkbook.FullName 

' establish the connection 

' set the provider Provider=Microsoft.ACE.OLEDB.12.0 for excel 2007 and above 

'Provider=Microsoft.Jet.OLEDB.4.0 for excel 2003 

' data source ' file path 

'Extended Properties Excel 12.0 ' for excel 2007 and above 

'Extended Properties Excel 8.0 ' for excel 2003 

'"IMEX=1;" tells the driver to always read "intermixed" (numbers, 

dates, strings etc) data columns as text 

'HDR = the data is having header row yes or no. 

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strfile & 

" ;Extended Properties=""Excel 12.0;HDR=YES"";" 

Set cn = CreateObject("ADODB.Connection") 

Set rs = CreateObject("ADODB.Recordset") 

' clear output sheet 

Sheets("Output").UsedRange.Clear 

cn.Open strCon 

' remeber by default it takes header names as field names 

' run a simple select query on fixed range 

' [Data$A1:c49] it will act as a table name  should always be written 

as sheetname followed with dollar sign and 

'then the refrence (note refrence should not have any dollar signs) 

strsql = "SELECT * FROM [" & Range("source_data").Worksheet.Name & "$" 

& Range("source_data").AddressLocal(0, 0) & "]" 

rs.Open strsql, cn 

' control check if it does not return any value 

If rs.EOF = True Then 

MsgBox "No Record Found" 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

Exit Sub 

End If 

' paste header names in first row of output sheet 

For Each fld In rs.Fields 

With Sheets("Output").Cells(1, i) 

    .Value = fld.Name 

    .Interior.Color = RGB(31, 73, 125) 

    .Font.Color = vbWhite 

    .Font.Bold = True 

    i = i + 1 

End With 

Next 

' paste the result in output sheet 

Sheets("Output").Range("A2").CopyFromRecordset rs 

' format output sheet 

With Sheets("Output").UsedRange.Borders 

        .LineStyle = xlContinuous 

        .Weight = xlHairline 

        .ColorIndex = 5 

End With 

' close the connection and recordset 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

End Sub 

============================================================= 

Example 4 - Test Some Simple Queries on the data 

Option Explicit 

Sub example4() 

' declare variables 

Dim cn As ADODB.Connection 

Dim rs As ADODB.Recordset 

Dim i As Long 

Dim strfile As String 

Dim fld As ADODB.Field 

Dim strCon, strsql As String 

i = 1 

' we have to create the connection within this workbook 

strfile = ThisWorkbook.FullName 

' establish the connection 

' set the provider Provider=Microsoft.ACE.OLEDB.12.0 for excel 2007 and above 

'Provider=Microsoft.Jet.OLEDB.4.0 for excel 2003 

' data source ' file path 

'Extended Properties Excel 12.0 ' for excel 2007 and above 

'Extended Properties Excel 8.0 ' for excel 2003 

'HDR = the data is having header row yes or no. 

'"IMEX=1;" tells the driver to always read "intermixed" (numbers, 

dates, strings etc) data columns as text 

'' remeber by default it takes header names as field names 

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strfile & 

" ;Extended Properties=""Excel 12.0;HDR=YES"";" 

Set cn = CreateObject("ADODB.Connection") 

Set rs = CreateObject("ADODB.Recordset") 

' clear output sheet 

Sheets("Output").UsedRange.Clear 

cn.Open strCon 

' test some queries given below you can  remove the comment from them to test 

strsql = "TRANSFORM Sum(R_Sales) AS SumOfR_Sales SELECT R_YEAR FROM 

[Data$A1:c49] where [R_YEAR]= " & Int(InputBox("Enter year")) & " 

GROUP BY R_YEAR PIVOT R_Month" 

'strsql = "TRANSFORM Sum(R_Sales) AS SumOfR_Sales SELECT R_YEAR FROM 

[Data$A1:c49]  GROUP BY R_YEAR PIVOT R_Month" 

'strsql = "SELECT R_Year,SUM(R_Sales)as Sales FROM [Data$] GROUP BY R_Year" 

rs.Open strsql, cn 

' control check if it does not return any value 

If rs.EOF = True Then 

MsgBox "No Record Found" 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

Exit Sub 

End If 

For Each fld In rs.Fields 

With Sheets(1).Cells(1, i) 

    .Value = fld.Name 

    .Interior.Color = RGB(31, 73, 125) 

    .Font.Color = vbWhite 

    .Font.Bold = True 

    i = i + 1 

End With 

Next 

Sheets(1).Range("A2").CopyFromRecordset rs 

With Sheets(1).UsedRange.Borders 

        .LineStyle = xlContinuous 

        .Weight = xlHairline 

        .ColorIndex = 5 

End With 

rs.Close 

cn.Close 

Set rs = Nothing 

Set cn = Nothing 

End Sub 

Downlaod Working File https://www.box.com/s/ca069cb0bb3baa81ac5c