Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|