Lesson One… SQL in VBA
April 20, 2009

The references you'll needVital to any peice of reporting is timely and accurate data. New data needs to be imported and the whole process needs to be as quick and seemless as possible. While you could simply record or write VBA to open a given file, copy accross the data and close the file again. This may work, but there is infact a better, faster and more flexible way. In fact, you won’t even need to open the file your reading again!

Step 1:

Before we get started let’s lay the groundwork.  First things first let’s add a few references we’ll be needing, so press alt+F11 and you’ll get yourself to the Visual Basic editor. From there we need on the top menu “tools” and then “references”. Then you’ll need to add some in (top right):

Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library.

Step 2:

Now let’s add in the VBA, to do that we’ll need a new module and add the VBA in below:

Sub SQLinVBA(FullFileName As String,  SQL_Text As String, ToSheet As String)

‘ ///// aviator monkey /////

Dim Cn As ADODB.Connection
Dim FileFullName As String
Dim SheetName As String, SQL_Text As String
Dim Rst As ADODB.Recordset

‘ Define the closed excel workbook as database :
‘ Setting up a new connection :
Set Cn = New ADODB.Connection

‘ Connection using the ‘OLE DB Microsoft Jet’ provider

With Cn
.Provider = “Microsoft.Jet.OLEDB.4.0”
‘ With columns headers :
.ConnectionString = “Data Source=” & FileFullName & _
“;Extended Properties=””Excel 8.0;HDR=YES””;”
‘        ‘ Without columns headers
‘        .ConnectionString = “Data Source=” & FileFullName & _
“;Extended Properties=Excel 8.0;”
End With

Set Rst = Cn.Execute(SQL_Text)

‘ Write the request result in cell A1 :
Sheets(ToSheet).Range(“A1”).CopyFromRecordset Rst

‘ Close connection :
Set Rst = Nothing
Set Cn = Nothing

End Sub

Step 3:

Ok let’s try an example. Calling the subroutine (that’s fancy talk for using the code you just added), either by highlighting the code (below) an pressing F5 (the quickest way), or by running the macro in your preferred way…

Sub Go

Call SQLinVBA(“C:\TEST.xls”,”SELECT * FROM [Sheet1$]”,”Sheet2″)

End Sub

Replace C:\TEST.xls with the .xls file you want to read, Sheet2 with the sheet you’d like it written to (in your current workbook) and as for the the SQL text, SELECT * FROM [Sheet1$]: in this case it’s reading all the data from Sheet1, but there’s nothing stopping you doing almost anything you would in SQL normally, add a few where clauses, the odd “case” statement, knock youself out. Notice that the sheet name is surrounded by square brackets and a dollar symbol this is vital (not optional!) and if it’s not in there you’ll get throw all sorts of errors your way.

That’s it for today’s lesson, go play.