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?


--

 

[joel]

It seem like you know SQL pretty well. You have afew choices.
Depending on the number of items you are returning your can setup a SQ
with multiple WHERE clauses to return all the items at one time o
retrieve the data multiple times with a single WBERE clause. there arr
two questions that need to be answered

1) Do you wnat to have the query automatically update?
2) Are the number of rows that are going to be returned each time th
data is retrieve the same number of rows or are the number of row
returned going to be different each time the data is retrieved?

You can get data from a database using a query or opening the databas
as a recordset like you would in Access. The recordset commands i
Excel VBA and Access VBA are the same as is probably the better metho
is the number of rows going to be different each time the query is run.
the disadvantage with the recordset is you would have to manually ru
the update or setup a timer to run the macro periodically. You can alo
use a workbook open event to retrieve the data as another option.

I think you need to think about which options you want before you as
for the correct code to impliment the options you choose

 

[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