Results 1 to 6 of 6

Thread: MSQuery / Excel

  1. #1
    eslayter
    Guest

    MSQuery / Excel

    In Excel is there a way to pass criteria TO MSQuery from the spreadsheet? And if the answer is 'Yes', is there a way to do it without VB coding (which I know nothing about)?

    Thanks for your help,
    Erik

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: MSQuery / Excel

    Yes there is
    Assuming that you are using Excel2K...(is possible in Excel 97 and previous too)
    Use the Data, Get External Data, New Database Query this will launch a wizard that will help you build your MSQuery.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery / Excel

    Further to Catharine's response, when you have finished in query and returned the data to Excel, the query definition will be saved as part of the range into which the data is inserted.

    You can then go back (via Data>Get External Data>Edit Query) and edit the query (ie MSquery will be started with the previous tables and criteria already sitting there looking at you).

    Jon

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery / Excel

    [img]/w3timages/icons/wink.gif[/img]If what you want is to pass parameter values, then use the parameterized version of MSQuery. For example, if you wanted data to come back that is more recent than a date that you wish to specify each time you run the query, you can have MSQuery prompt the user for that value or you can imbed it in your worksheet and tell MSQuery where it is.

  5. #5
    eslayter
    Guest

    Re: MSQuery / Excel

    So what epic60sman is getting at is where I was going with my questions...I'm guessing that the standard MSQuery that comes with Office2000 is not the parameterized version. Or if it is the standard version, how do I get MSQuery to prompt the user for a value or imbed it in the worksheet?

    If it's not the standard version, is the parameterized version one that is available for sale through the traditional retail market?

    Thanks,
    Erik

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSQuery / Excel

    Sorry. I did not mean to imply that there is a parameterized version with a capital V. You just have to read about how to do a paramaeter query. Its much similar to doing a query in the Design View in Access. With Excel 97, you can not refine an existing query and add aparamter, you have to do it as a new query. So, open a new query and pick your source and specify your tables and their linkage. Then before you open the SQL window, On the External Data toolbar, click Query Parameters Icon (it will seem to lighten up and be depressed. Then click the parameter you want to change in the Name Box. In the Prompt for value using the following string box, type the text you want to use for the prompt inside square brackets, and then click OK. Then open SQL and write your query (when you do you will see the variable you parametized, say X, ... and X =?). When you Refreesh the query from your spreadsheet, you will be prompted (witht the note that you typed into the square brackets). When you get the prompt, you will be able to click on a cell in your spreadsheet into which you entered the value you want for this run.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •