Using SQL (with a Group by function) inside EXCEL VBA



I have some data on a worksheet. I would like to use this data as input
into a SQL query (Select .... from by....) and load the query
output on a different worksheet. How can I set up a Excel VBA macro with a
SQL that will allow me to do this?




Tim Williams

Here's a sub I've used - you'll need to adapt to suit your particular needs.
I had two sheets codenamed shtContents (data to be queried) and shtQuery
(where query results end up)


Sub ExecSql()

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath, icount
Dim f As ADODB.Field
Dim sSQL As String
Dim sRange1 As String, sRange2 As String
Dim rngresults As Range

sSQL = " select a.[whatever],a.[whatever2] " & _
" from <r1> a where a.[whatever]='somevalue' " & _
" order by a.[whatever2] desc "

'build the "table" name
'eg: SELECT * FROM [Sheet1$E11:F23]
sRange1 = Rangename(shtContents.Range("A1").CurrentRegion)

If ActiveWorkbook.Path <> "" Then
sPath = ActiveWorkbook.FullName
MsgBox "Workbook being queried must be saved first..."
Exit Sub
End If

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties='Excel 8.0;HDR=Yes'"

sSQL = Replace(sSQL, "<r1>", sRange1)

'On Error Resume Next
Debug.Print sSQL

oRS.Open sSQL, oConn

If Err.Number <> 0 Then
MsgBox "Problem: " & vbCrLf & vbCrLf & Err.Description
GoTo skip
End If

On Error GoTo 0

If Not oRS.EOF Then

Set rngresults = shtQuery.Range("A4")
icount = 0
For Each f In oRS.Fields
rngresults(1).Offset(0, icount).Value = f.Name
icount = icount + 1
Next f
rngresults(1).Offset(1, 0).CopyFromRecordset oRS


MsgBox "No records found"

End If

On Error Resume Next

End Sub

Function Rangename(r As Range) As String
Rangename = "[" & r.Parent.Name & "$" & _
r.Address(False, False) & "]"
End Function

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question