SQL in Excel?

[MAB]

In Excel 2000/XP is it possible to treat a range of cells as a table and run
sql statements against it? for example in sheet1 I have 3 columns Item_code,
quantity, amount. If I want to calculate the total quantity, and amount for
every Item to appear in Sheet2 which I could get by the following SQL
statement

select item_code, sum(quantity),sum(amount)
from table_name (or range of cells)
group by item_code

thx

 

[Bob Phillips]

MAB,

Very easy using an ODBC connection. I use ADo myself, others use other
techniques.

Here's an example using ADO, it copies the recordset to an array. In XL2002
you can copy a recordset to the workbook.

Dim objConn As ADODB.Connection
Dim iRow As Long
Dim sWorkbook As String
Dim sConnString As String
Dim SQLString As String
Dim aryRecordSet

With ActiveSheet
sWorkbook = "c:\myTest\myFile.xls"

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sWorkbook & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = New ADODB.Connection
objConn.Open sConnString

Dim oRS As ADODB.Recordset
Dim cCols As Integer, n As Integer

SQLString = "Select * From [Sheet1$]"

Set oRS = New Recordset
Set oRS = objConn.Execute(SQLString)
If Not oRS.BOF And Not oRS.EOF Then

aryRecordSet = oRS.GetRows()

End If

objConn.Close
Set oRS = Nothing
Set objConn = Nothing

End With

You need to reference the Microsoft ACtiveX Data Object Library anbd Data
Object Recoirdset Library in VBA.

 

[Tushar Mehta]

For a non-programmatic solution, check the 'RDBMS in Excel' tutorial on
my web site.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions