Feeding Worksheet Data Into a Query

If I have 4 values that reside in a worksheet in cells F1:F4,

how do I take these values  and specify these as criteria in my where
clause:

WHERE ID IN (F1:F4)

I need a comma separated list of IDs so that the T-SQL statement can
be executed correctly,

WHERE ID IN (F1,F2,F3,F4)
0
9/24/2008 9:55:22 PM
excel 39879 articles. 2 followers. Follow

6 Replies
567 Views

Similar Articles

[PageSpeed] 31

There's always more than 1 way to skin a cat ...

One way to solve your problem would be to dim 5 variables.
Example:
(I am going to assume your values are string values.)

Dim strA As String, strB As String, strC As String, D As String
Dim strWhereSQL As String

strA = Range("F1").Value
strB = Range("F2").Value
strC = Range("F3").Value
strD = Range("F4").Value

strWhereSQL = "Where ID in ('" & strA & "','" & strB & _
  "','" & strC & "','" & strD & "')"

I hope that is of some help to you.

Good Luck

"Tangier" wrote:

> If I have 4 values that reside in a worksheet in cells F1:F4,
> 
> how do I take these values  and specify these as criteria in my where
> clause:
> 
> WHERE ID IN (F1:F4)
> 
> I need a comma separated list of IDs so that the T-SQL statement can
> be executed correctly,
> 
> WHERE ID IN (F1,F2,F3,F4)
> 
0
NDPard (28)
9/25/2008 8:51:02 PM
On Sep 25, 3:51=A0pm, ND Pard <NDP...@discussions.microsoft.com> wrote:
> There's always more than 1 way to skin a cat ...
>
> One way to solve your problem would be to dim 5 variables.
> Example:
> (I am going to assume your values are string values.)
>
> Dim strA As String, strB As String, strC As String, D As String
> Dim strWhereSQL As String
>
> strA =3D Range("F1").Value
> strB =3D Range("F2").Value
> strC =3D Range("F3").Value
> strD =3D Range("F4").Value
>
> strWhereSQL =3D "Where ID in ('" & strA & "','" & strB & _
> =A0 "','" & strC & "','" & strD & "')"
>
> I hope that is of some help to you.
>
> Good Luck
>
>
>
> "Tangier" wrote:
> > If I have 4 values that reside in a worksheet in cells F1:F4,
>
> > how do I take these values =A0and specify these as criteria in my where
> > clause:
>
> > WHERE ID IN (F1:F4)
>
> > I need a comma separated list of IDs so that the T-SQL statement can
> > be executed correctly,
>
> > WHERE ID IN (F1,F2,F3,F4)- Hide quoted text -
>
> - Show quoted text -

THANK YOU! But now the question is, if I have cells to get data from
F2:F50, how do I generate a comma separated list to put into the
WHERE CHildID in ( comma separated list here )
0
9/25/2008 10:09:05 PM
Well why didn't you say so:

Sub Try_This()
'Assumes your list starts on F2 and that they are Sting values
'(You never did let us know if you're working with text or numbers)

dim strWhereSQL as string
  Range("F2").Select
Do while ActiveCell.Value > ""
  If strWhereSQL > "" Then
    strWhereSQL = strWhereSQL & ",'" & ActiveCell.Value & "'"
  Else
      strWhereSQL = "Where ID In '" & ActiveCell.Value & "'"
  End If
  ActiveCell.Offset(1).Select
Loop
  strWhereSQL = strWhereSQL & ")"
End Sub

Good Luck.
"Tangier" wrote:

> On Sep 25, 3:51 pm, ND Pard <NDP...@discussions.microsoft.com> wrote:
> > There's always more than 1 way to skin a cat ...
> >
> > One way to solve your problem would be to dim 5 variables.
> > Example:
> > (I am going to assume your values are string values.)
> >
> > Dim strA As String, strB As String, strC As String, D As String
> > Dim strWhereSQL As String
> >
> > strA = Range("F1").Value
> > strB = Range("F2").Value
> > strC = Range("F3").Value
> > strD = Range("F4").Value
> >
> > strWhereSQL = "Where ID in ('" & strA & "','" & strB & _
> >   "','" & strC & "','" & strD & "')"
> >
> > I hope that is of some help to you.
> >
> > Good Luck
> >
> >
> >
> > "Tangier" wrote:
> > > If I have 4 values that reside in a worksheet in cells F1:F4,
> >
> > > how do I take these values  and specify these as criteria in my where
> > > clause:
> >
> > > WHERE ID IN (F1:F4)
> >
> > > I need a comma separated list of IDs so that the T-SQL statement can
> > > be executed correctly,
> >
> > > WHERE ID IN (F1,F2,F3,F4)- Hide quoted text -
> >
> > - Show quoted text -
> 
> THANK YOU! But now the question is, if I have cells to get data from
> F2:F50, how do I generate a comma separated list to put into the
> WHERE CHildID in ( comma separated list here )
> 
0
NDPard (28)
9/26/2008 4:46:06 PM
On Sep 26, 11:46=A0am, ND Pard <NDP...@discussions.microsoft.com> wrote:
> Well why didn't you say so:
>
> Sub Try_This()
> 'Assumes your list starts on F2 and that they are Sting values
> '(You never did let us know if you're working with text or numbers)
>
> dim strWhereSQL as string
> =A0 Range("F2").Select
> Do while ActiveCell.Value > ""
> =A0 If strWhereSQL > "" Then
> =A0 =A0 strWhereSQL =3D strWhereSQL & ",'" & ActiveCell.Value & "'"
> =A0 Else
> =A0 =A0 =A0 strWhereSQL =3D "Where ID In '" & ActiveCell.Value & "'"
> =A0 End If
> =A0 ActiveCell.Offset(1).Select
> Loop
> =A0 strWhereSQL =3D strWhereSQL & ")"
> End Sub
>
> Good Luck.
>
>
>
> "Tangier" wrote:
> > On Sep 25, 3:51 pm, ND Pard <NDP...@discussions.microsoft.com> wrote:
> > > There's always more than 1 way to skin a cat ...
>
> > > One way to solve your problem would be to dim 5 variables.
> > > Example:
> > > (I am going to assume your values are string values.)
>
> > > Dim strA As String, strB As String, strC As String, D As String
> > > Dim strWhereSQL As String
>
> > > strA =3D Range("F1").Value
> > > strB =3D Range("F2").Value
> > > strC =3D Range("F3").Value
> > > strD =3D Range("F4").Value
>
> > > strWhereSQL =3D "Where ID in ('" & strA & "','" & strB & _
> > > =A0 "','" & strC & "','" & strD & "')"
>
> > > I hope that is of some help to you.
>
> > > Good Luck
>
> > > "Tangier" wrote:
> > > > If I have 4 values that reside in a worksheet in cells F1:F4,
>
> > > > how do I take these values =A0and specify these as criteria in my w=
here
> > > > clause:
>
> > > > WHERE ID IN (F1:F4)
>
> > > > I need a comma separated list of IDs so that the T-SQL statement ca=
n
> > > > be executed correctly,
>
> > > > WHERE ID IN (F1,F2,F3,F4)- Hide quoted text -
>
> > > - Show quoted text -
>
> > THANK YOU! But now the question is, if I have cells to get data from
> > F2:F50, how do I generate a comma separated list to put into the
> > WHERE CHildID in ( comma separated list here )- Hide quoted text -
>
> - Show quoted text -

thanx!!!!!!!!!!!!!!!1
0
9/29/2008 6:24:22 PM
On Sep 26, 11:46=A0am, ND Pard <NDP...@discussions.microsoft.com> wrote:
> Well why didn't you say so:
>
> Sub Try_This()
> 'Assumes your list starts on F2 and that they are Sting values
> '(You never did let us know if you're working with text or numbers)
>
> dim strWhereSQL as string
> =A0 Range("F2").Select
> Do while ActiveCell.Value > ""
> =A0 If strWhereSQL > "" Then
> =A0 =A0 strWhereSQL =3D strWhereSQL & ",'" & ActiveCell.Value & "'"
> =A0 Else
> =A0 =A0 =A0 strWhereSQL =3D "Where ID In '" & ActiveCell.Value & "'"
> =A0 End If
> =A0 ActiveCell.Offset(1).Select
> Loop
> =A0 strWhereSQL =3D strWhereSQL & ")"
> End Sub
>
> Good Luck.
>
>
>
> "Tangier" wrote:
> > On Sep 25, 3:51 pm, ND Pard <NDP...@discussions.microsoft.com> wrote:
> > > There's always more than 1 way to skin a cat ...
>
> > > One way to solve your problem would be to dim 5 variables.
> > > Example:
> > > (I am going to assume your values are string values.)
>
> > > Dim strA As String, strB As String, strC As String, D As String
> > > Dim strWhereSQL As String
>
> > > strA =3D Range("F1").Value
> > > strB =3D Range("F2").Value
> > > strC =3D Range("F3").Value
> > > strD =3D Range("F4").Value
>
> > > strWhereSQL =3D "Where ID in ('" & strA & "','" & strB & _
> > > =A0 "','" & strC & "','" & strD & "')"
>
> > > I hope that is of some help to you.
>
> > > Good Luck
>
> > > "Tangier" wrote:
> > > > If I have 4 values that reside in a worksheet in cells F1:F4,
>
> > > > how do I take these values =A0and specify these as criteria in my w=
here
> > > > clause:
>
> > > > WHERE ID IN (F1:F4)
>
> > > > I need a comma separated list of IDs so that the T-SQL statement ca=
n
> > > > be executed correctly,
>
> > > > WHERE ID IN (F1,F2,F3,F4)- Hide quoted text -
>
> > > - Show quoted text -
>
> > THANK YOU! But now the question is, if I have cells to get data from
> > F2:F50, how do I generate a comma separated list to put into the
> > WHERE CHildID in ( comma separated list here )- Hide quoted text -
>
> - Show quoted text -

ok sorry for all the questions, but how do I call this subroutine
within another subroutine which outputs the query?

PS, I am working with Child IDs, so in this case, numeric data.
0
9/29/2008 7:46:31 PM
To call the Try_This() subprocedure from a different subprocedure, you enter 
the following line in the different subprocedure:

     Call Try_This

Good Luck.

Sub Try_This()
 'Assumes your list starts on F2 and that they are Numeric values

dim strWhereSQL as string
   Range("F2").Select
 Do while ActiveCell.Value > ""
   If strWhereSQL > "" Then
     strWhereSQL = strWhereSQL & "," & ActiveCell.Value
   Else
     strWhereSQL = "Where ID In (" & ActiveCell.Value
  End If
  ActiveCell.Offset(1).Select
  Loop
  strWhereSQL = strWhereSQL & ")"
 End Sub
0
NDPard (28)
9/30/2008 3:39:03 PM
Reply:

Similar Artilces:

Access Query result different when opened in automation
I have a query that pulls information from three different linked tables. (this might very well be the source of my problems but I dont understand why) When I run the query manually in Access the result set is as I expect, but when I use that query in a function in Access VBA, the where clause is partially ignored. The part that is ignored is here: (ServerDetails.SERVER_TYPE) Not Like "*Domain Controller*") The full filter is here: WHERE (((ServerDetails.ENVIRONMENT)="HOME") AND ((ServerDetails.SERVER_TYPE) Not Like "*Domain Controller*") AND (((ServerDet...

RSS Feed List only displays three feeds
I'm using Outlook 2007 for a month now and imported a list of RSS/ATOM feeds. I'm still using my old feed reader in parallel and noticed that since about a week most of the feeds were not updated in Outlook. I opened the send/receive settings to see what's included when doing a send/receive and found that only three feeds were displayed. They worked but all of the other did not. I then created a new group named "RSS feeds" and included the three listed feeds. After several send/receive on this group, I finally received messages from other blogs as well. The settin...

Printing hard copy of formulas in a worksheet.
I have a worksheet with several formulas. How do I print a hard copy so I can reference it? Thanks. Click Tools ... Options ... View tab then tick Formulas Don't forget to untick later on after you have printed. GB "Marie" <anonymous@discussions.microsoft.com> wrote in message news:02ec01c394ee$f4177090$a401280a@phx.gbl... > I have a worksheet with several formulas. How do I print > a hard copy so I can reference it? > Thanks. Hit CTRL + ` (this is the key right below escape) and the formulas will show up. Then you can print the page. >-----Original...

Viewing different worksheets at the same time?
I was just wondering if it's at all possible to view different worksheets from one workbook at the same time. I got to thinking about this because you can split the view so that you can view different parts of one worksheet at the same time (dragging that little bar at the end of the scroll-bars). I'm currently having to create a load of links between two sheets withing a workbook and it would be so much easier if you could have both sheets on display at once. So, is this possible? Cheers, Rob -- Rob_T ------------------------------------------------------------------------ Rob_...

Consolidate data in a table
Using Excel 97. I have a table made from a csv file from eBay which lists my auction items and various fees. Each fee type is in a different row and I'd like to place them all on one row with the appropriate item no. and description (so I can total all fees for a specific item). I like to do this without cutting and pasting. Looks like this: 1234567890 Whatchamacallit Insertion Fee $0.35 1234567890 Whatchamacallit Picture Fee $0.15 1234567890 Whatchamacallit Final Value Fee $0.67 Would like this: 1234567890 Whatchamacallit $0.35 $0.15 $0.67 $1.17 with appropriate column headings, o...

How do I require a form field have data entered before next field
I have a word document with form fields and I would like to make some of the fields to require an answer befoe the next field is filled in. Marsha K wrote: > I have a word document with form fields and I would like to make some > of the fields to require an answer befoe the next field is filled in. See http://www.gmayor.com/formfieldmacros.htm. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. ...

record data
Hello I've made a control of stock wich i would like to complete, but i don't know how. I have stock organized like this: In 1 sheet it is like this: A B C D E(invisible) Design. Qty In Out Stock 1 Pen E1+C1-D1 10 15 50 2 Rubber E2+C2-D2 0 2 20 3 Whathever E3+C3-D3 1 0 10 What i want in other sheet is to know the quantity i took or put and the day something like this: A B C D ...

Emailing Excel Data
When I send a spreadsheet embedded in an email via Outlook 2003 (not as an attachment)I find that if I have a cell, or a series of cells selected within the email message, only the data from the selected area is actually emailed yet the entire sheet appears in the email. Does anyone know why? is there a setting that will prevent this from happening?? ...

How to change query empty field to "0"
Hello, At the beginning forgive me at the bad English. I am a beginner in Access. I'm working on a database that deals with records warehouse. To simplify the. I have one query, for example query A, a query where the calculation of 50 items. This query A has sum by the code of materials. The second query is composed of one few table and query A. But in this table is 60 items with the calculations of other tables. The column, which is associated with a query (sum: SumOfqueryA) has a problem. 50 fields have result, and 10 field is blank because I have 50 results in QueryA. How...

How can you attach an e-mail into an excel worksheet?
I want to attach an e-mail into an excel worksheet. The formula I have seen is =EMBED("Package","") But not sure how I can do it. Any ideas? Go to Insert - Object, From file. Navigate to the email you want to embed. Note the options who can choose to change icon or how it is links. You can then double-click on the object to access the email. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "humdul" wrote: > I want to attach an e-mail into an excel worksheet. The formula I have seen > is =EMBED(&qu...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

printing worksheet on 3x5 card
I would like to know if it is possible to print a worksheet the size of a 3x5 card, so I can use it with my flashcards for school? Printerchallenged, if you printer supports this size paper then, file page setup, set paper size to 3x5, excel gets the paper size from your printer settings -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "printerchallenged" <printerchallenged@discussions.microsoft.com> wrote in message...

Adding a Date data-type field with a Make-Table query
I am using a make-table query to make a table. Some of the fields are being populated with this query, however I need to add several more fields with null values so that they can be updated by subsequent update queries. The default field-type that is created if I just put in a field name is Text. How can I create a Date field-type field from a make-table query which has a null value? Well, you can create a DateTime field by using something like: DateField: CDate(0) But the field won't be empty, it will contain 0, or, stated as a date value, Dec 30, 1899 12:00 am. I don't kn...

link several worksheets
Is there any way to achieve following: WORKSHEET 1 (contains downtime in hours, max value 24.00) link1 link2 link3 date1 2.50 0.00 1.50 next date 0.00 0.00 0.00 WORKSHEET 2 (computes availability in percentage based on value in worksheet1) link1 link2 link3 date1 89.58 100.00 93.75 next date 100.00 100.00 100.00 Thus every day, data is fed in WORKSHEET1, and thu...

Populate fields in forms with data from CRM 3.0?
Hi all. Is it possible to gather data from CRM 3.0 ("Accounts" in our case) and use it to populate a drop down field in a SharePoint form? We're running MOSS 2007 Enterprise so BDC is an option if need be. I haven't checked out the downloadable web parts for CRM connectivity yet but as far as I can tell they're not for this kind of functionality. Hi Martin, Create a webservice on top of Microsoft CRM. And use a JavaScript to get the values, and populate the drop down lists. A point to start with is the Microsoft CRM 3.0 SDK (Chapter Client Programming Guide, Clie...

News Feed Folders
I have just purchased office 2007 and use Outlook for my mail. I have a problem with my News Feed Folders as they seem to breed like rabbits and are now up to 20 for the third time. Is there any way to get rid of the NRF completely as I am fed up of them cluttering up my folder section. They are always empty so what is the point? Help pls pls pls :-) Norm Taylor wrote: > I have just purchased office 2007 and use Outlook for my mail. I have a > problem with my News Feed Folders as they seem to breed like rabbits and are > now up to 20 for the third time. Is there any...

Import data to HQ database?
Is there any Add-ons tools to create one major HQ database from different database of different stores? Any help would be very appreciated. QN There are no tools like that actually, but what you can do is use the Store Operations Import Utility (you can get this from Customersource or Partnersource), and use it to import to a Store Operations database and use the database as a template database when creating the HQ db. :) -- "Phu Nguyen" wrote: > Is there any Add-ons tools to create one major HQ database from different > database of different stores? Any help wo...

Outlook 2003 data recovery other than .pst/.ost
HI Guys, Recently my old PC got crashed & now I am trying to transfer all the relevant data to new m/c but I am not able to recover emails from the outlook 2003. As I dont see any .pst/.ost files on that m/c. Is there any other format those emails would be stored ? How do I restore it on my new pc. Thanks for your help Venkat In news:1130383029.526144.269300@o13g2000cwo.googlegroups.com, venkat <venkatakrishnabr@gmail.com> typed: > HI Guys, > > Recently my old PC got crashed & now I am trying to transfer all the > relevant data to new m/c but I am not able to re...

Downloading data into Excel
I am interested in purchasing business leads from several different companies. These leads would contain name, address, phone, etc. The autoresponder system I would like to use accepts data from Excel. I would like to know what data format leads can I use that could be downloaded into Excel. Also, since I am familiar with computers and Excel but not an expert, I would like to get information on EXACTLY how to download the data from the acceptable formats into Excel. Any help you folks can provide would be greatly appreciated. Thanks in advance! healthplanman@aol.com ...

Data moving 2
I have following code: Sub climate() Dim x As Long, y As Long x = Range("X" & Rows.Count).End(xlUp).Row y = InputBox("enter the row number to paste") Range("A2:BK2" & x).Copy Range("A" & y).PasteSpecial MsgBox "Updated" End Sub I want to cut data of range(A2:BK2 to end row related) and then paste to row number which determine in Box. when i run above code on some of sheets, error message appear(Run Time error 1004). please note that, this code with cut command not run, only with copy command. any help will be gre...

auto email worksheet
Is there a macro I can write that would email my worksheet 6 days a week (no Sunday) Cheers!!! Use Windows Scheduled Tasks to run your macro at specific times/days. The basics of the macro would be something like this.... '/========================================/ ' Sub Purpose: example of how to send a file attachment ' Requires reference to Microsoft Outlook '/========================================/ ' Public Sub SendTheEmail() Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Dim objAttachments As Outlook.Attach...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

use data on excel sheet to input data on web page
I don't know if you can do this but here's the question. I would like to take some data on a spread sheet (example: division # and item code) and have this placed on a web page in the correct spots (division is a drop down box) so the site will find the correct information and then have excel import the found data onto a separate work sheet. Any insight into this would be very helpful. ...

Importing Data from Access into Excel
I have a spreadsheet that has mutiple tabs that I want to import data from an Access table into a specific cell everytime I refresh. That's not a lot of information to go on, but....in general, you could start with: 1)Select the cell where the data pull will begin to store the Access data 2)Data>Import External Data>New database Query 3)Select the MS Access data source 4)Select the table(s) you need to use and progress through the MS Query screens. 5)At the end, you can either select to edit the query or just send the data back to Excel. 6)Repeat for each sheet that is to re...

Summing a years worth of data
Hi If data is entered every month in one tab. How would i then sum thi data by year into another tab? if necessary i dont want to kee changing the formula from year to year eg Col A Col B Jan 04 20 Feb 04 25 Then another tab that sums data by year eg col A Col -- Message posted from http://www.ExcelForum.com Hi if column A on your second sheet contains the year try the following in B1 =SUMPRODUCT(--(YEAR('sheet1'!A1:A1000)=A1),'sheet1'!B1:B1000) -- Regards Frank Kabel Frankfurt, Germany "david72 >" <<david72.149hr...