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

[Alpha09]

Hi,
I have some data on a worksheet. I would like to use this data as input
into a SQL query (Select .... from Sheet1....group 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)

Tim

'*************************************
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
Else
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

shtQuery.UsedRange.ClearContents
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

Else

MsgBox "No records found"

End If

skip:
On Error Resume Next
oRS.Close
oConn.Close

End Sub

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