use sql in excel

Using SQL in Excel VBA

QueryTables in Excel

OK–one more I need to add today. I find this one very exciting. If you are in the <0.1% of humanity who loves SQL but is sometimes limited to using Excel for data modelling I have reached my target audience ¬†ūüėČ

If you want to automate data manipulations in Excel that involve vlookups, sumifs, and their ilk it is often MUCH more effective to use a powerful SQL statement. In the past I have written routines that send Excel data to MS Access then pull it back in. But users often don’t have MS Access installed or configured. A few weeks ago I figured out how to automate¬†SQL in Excel. Should have been more obvious to me since I have used MS Query in Excel manually before–but the following¬†short code example shows how it’s done.

For this one to work you actually create a connection to another excel file (it works with .csv also). I am not sure how to create a connection to the file you’re code is in. But this can be gotten around by opening another workbook and copying your data there (via VBA) first. I have since learned you can also do joins and other complex queries using this method.

All you need to do to get started is:

  1. Open Excel
  2. Hit Alt+F11 to open the VBA editor
  3. Insert a new module then paste the simple example code into the code editor
  4. Ensure you change the connection string to look at the path of a valid file on your machine
  5. Name the range you want to put in your from clause (i.e. the table in your sql statement) as ‘csvout’ as below, or otherwise–and adjust the sql to match your column headers (i.e. [sales person] and [ticket price per unit] below)
  6. Click F8 repeatedly to execute the routine (get the files I used here)
Simple Example of Using SQL in Excel
Sub xlsql() 'routine for demonstrating an sql query within excel

Dim qt1 As QueryTable
Dim cn As String

cn = "ODBC;DSN=Excel Files;DBQ=C:\Users\cgoldie\Downloads\data2.xlsx" 'create a link to an Excel file on your machine
Set qt1 = ActiveSheet.QueryTables.Add(cn, Cells(1, 1), "select * from csvout") 'csvout is a named range in the excel file--sql treats it like a table
qt1.CommandText = "select [sales person], sum([ticket price price per unit]) as tp2 from csvout group by [sales person]"
qt1.Refresh

End Sub

Here is a more complex example after some more experimenting and other work:

More Detailed Example of using SQL in Excel
Excel VBA routine incorporating declaration of global variable scope, passing vars to another routine, message box response options, and basic error handler logic:


Dim bk1, bk2, bk3 As Workbook 'declare global variables (to be retained across all routines and functions)
Dim sht1, sht2, sht3, sht4 As Worksheet
Dim rg1, rg2, rg3 As Range
Dim i, j, k, n As Long
Dim x, y, z As Integer
Dim csvpath, bflpath, cn1, sql1, path3, oilu, gasu, sql2, sql3 As String
Dim qt1 As QueryTable

Public Sub fcasts() 'a routine for transferring .csv outputs from Cash to the R1 bfl template .xls file, ready for importing to R1

'Before running the routine ensure your csv data is clean:
' - in P1 there is a strict one-to-one relationship between objective id, class, subclass
' - you have a valid BFL loader file as well as a Cash-generated .csv export available
' - refer to the ctrlroom tab in this workbook for more guidance
' - Craig Goldie, Warrior Consultant, Apr 2015

Application.ScreenUpdating = False
On Error GoTo handler

'open bfl file
'bflpath = InputBox("Enter the full path of the BFL file/template" & Chr(13) & "you want to transfer data to")
bflpath = "C:\Users\C.Goldie\Desktop\work2\201504\0414ranr_p1r1_\sbox_0420\bfl0429.xlsx"
Set bk1 = Workbooks.Open(bflpath)
'open cash csv file
'csvpath = InputBox("Enter the full path of the Cash .csv output file" & Chr(10) & "from a compatible cash preset")
csvpath = "C:\Users\C.Goldie\Desktop\work2\201504\0414ranr_p1r1_\sbox_0420\d_csv.csv"
Set bk2 = Workbooks.Open(csvpath)

'***populate the Objective Data tab
Set sht1 = bk2.Sheets(1)
bk2.Names.Add "d_csvout", sht1.Cells(1, 1).CurrentRegion 'name csv data range
Set sht2 = bk1.Sheets("objective data")
sht2.Range("6:64000").Cells.Delete 'clear out any residual data on bfl objective data tab

cn1 = "ODBC;DSN=Excel Files;DBQ=" & csvpath ' set up connection to csv data
Set sht1 = ThisWorkbook.Sheets.Add
Set qt1 = sht1.QueryTables.Add(cn1, sht1.Cells(1, 1)) 'pull queried csv data to paste in objective data tab
qt1.CommandText = "select distinct [r1 objective id],null as objn,null as resnm,null as fldnm,[resource class],[resource subclass], 1 as pos1,null as spare1,'N',null as spare2,'Y','31-Dec-2099',null as cpeep,null as comments,[sensitivity run] " & _
 "from d_csvout " & _
 "where len([r1 objective id])>6"
qt1.Refresh False

sht1.Cells(1, 1).CurrentRegion.Copy
sht2.Cells(6, 1).PasteSpecial xlPasteValues
sht2.Range("6:6").Delete
sht2.Range("b6:d64000,h7:h64000,j7:j64000,m7:n64000").ClearContents
sht2.Range("g6:g64000").NumberFormat = "0%"

'***populate the Forecast Rates tab
Set sht3 = bk2.Sheets(1)
i = InStr(sht3.Range("i1"), "(")
j = InStr(sht3.Range("i1"), ")")
gasu = Mid(sht3.Range("i1"), i + 1, j - i - 1) 'find gas units
i = InStr(sht3.Range("l1"), "(")
j = InStr(sht3.Range("l1"), ")")
oilu = Mid(sht3.Range("l1"), i + 1, j - i - 1) 'find oil units
If oilu = "m3/d" Or oilu = "bbl/d" Then sulu = "T/d" Else sulu = "MT/d"

x = MsgBox("You are transferring the following units to the BFL file:" & Chr(13) & Chr(13) & _
 "Gas Units: " & gasu & Chr(13) & "Oil/Liquids Units: " & oilu & Chr(13) & "Sulphur Units: " & sulu & Chr(13) & Chr(13) & _
 "If this is as expected click OK (and please adjust the BFL.xls file units to match prior to " & _
 "loading into Resource One), otherwise perform another csv export from Cash " & _
 "and select Metric or Imperial units and Medium or Large scale on the 'Other Settings' tab " & _
 "when creating the csv file.", vbOKCancel, "Units Notice!!") 'flag current units to user
If x <> 1 Then
 Call tidy
 Exit Sub
End If

Set sht2 = bk1.Sheets("Forecast Rates")

sht2.Range("7:64000").Cells.Delete
sql1 = "select [r1 objective id],'' as var1,'' as var2,'' as var3,[resource class],[resource subclass], [sensitivity run], '31-Dec-2099','' as var4,right([period name],4), '' as var5, " & Chr(13) & _
 "sum(iif(partner='Gross',[Oil 100% WH(" & oilu & ")],0)) as 100oilwh, " & Chr(13) & _
 "sum(iif(partner='Gross',[Oil 100% F&L(" & oilu & ")],0)) as 100oilfl, " & Chr(13) & _
 "sum(iif(partner='Gross',[Oil 100% CiO(" & oilu & ")],0)) as 100oilcio," & Chr(13) & _
 "sum(iif(partner='Gross',[Oil 100% AfS(" & oilu & ")],0)) as 100oilafs, " & Chr(13) & _
 "sum(iif(partner='Gross',[Cond 100% WH(" & oilu & ")],0)) as 100condwh, " & Chr(13) & _
 "sum(iif(partner='Gross',[Cond 100% F&L(" & oilu & ")],0)) as 100condfl," & Chr(13) & _
 "sum(iif(partner='Gross',[Cond 100% CiO(" & oilu & ")],0)) as 100condcio, " & Chr(13) & _
 "sum(iif(partner='Gross',[Cond 100% AfS(" & oilu & ")],0)) as 100condafs, " & Chr(13) & _
 "sum(iif(partner='Gross',[NGL 100% WH(" & oilu & ")],0)) as 100nglwh," & Chr(13) & _
 "sum(iif(partner='Gross',[NGL 100% F&L(" & oilu & ")],0)) as 100nglfl, "
sql2 = "sum(iif(partner='Gross',[NGL 100% CiO(" & oilu & ")],0)) as 100nglcio, " & Chr(13) & _
 "sum(iif(partner='Gross',[NGL 100% AfS(" & oilu & ")],0)) as 100nglafs, " & Chr(13) & _
 "sum(iif(partner='Gross',[Gas 100% WH(" & gasu & ")],0)) as 100gaswh," & Chr(13) & _
 "sum(iif(partner='Gross',[Gas 100% F&L(" & gasu & ")],0)) as 100gasfl, " & Chr(13) & _
 "sum(iif(partner='Gross',[Gas 100% CiO(" & gasu & ")],0)) as 100gascio, " & Chr(13) & _
 "sum(iif(partner='Gross',[Gas 100% AfS(" & gasu & ")],0)) as 100gasafs," & Chr(13) & _
 "sum(iif(partner='Gross',[Prod Water 100% WH(" & oilu & ")],0)) as 100pwaterwh, " & Chr(13) & _
 "sum(iif(partner='Gross',[Inj Water 100% WH(" & oilu & ")],0)) as 100iwaterwh, " & Chr(13) & _
 "sum(iif(partner='Gross',[CO2 100% WH(" & gasu & ")],0)) as 100co2wh," & Chr(13) & _
 "sum(iif(partner='Gross',[H2S 100% WH(" & gasu & ")],0)) as 100h2swh, " & Chr(13) & _
 "sum(iif(partner='Gross',[Sulphur 100% WH(" & sulu & ")],0)) as 100sulphurwh, " & Chr(13) & _
 "sum(iif(partner='Gross',[N2 100% WH(" & gasu & ")],0)) as 100n2wh, " & Chr(13) & _
 "sum(iif(partner='Gross',[Other 100% WH(" & gasu & ")],0)) as 100otherwh," & Chr(13) & _
 "sum(iif(partner='SO',[Oil SWIS F&L(" & oilu & ")],0)) as swisoilfl, " & Chr(13) & _
 "sum(iif(partner='SO',[Oil SWIS CiO(" & oilu & ")],0)) as swisoilcio, " & Chr(13) & _
 "sum(iif(partner='SO',[Oil SWIS AfS(" & oilu & ")],0)) as swisoilafs,"
sql3 = "sum(iif(partner='SO',[Cond SWIS F&L(" & oilu & ")],0)) as swiscondfl," & Chr(13) & _
 "sum(iif(partner='SO',[Cond SWIS CiO(" & oilu & ")],0)) as swiscondcio, " & Chr(13) & _
 "sum(iif(partner='SO',[Cond SWIS AfS(" & oilu & ")],0)) as swiscondafs, " & Chr(13) & _
 "sum(iif(partner='SO',[NGL SWIS F&L (" & oilu & ")],0)) as swisnglfl, " & Chr(13) & _
 "sum(iif(partner='SO',[NGL SWIS CiO(" & oilu & ")],0)) as swisnglcio, " & Chr(13) & _
 "sum(iif(partner='SO',[NGL SWIS AfS(" & oilu & ")],0)) as swisnglafs," & Chr(13) & _
 "sum(iif(partner='SO',[Gas SWIS F&L(" & gasu & ")],0)) as swisgasfl, " & Chr(13) & _
 "sum(iif(partner='SO',[Gas SWIS CiO(" & gasu & ")],0)) as swisgascio, " & Chr(13) & _
 "sum(iif(partner='SO',[Gas SWIS AfS(" & gasu & ")],0)) as swisgasafs," & Chr(13) & _
 "sum(iif(partner='SO',[Prod Water SWIS WH(" & oilu & ")],0)) as swispwaterwh, " & Chr(13) & _
 "sum(iif(partner='SO',[Inj Water swis WH(" & oilu & ")],0)) as swisiwaterwh," & Chr(13) & _
 "sum(iif(partner='SO',[CO2 swis WH(" & gasu & ")],0)) as swisco2wh, " & Chr(13) & _
 "sum(iif(partner='SO',[H2S swis WH(" & gasu & ")],0)) as swish2swh, " & Chr(13) & _
 "sum(iif(partner='SO',[Sulphur swis WH(" & sulu & ")],0)) as swissulphurwh, " & Chr(13) & _
 "sum(iif(partner='SO',[N2 swis WH(" & gasu & ")],0)) as swisn2wh, " & Chr(13) & _
 "sum(iif(partner='SO',[Other swis WH(" & gasu & ")],0)) as swisotherwh, " & Chr(13) & _
 "sum(iif(partner='SO',[Oil GES AfS(" & oilu & ")],0)) as gesoilfl, " & Chr(13) & _
 "sum(iif(partner='SO',[Cond GES AfS(" & oilu & ")],0)) as gescondfl," & Chr(13) & _
 "sum(iif(partner='SO',[NGL GES AfS(" & oilu & ")],0)) as gesnglfl, " & Chr(13) & _
 "sum(iif(partner='SO',[Gas GES AfS(" & gasu & ")],0)) as gesgasfl, " & Chr(13) & _
 "sum(iif(partner='SO',[Prod Water SWIS WH(" & oilu & ")],0)) as gespwaterwh " & Chr(13) & _
 "from d_csvout " & Chr(13) & _
 "where len([r1 objective id])>6 " & Chr(13) & _
 "group by [r1 objective id],[resource class],[resource subclass], [sensitivity run],right([period name],4)"
sql4 = sql1 & sql2 & sql3
qt1.CommandText = sql4
qt1.Refresh False
sht1.Cells(1, 1).CurrentRegion.Copy
sht2.Cells(7, 1).PasteSpecial xlPasteValues
sht2.Range("7:7").Delete
sht2.Range("b7:d64000,i7:i64000,k7:k64000").ClearContents

path3 = bk1.Path & "\bfl_" & LCase(Format(Now(), "yyyymmmdd_hhmmss")) & ".xlsx"
bk1.SaveAs path3
Call tidy
MsgBox "A file is now ready for loading to Resource One and has been saved here: " & Chr(13) & Chr(13) & path3
Exit Sub

handler: 'error handler in case of error
 Call tidy
 MsgBox "An unhandled error has occurred. Contact Craig Goldie at C.Goldie@smell.com or another palashir consultant for assistance.", vbOKOnly, "Error!"
End Sub

Sub tidy() 'close open files, delete query sheet

Set qt1 = Nothing
Application.DisplayAlerts = False
sht1.Delete
Application.DisplayAlerts = True
bk2.Close False
bk1.Close False
Application.ScreenUpdating = True

End Sub

 

2 thoughts on “Using SQL in Excel VBA”

  1. Hi thomas, an outer join is probably not supported by the version of SQL used here.. If it is the same as Jet SQL (that MS Access uses) then it won’t work. But you can get around that issue by using two left joins (ie. create table3 as table1 left join table2, then create table 4 as table2 left join table 1 then stack using append or use a union query–is a pain, but will work.

  2. do you know do use sql outer join with this method ?
    i search to make an outer join with multiple files and not just between the sheets of the same workbook
    kind regards
    Thomas

Share your thoughts below :)