Use MACRO to export queries to specific SHEETs in ONE Excel file?

Discussion in 'Access Macros' started by Hatman50, Jun 15, 2010.

  1. Hatman50

    Hatman50 Guest

    I'm sorry if this question seems familiar, but I've not found a thread
    with a solution.
    I want to export numerous querries to ONE Excel file having each
    querry put in/on it's own SHEET.
    Can this be done using an Access Macro?
    If not, can VBA do it?

    Thank you for any advice.
     
    Hatman50, Jun 15, 2010
    #1
    1. Advertisements

  2. Hatman50

    Jeff Boyce Guest

    How are you deciding which query goes on which sheet? You'll need to let
    Access know ...

    If you wish to have this process completely automatic, you'll probably find
    you have more control by using a VBA procedure.

    If you can specify the names of each of the spreadsheets (and each of the
    queries), you could do it via macros. ... but if you're using an earlier
    version of Access, you won't have a way to handle any errors in the macro.

    Good luck!

    Regards

    Jeff Boyce
    Microsoft Access MVP

    --
    Disclaimer: This author may have received products and services mentioned
    in this post. Mention and/or description of a product or service herein
    does not constitute endorsement thereof.

    Any code or pseudocode included in this post is offered "as is", with no
    guarantee as to suitability.

    You can thank the FTC of the USA for making this disclaimer
    possible/necessary.
     
    Jeff Boyce, Jun 15, 2010
    #2
    1. Advertisements

  3. Hatman50

    Hatman50 Guest

    We are using Access 2003. So the question is HOW do I do it via
    Macros? How/Where in the macro do I refer to the SHEET name?
    i.e.
    In the Macro there is an Action called OutputTo. And at the bottom of
    the screen are Action Arguments as follows.
    Object Type = Query
    Object Name= Name of Query
    Object Format= Microsoft Excel(*.xls)
    Output File= Path to Excel File
    Auto Start= No
    Template File= blank

    Unfortunately The user DOES NOT want to use/learn VBA.
    If it can be done via a Macro. Please advise How?
    Regards.
     
    Hatman50, Jun 15, 2010
    #3
  4. Hatman50

    Jeff Boyce Guest

    Hopefully one of the other newsgroup readers has specific experience with
    this.

    All my 'exports to Excel' have been via VBA, because of the control and
    error handling.

    Good luck!

    Regards

    Jeff Boyce
    Microsoft Access MVP

    --
    Disclaimer: This author may have received products and services mentioned
    in this post. Mention and/or description of a product or service herein
    does not constitute endorsement thereof.

    Any code or pseudocode included in this post is offered "as is", with no
    guarantee as to suitability.

    You can thank the FTC of the USA for making this disclaimer
    possible/necessary.

    We are using Access 2003. So the question is HOW do I do it via
    Macros? How/Where in the macro do I refer to the SHEET name?
    i.e.
    In the Macro there is an Action called OutputTo. And at the bottom of
    the screen are Action Arguments as follows.
    Object Type = Query
    Object Name= Name of Query
    Object Format= Microsoft Excel(*.xls)
    Output File= Path to Excel File
    Auto Start= No
    Template File= blank

    Unfortunately The user DOES NOT want to use/learn VBA.
    If it can be done via a Macro. Please advise How?
    Regards.
     
    Jeff Boyce, Jun 15, 2010
    #4
  5. Hatman50

    Hatman50 Guest

    Sorry Jeff, I must've misunderstood your previous post. I thought you
    were suggesting that "It could be done via Macros" depending on the
    Version of Access. That's why I was asking How?. My Bad!

    Anybody Else have any success with this?
     
    Hatman50, Jun 15, 2010
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.
Loading...