Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output to a Sheet (Access 2002)

    Hi All!

    I was wondering if there was any way to output to a spreadsheet in an Excel workbook?

    For example, I'm using this code:

    DoCmd.OutputTo acOutputQuery, "qryBanks", acFormatXLS, "Banks.xls", False

    But what I'd actually like to do is export to one workbook called Institutions.xls and have that query export to sheet Banks, another export to sheet Savings & Loans, etc.

    The effect that I'm trying to get is the same as clicking File-->Export and choosing the same excel file name for each query that I'm exporting. When you do it manually, it saves each query in a different sheet with the same of the query as the tab.

    TIA!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output to a Sheet (Access 2002)

    Cecilia,

    Use DoCmd.TransferSpreadsheet instead of DoCmd.OutputTo. This will create tabs named after the table or query.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryBanks", "Banks.xls"

    If you want to export field names to the first row as column headings, add ", True" (without the quotes; the default value is False)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to a Sheet (Access 2002)

    Thanks, Hans...

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Sometimes I can be so silly.

    Interesting thing, though, is that it saves the spreadsheet to the My Documents folder...Which isn't even my default database location! I kind of expected it to be in the same folder as my database. Kind of makes it difficult to have the file automatically open.

    BTW, is it just my imagination, or is Help in XP not nearly as good as it was in 97? I seem to have a much harder time these days looking up things like this.

    Thanks again!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output to a Sheet (Access 2002)

    You should be able to specify a path for the workbook: "serversharefolderBanks.xls" instead of just "Banks.xls". If you want to use the folder in which the database resides, without specifying it explicitly, you can extract it from CurrentDb.Name.

    And yes, the HTML help in Office 2000 and up is a nuisance. It has become much more difficult to find things than in older versions; most of the help is still there, but you need patience and luck to unearth it.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to a Sheet (Access 2002)

    Thanks again, Hans,

    I was just hoping that with this version of Access that things would have gotten easier. Just a simple thing like finding a path to save a spreadsheet is such a pain!

    Even searching for simple code to open an excel spreadsheet is impossible! I can type in Excel and get only four results, none of which has to do with Excel. Excel Automation results in some vague references to access automation. Shell is useless because the server path is way too convoluted, and I can't find my old familiar shell help, either. I know it's in there--How do I unlock this completely useless help system??? Does MS expect me to memorize ALL bits of code???

    I wish we could go back to 97.... <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>

    Okay, sorry for complaining....I'll be nice now ;-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output to a Sheet (Access 2002)

    Searching the help index often does not turn up what you want. Two other ways to get help in the VB Editor:

    1. Type the name of an object, method, property, function or instruction in the Immediate window and press F1.
    2. Activate the Object Browser and type a word in the Search box, then press Enter. If you see something you would like to know more about, click on it and press F1.

    Also see <post#=300625>post 300625</post#>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to a Sheet (Access 2002)

    Hi again, Hans,

    #1--Thanks for the tips on using help.

    #2--I actually remembered at the last minute that I've done this before, and once I remembered, it was so simple, I can't believe I missed it!

    Unfortunately, I don't program *all* the time, and in between I kinda forget stuff. Urrrgh, that gets me all stressed out, when I know there's a simple solution, but I can't for the life of me find it.

    But all's (mostly) good with the world now, and definitely many thanks to you!!!

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Output to a Sheet (Access 2002)

    To clarify for others reading this thread, the Help for Functions and all VBA usage is found in the VBA Help, which isn't pulled up when you open Help from the Access menu. You need to get into the VBA Editor in order to get that Help - and for whatever reason, all nearly al functions Help got stuck in VBA rather than in Access Help.
    Wendell

Posting Permissions

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