Pages

Saturday, July 14, 2012

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 

No comments:

Post a Comment