Passing Cell values to a SQL statement

I have set up a spreadsheet that uses excel's "Get external data"
feature to connect to a SQL database and run a querry. The problem is
that I need to get data for several different product codes. These
codes change from time to time but basically the querry looks like
this

Select * from SalesData
Where
SalesData.productCode = 1
Or 
SalesData.productCode = 2

is there a way to set it up such that the product codes are passed to
the statement from cell values in a given range? This way it would
dynamically retrive whatever I am looking for.

Also if any of you double as SQL wizzards, can I pass an array of
values into my Where clause so that I don't have to list each
sepperately?

Thanks.

Tyler
0
4/5/2004 3:43:51 PM
excel 39879 articles. 2 followers. Follow

3 Replies
573 Views

Similar Articles

[PageSpeed] 30

Hi

You need a procedure which will rewrite the CommandText propertie for querie
object. Here is an example
Public Sub UuendaP�ringud()
    kuu = ActiveSheet.Range("F1").Value
    aasta = ActiveSheet.Range("F2").Value
    ...
    Set qtQtrResults = Worksheets("Tasud").QueryTables(1)
    Sheets("Tasud").Activate
    ActiveSheet.Range("A2").Select
    With qtQtrResults
        .CommandType = xlCmdSql
        .CommandText = _
            "SELECT a.tabn, SUM(a.summa) AS arvest, "&_
            "SUM(IIF(a.sotsmaks,1,0)*a.summa) AS sotsalus, "&_
            "a.allyksus FROM tasud a "&_
            "WHERE a.tabn<>'' AND a.summa>0 AND a.tl<'060' AND "&_
            "Year(a.sisestus)=" & aasta & " AND "&_
            "Month(a.sisestus)=" & kuu & " "&_
            "GROUP BY a.allyksus, a.tabn ORDER BY a.tabn"
        .Refresh
    End With
.....
End Sub


--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"Tyler WIllis" <TCWillis79@hotmail.com> wrote in message
news:7fb60844.0404050743.516521c4@posting.google.com...
> I have set up a spreadsheet that uses excel's "Get external data"
> feature to connect to a SQL database and run a querry. The problem is
> that I need to get data for several different product codes. These
> codes change from time to time but basically the querry looks like
> this
>
> Select * from SalesData
> Where
> SalesData.productCode = 1
> Or
> SalesData.productCode = 2
>
> is there a way to set it up such that the product codes are passed to
> the statement from cell values in a given range? This way it would
> dynamically retrive whatever I am looking for.
>
> Also if any of you double as SQL wizzards, can I pass an array of
> values into my Where clause so that I don't have to list each
> sepperately?
>
> Thanks.
>
> Tyler


0
garbage (651)
4/5/2004 5:24:20 PM
You can use the IN keyword i.e. 

  Select * from SalesData
  Where SalesData.productCode IN(1,2)

Performace will depending on which DBMS you are using, so do some
testing if you intend to use

Again depending on DBMS product, it is preferable to create a stored
procedure on the server side and get the client (Excel) to pass the
values for the IN clause as parameters (might get away with passing as
a single string paramter?)

--

TCWillis79@hotmail.com (Tyler WIllis) wrote in message news:<7fb60844.0404050743.516521c4@posting.google.com>...
> I have set up a spreadsheet that uses excel's "Get external data"
> feature to connect to a SQL database and run a querry. The problem is
> that I need to get data for several different product codes. These
> codes change from time to time but basically the querry looks like
> this
> 
> Select * from SalesData
> Where
> SalesData.productCode = 1
> Or 
> SalesData.productCode = 2
> 
> is there a way to set it up such that the product codes are passed to
> the statement from cell values in a given range? This way it would
> dynamically retrive whatever I am looking for.
> 
> Also if any of you double as SQL wizzards, can I pass an array of
> values into my Where clause so that I don't have to list each
> sepperately?
> 
> Thanks.
> 
> Tyler
0
onedaywhen (103)
4/6/2004 10:18:29 AM
Thanks, Exactly the sort of answers I was looking for. Sorry for not
getting this into the right section. I didn't notice the ADO thread
till after I posted.

onedaywhen@fmail.co.uk (onedaywhen) wrote in message news:<b8c9d0b7.0404060218.2c0a592d@posting.google.com>...
> You can use the IN keyword i.e. 
> 
>   Select * from SalesData
>   Where SalesData.productCode IN(1,2)
> 
> Performace will depending on which DBMS you are using, so do some
> testing if you intend to use
> 
> Again depending on DBMS product, it is preferable to create a stored
> procedure on the server side and get the client (Excel) to pass the
> values for the IN clause as parameters (might get away with passing as
> a single string paramter?)
> 
> --
> 
> TCWillis79@hotmail.com (Tyler WIllis) wrote in message news:<7fb60844.0404050743.516521c4@posting.google.com>...
> > I have set up a spreadsheet that uses excel's "Get external data"
> > feature to connect to a SQL database and run a querry. The problem is
> > that I need to get data for several different product codes. These
> > codes change from time to time but basically the querry looks like
> > this
> > 
> > Select * from SalesData
> > Where
> > SalesData.productCode = 1
> > Or 
> > SalesData.productCode = 2
> > 
> > is there a way to set it up such that the product codes are passed to
> > the statement from cell values in a given range? This way it would
> > dynamically retrive whatever I am looking for.
> > 
> > Also if any of you double as SQL wizzards, can I pass an array of
> > values into my Where clause so that I don't have to list each
> > sepperately?
> > 
> > Thanks.
> > 
> > Tyler
0
4/6/2004 9:10:50 PM
Reply:

Similar Artilces:

Spreadsheet name in a Cell
I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3$94f$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

Update Query to Change Values From One Table To Another
Hi, I have two tables with a number of fields in each table. In one table called ACCOUNTS CHARGED the yes/no field called CHARGED is always by default "yes" everytime a record is entered in it. I want it every time to update the table called RESPEL ALL CHARGES (when running an Update Query) the identical field CHARGED by looking at the RESNO field in the table ACCOUNTS CHARGED to be the same as the RESNO field in the table RESPEL ALL CHARGES. Thanks ...

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

Automating earnings statements
We are interested in either: creating a location where employees can securely look up their earnings statement; or where we distribute each individual earnings statement to a private e-mail address (where the employee can choose to either print or not). Any advice for either option above would be highly appreciated. Thanks in advance, Kerry Business Portal's Employee Self Service module called Employee Pay has this functionality. I've got a couple of clients using this module. One is a hospital with about 750 employees. It's very easy to use. -- Charles Allen, MVP ...

help for sql select command !!!
Hi Everyone! Imagine we have 3 checkBoxes in a search page and 3 boolean fields in DB table and every checkbox is for one of these fields . In search page users check every 3 checkBoxes and what i want is that first it shows the records that every their 3 fields are checked then 2 and then 1(DESC). BTW my DB is MS Access. Please help me ! On Mar 1, 7:17=A0pm, "miladha...@gmail.com" <miladha...@gmail.com> wrote: > Hi Everyone! > Imagine we have 3 checkBoxes in a search page and 3 boolean fields in > DB table and every checkbox is for one of these fields . I...

#Value Error #3
Hi I'm using this formula =IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2 and coping it down a column and as long as column E is not null it returns the value and if column E is null I get the #VALUE Error. Can this be told to return blank if colunm E has a null vallue? The formula is written in column I. "RayG" <RayG@discussions.microsoft.com> wrote in message news:A1AD5C13-D52C-424B-B574-22293BE72049@microsoft.com... > =IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,...

incremental counter for dup number values
my wife, accountant, is very often bringing home work where she wants to match offsetting records from two record sets by value only. However, where one set may have 10 instances of $25, the other may only have 2 instances of $25. How can I effectively limit the pick of only two records from the set with 10 dups? ...

portfolio value over time
I run Money 2004 and i can't do some things i think are really basic - main two (related issues) that bother me are: 1) is there any way to view the value of my entire investment portfolio over time (e.g. as a chart showing basically just price * volume of shares i own for each investment, combined to show all investments together)? 2) is there any way to view price history for all my holdings as an indexed value (0% at the start growing up or down based on % change in price) as opposed to absolute value? Some shares are $20 each some are $200 each - doesn't make sense to sho...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

Querry regarding statement periods
When balancing my accounts, the default period is always monthly. (In Ver. 14.0) Is there any way to change this to quarterly half yearly? for accounts that don't get much traffic> TIA Peter. ************ No. But all that happens is you have to change the balanced to date in the genie when you go to balance. "LPS-AU" wrote: > When balancing my accounts, the default period is always monthly. (In Ver. > 14.0) Is there any way to change this to quarterly half yearly? for accounts > that don't get much traffic> ...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =$C$5*D12 will do the trick ...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Delete record(s) in other cells based on value of one cell.
What I have is a protected sheet, where all columns except for 3 are protected. I cannot delete the entire row with protection turned on. So, what I need is to be able to delete the value in these three columns, only when the value in column A is "d". Any clues? Protection must remain on, users are using this spreadsheet and I cannot allow them to have access to the formulas in these other columns. Help...Micheal Provide a macro that unprotects the worksheet, does the work and reprotects the worksheet is one way around the problem. MPope wrote: > > What I have is a ...

Adjusting the Value of an asset acount
When I try to adjust the value of an asset I am finding that the increase or decreased value is applied to either my income or expenses. Example: a piece of property increased in it's value. I changed the value accordingly, but I am having problems with placing this transaction into a specific category. I want to create categories for asset appreciation and depreciation, but I am finding that I am asked to enter them into either an expense or income category. If there is an increase in value for that asset I am seeing it as income in my monthly reports such as "expense verse ...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

XL 2007 : Noty able to move data values in XY Scatter chart
Hi All, In Excel 2003 I can move data values (scattered points) in XY Scatter chart however this functionality is not working in Excel 2007. Any help? Tushar - Search Google for "excel 2007 deprecate chart" and one of the results is http://blogs.msdn.com/excel/archive/2006/08/28/724641.aspx which says "Direct Manipulation of Data Points on Charts In Previous versions of Excel, data points on a chart could be dragged, resulting in their source values being changed on the worksheet. This seldom used feature has been deprecated for Excel 2007." - Mike http://www...

Copy / Paste Merged Cells
Using Excel 2000, is is possible to copy and paste a range which has merged cells such that the pasted data displays only values. When using paste special and the "formula" option, merged cells will paste into the destination without problems but when using paste special with the "value" option, merged cells will not paste. Is there a way to do this easily and if so, what is it? Thx. Maybe you can copy from the formula bar and paste into the formula bar??? LPS wrote: > > Using Excel 2000, is is possible to copy and paste a range > which has merged cells...

Format Cell Problem #2
Hi, Sorry for all these dumb questions, but I never had any problems with Excel 97. This one is apparently just too complicated for me at my old age. Anyway, thanks for all the help; really appreciate it. So, using Excel 2007, and XP I select a cell that has a number in it with 5 decimal places. I go to Format Cells, and select Number, and tell it to go to 4 decimal places. It won't, and continues to show 5 decimal places. This is also true if I try it on a column of numbers. What am I not doing right ? (I am sure that what appears in the cell is truly a number, and not text.) T...

Cell fill color changes by itself
I have a user who has excel worksheets that they use fill colors. These are colors that they use all the time. Suddenly when she opens the sheets, the colors change by themselves even though no one has changed them. When another user opens the same document, they are the correct colors. It seems random as sometimes the document shows the correct fill colors. ...

Cell Phone/Laptop Client/CRM User Sync Architectural Question...
First let me apologize for this question. I am sure that there is something very simple and obvious that I am missing, but I am told that the only dumb question is the unasked one (said with tongue deeply in cheek!).... Current Situation: Small 25 person company Everyone has account on SBS server; i.e., active directory account All have CRM accounts (CRM V3 with all upgrades) Most have laptops with Outlook 2007/CRM client (with all upgrades) Most have Windows Mobile x (5 or 6) cell phones that sync with Exchange We Want: All of us want to be able to have contact info in our phones for each...

Finding max array value of variable cell range
Hi All, I'm currently stuck in trying to find out to get a max array value from a variable cell range, the data is divided in 5 collums, and the variable cell range should be dependent upon the first column. the maximum value should be available from column c to e. the first colums has blanks in between of variable spaces. I need to get the maximal amount of the array based upon the id code in first column (including the row of the id code, excluding the row of the second id code)... example: A B C D E 12341 data data data etc... 12341 42343 23432 etc.... does ...

Table row cell adjustment
I have a 3 column 30 row table in Publisher which will be divided into the equivalent of two tables horizontally, which will keep margins or other table settings the same. Is it possible in rows 1 and 16 to either eliminate the cells or reformat the rows by dragging the cells to the Left and Right margins? I would like to place a heading into these two rows which by using the present format would force carriage returns. Having 4 tables and struggling with margin alignment, there must be an easier method to what I am doing. In advance, Thanks, Frankd ...

#VALUE! On An Array Formula Referencing a Range Outside The Workbo
This formula works only when i have the other referenced workbook open simultaneously...when it is not open, i get the #VALUE! error.... Array Formula: {=SUM(IF($F$5:$F$92=$F280,O$5:O$92))+SUMIF('O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95,$F280,'O:\Gruss Value Fund\Capital\2005\[GV LP Capital 2005.XLS]SUMMARY'!M:M)} There are some functions that don't work with closed workbooks. You found one with =sumif(). But you could use a different formula to replace the =sumif() portion: =sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SU...

Installing SQL 2000 and SQL 2008 on the same server ?
Hello, I have a small question, is it possible to install a SQL server 2000 and a sql 2008 server on the same server with Windows 2008 server ? For sql2005 ans 2008 it's ok (http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/6cd82ab9-cc97-46b4-bd65-ce5ac23bd6e2/) but for sql 2000? Thanks, Didier Yes. "Didier" <Didier@discussions.microsoft.com> wrote in message news:4E0A0A4D-9FD6-4169-ABC9-506AD9B4BB1D@microsoft.com... > Hello, > > I have a small question, is it possible to install a SQL server 2000 and a > sql 20...

populate dataset fk value using xmlschema xsd
I am trying to use xml shema to load xml file to a dataset. Here are two tables: Table Item: Item_ID (key) Name Category_ID (foreign key from table Category) Table Category: Category_ID (key) Description I set relationship between 2 tables as commented above Here is a part of my xml file: <Category DisplayName="Cat 1"> <Item Name="Item 1" /> <Item Name="Item 2" /> <Item Name="Item 3" /> </Category> <Category DisplayName="Cat 2"> <Item Name="Item 4" /> <Item Name=&qu...