ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   SQL in Excel? (https://www.excelbanter.com/excel-programming/274543-sql-excel.html)

MAB[_2_]

SQL in Excel?
 
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[_5_]

SQL in Excel?
 
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.

--

HTH

Bob Phillips

"MAB" wrote in message
...
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





Tushar Mehta[_6_]

SQL in Excel?
 
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

In article ,
says...
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





All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com