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


0
8/16/2003 4:54:12 AM
excel 39879 articles. 2 followers. Follow

2 Replies
774 Views

Similar Articles

[PageSpeed] 47

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" <erwffsdfsfwefsdfsdfwewf@yahoo.com> wrote in message
news:bhkdg6$e94p$1@ID-31123.news.uni-berlin.de...
> 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
>
>


0
bob.phillips (411)
8/16/2003 9:12:40 AM
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 <bhkdg6$e94p$1@ID-31123.news.uni-berlin.de>, 
erwffsdfsfwefsdfsdfwewf@yahoo.com 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
> 
> 
> 
0
ng_poster (159)
8/17/2003 4:40:30 PM
Reply:

Similar Artilces:

Multi-Page Word Doc Linked to Excel?
How do I get a multi-paged word document to show ALL pages when it is object-linked to an Excel Spreadsheet? I've tried everything I can think of, and it only shows the first page in Excel. Please help, and thanks in advance. -Kalea AFAIK only the first page will display on the wksht. In order to access the entire doc, double-click it so it will open in Word. HTH |:>) "Kalea" wrote: > How do I get a multi-paged word document to show ALL pages when it is > object-linked to an Excel Spreadsheet? I've tried everything I can think of, > and it only shows ...

inset rows and copy formatting , excel macro
i need a macro that inserts rows the same as the macro below but will also copy the formatting and formula from the row above Sub Macro1() '-- Ken Wright, 2003-08-09 Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long Dim Rng As Range Dim lastrw As Long numRows = InputBox("How many Rows") lastrw = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A")) For r = Rng.Rows.Count To 1 Step -1 Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = Tru...

Chart Question for Excel 2003 **Desperate**
I'm using Excel 2003 In the assignment for the students we have two charts (clustered column and a pie chart) based on values on the Statistics tab. The file is Contest.xls I'm working on a macro that will mark the assignment in a matter of seconds (as there are many students and many other assignments. I have everything but the charts prepared for the macro. I would like to compare what the student has selected, (ie. Chart Type>Standard Type>Clustered Column, Source Data>Data Range>=Statistics!$C$4:$H$8>Series: Rows or Columns, etc) with the same data we kn...

Exporting Excel file into Notepad with column & row lines
I cannot get the horizontal & vertical lines to show up in my notepad file. Help Please. Notepad looks at text. Text doesn't have horizontal & vertical lines. You can, of course, use underscore characters _ to look like a horizontal line, and pipe characters | to look like a vertical line. -- David Biddulph "JIVL" <JIVL@discussions.microsoft.com> wrote in message news:232B20B7-2719-47ED-A2C3-5673B8FCA925@microsoft.com... >I cannot get the horizontal & vertical lines to show up in my notepad file. > Help Please. ...

Excel count with 2 conditions
I'm sure this is an easy one for you but I just can't get it to work correctly. I have a spreadsheet that I want to count certain criteria. Example: A B C Name Gender Score Calvin M 3 William M 4 Jessica F 1 Sarah F 3 Bryan M 1 Anna F 3 I need to how many females scored a 3. What would you suggest I use for a formula? =SUMPRODUCT(--(B1:B6="F")*(C1:C6=3)) "janet" wrote: > I'm sure this is an easy...

Excel function help facilities
When using an Excel function, the Fx button in the formula bar will produce a window which reveals the parameters and values of the function. At the bottom of this there are provisions for help on the function. What can I specify when writting a function which will pass help info through this facility? Hi RPS, This is not natively supported in Excel but see Laurent Longre's FunCustomize Addin at: http://longre.free.fr/english/ --- Regards, Norman "RPS" <RPS@discussions.microsoft.com> wrote in message news:6EBA6AB9-EC1F-4067-AD14-9D9A6A2F56DC@microsoft....

how can i make paragraphs in side one cell in Excell?
Hi press ALT & ENTER when you want a new line Cheers JulieD "ashraf" <ashraf@discussions.microsoft.com> wrote in message news:E508A2C8-CC79-4107-93EE-5D607A53E8BA@microsoft.com... > or, format cell / alignment / wrap text ...

Subtotal Lines in the wrong place Excel 2003
When nesting subtotals in Excel 2003, the subtotal lines are not in the correct position. Is there a fix or a work around for this problem? Example: Craft Code Crew Count BA Elec 1 BA Elec 1 BA Total 2 Elec Total 2 Hi AFAIK you should start with the subtotal for the right-most column and then add the other columns step by step -- Regards Frank Kabel Frankfurt, Germany "k2quayle" <k2quayle@discussions.microsoft.com> schrieb im Newsbeitrag news:FF252538...

.NET-BroadcastEventWindow.1.0.5000.0.2f: EXCEL.EXE
My Windows 2000 system suddenly started giving this error message whenever I log in. I do not launch Excel before getting this error: ..NET-BroadcastEventWindow.1.0.5000.0.2f: EXCEL.EXE - Application Error The instruction at "0x308f3c7c" referenced memory at "0x00000004". The memory could not be "read". Click OK to terminate the program Click CANCEL to debug the program [OK] [Cancel] Why am I getting this error, and how can I fix it? I noticed that after I click OK, Task Manager shows one or two copies of EXCEL.EXE still running. But I did not (knowingly) ...

Antivirus for Macros in Excel 97
First time here. Sorry if this has been covered or is in a FAQ but I searched and didn't find the answer. I have Excel 97 and want to know the easiest (read: automatic?) way to check macros for viruses. It's some examples I've downloaded from sites that I can probably trust but..you never know. I have avast! scanner (free version). I suppose if you download the file in XLS format it would check it? But most come in ZIP files. Do I have to manually run the unzipped macro through the scanner to check for viruses or is there a way to tell Excel (or the scanner) when I open a f...

I need an excel plug in to save workbooks in adobe acrobat format
I used to have this feature on the toolbar, but following a severe recent excel and outlook crash, it got wiped off !! There seems to be no way to restore this essential facility, of choosing to save excel workbooks in a pdf format instead of xls. DOes anyone know how to restore it? Hi Chris, Try-File, Print, then select Acrobat Distiller. It will then ask you where you want the .pdf file to be saved. Thats how it works on mine. Hope that helps "chris brown" wrote: > I used to have this feature on the toolbar, but following a severe recent > excel and outlook crash,...

Excel demands installation disc
I've just updated Win XP to Service Pack 3 and installed probably hundreds of windows updates, which I believe includes a few MS Office updates. Now when I try to open Excel, it says "Preparing to install..." and demands the installation CD. With persistence I can get past this by clicking "Cancel" 3 times, but my desktop icons of spreadsheets won't open Excel. Was an Office update responsible for this? Or is there another solution? Thanks, Bobbi This is Excell 2003 -Bobbi "Bobbi" <bobbi@example.invalid> wrote in message n...

how to open 1-2-3 files with excel
I cannot open my old 1-2-3 files with excel. I need help. Office File Converter Pack http://www.microsoft.com/downloads/details.aspx?FamilyID=cf196df0-70e5-4595-8a98-370278f40c57&DisplayLang=en This includes LOTUS32.CNV, which is the Lotus 123 converter. You should note that you can ONLY OPEN Lotus 123 files with this converter. "Tanmen" <Tanmen@discussions.microsoft.com> wrote in message news:FAD82E0E-4806-401E-B49F-7365FC64FBC3@microsoft.com... > I cannot open my old 1-2-3 files with excel. I need help. Tanmen Wrote: > I cannot open my old 1-2-3 files with...

Preserving Excel settings on reinstall
I am setting up my system from scratch. I have customized excel with buttons, macros, etc. Is there a way to copy these settings from the old to the new installation? Thanks, Thorbjorn The toolbar file has the extension of *xlb, then you can also copy over the personal.xls if you are using it in your customization -- Regards, Peo Sjoblom "Thorbjorn Sundboe" <please@replytonewsgroup.com> wrote in message news:us9iqvyIFHA.588@TK2MSFTNGP15.phx.gbl... > I am setting up my system from scratch. I have customized excel with > buttons, macros, etc. Is there a way to cop...

Block a excel file from being copied?
I am trying to stop others from saving a file under a different name. Is it possible? Not really. Users can copy the file using windows explorer. And anything I could do to make sure that it's where it's supposed to be (and named correctly) would rely on macros. And macros can be disabled. SG Hurst wrote: > > I am trying to stop others from saving a file under a different name. Is it > possible? -- Dave Peterson Perhaps "hide" everything in the file, and then have auto-open macro check file name and location, if success, unhide? Dave...

compiling an Excel spreadsheet
Can you compile an Excel spreadsheet into an .exe file? Alex You may want to investigate the possibilities presented by Visual Baler. http://the-ciba.com/vbaler/vbaler.html I have never used it nor know anyone who has so cannot vouch for its functionality. You could also investigate the use of Visual Basic(not VBA) to create stand-alones. Gord Dibben Excel MVP On Thu, 10 Feb 2005 13:59:02 -0800, "Alex5" <Alex5@discussions.microsoft.com> wrote: >Can you compile an Excel spreadsheet into an .exe file? Years ago I used VB with lotus 2.3 to create stand alone programs....

How do I create a polar plot in Excel?
As header. Doesn't seem to be a facilility in Excel to do this. NB. This is not about 'radar graphs' - I need range and angle to be plotted.... TIA. Hi, Polar plots are not a standard excel chart. You can however built them. http://www.andypope.info/charts/polarplot.htm http://www.andypope.info/charts/polarplot2.htm Cheers Andy Glennk wrote: > As header. > > Doesn't seem to be a facilility in Excel to do this. > > NB. This is not about 'radar graphs' - I need range and angle to be > plotted.... > > TIA. -- Andy Pope, Microsoft MVP...

Footer Settings in an Excel Sheet
Hi, I am setting up the footer to an Excel page through VBA Script but sometime, the left footer is not starting from the extreme left (i.e., it leaves some spaces and seems like started from the column B). The following script used to set up the footer. Range("A3:M101").Select With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = analyst .CenterFooter = filename .RightFooter = mydate .LeftMargin = Application.InchesToPoints (0.236220472440945)...

Comparing 2 Excel files
I have 2 Excel spreadsheets each with about 7 sheets in them tha contain tables with data. Each sheet has a header, which describe what is in the column. Our customer would like us to create a progra that will compare the 2 Excel files and list out what is differen between the two of them. For example, there may be new rows of dat that is in one file but not in the other, rows of data may have bee deleted, or the information in a row may have changed. Is there an way that the tables can be compared between the two files -- Message posted from http://www.ExcelForum.com Hi, Take a look: j...

Portal 2.0 export to excel non functional
Has anyone else run into issues with BP where the sharepoint base 'export to excel' functionality does not work ? I have talked to MS product support for BP, and Sharepoint and at the end of much 'try this/try that' they have told me to reinstall. Well, thats all well and fine but the users have in the meantime placed significant content on the system and I am not yet comfortable with backup and restoring this system. More worrisome is the fact that attempting to create a test box installed in the exact same fashion as the first to test restoring resulted in a non-fun...

Excel #42
when opening excel document it says that the document is already opened, however it is not. this is on an new installation on window xp. you click o.k. and it goes away and everything is o.k. but it is annoying Maybe Debra Dalgleish has the answer: http://www.contextures.com/xlfaqApp.html#AlreadyOpen Terry Bosch wrote: > > when opening excel document it says that the document is > already opened, however it is not. this is on an new > installation on window xp. you click o.k. and it goes away > and everything is o.k. but it is annoying -- Dave Peterson ec35720@msn.com ...

Converting a Word file to excel spreadsheet
I need to convert a word file of some 300 addresses in the following format: Last Name, First name Address City, State Zip Phone E-mail to an Excel spreadsheet in order to do a mail merge for labels. Does anyone know of a straightforward way to do this? ...

Does Excel support mixed formatting of a string in a formula cell, yet?
Excel is one of the most spectacular apps ever created. But one feature I really need is mixed formatting of a string whose cell is a formula. By mixed formatting I mean, you know, making selected (as opposed to all) character(s) of the string bold, italic, a different color, sub- or superscripted, etc. In Excel 2002 you cannot do that in a formula cell (right?). In edit mode, the user would, say, press F9 to display the calc'ed string, selectively format it as usual, then cancel edit. Excel would save the formatting as a separate mask. What could be easier? The user would ...

Auditoria SQL 2000
Buenas tardes, Me han pedido auditar lo que pasa en las bases de datos, desde los usuario hasta lo que hace el dba. Tengo un SQL 2000 sp4 y windows 2000 server SP4 como puedo habilitar este detalle de registro de sucesos? Gracias, Jaime In news:upBatvLwFHA.3312@TK2MSFTNGP09.phx.gbl, Jaime Sepulveda <jsepulveda@laphar.com> typed: > Buenas tardes, > > Me han pedido auditar lo que pasa en las bases de datos, desde los > usuario hasta lo que hace el dba. > > Tengo un SQL 2000 sp4 y windows 2000 server SP4 > > como puedo habilitar este detalle de registro d...

Updated: Excel, PowerPoint Disappeared, Only Word remains
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Yesterday my autoupdater prompted me for a new office update. As it was installing the whole system froze and I shut it down. (I decided to try the update again at a later time.) Today, I went to open an Excel file, only Excel is missing. So is everything else. The only application that now remains in my Microsoft Office folder is Word. I've tried searching the system for them, checked Trash, etc. They are definitely gone. Is there anyway to get them back? Or how do I reinstall them? (Can I download them or do I need t...