Results 1 to 10 of 10
  1. #1
    DaveShmave
    Guest

    Export table(s) to Excel (Access 2000 (SR-2))

    I did a couple of searches but was unable to get my question answered. Maybe someone can help me with this.

    I want to export 3 tables to Excel but have the first table as "Sheet1", the second table as "Sheet2" and the third as "Sheet3" within the same spreadsheet. Can this be done and if so, how exactly.

    TIA

    Dave <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    As a test I just exported a table to Excel which saves it a .xls file naturally using the table name as the file name. Then I exported another table to the very same .xls file just created and the second table showed up as the next sheet. The sheets were named by their original table names. This seems to be a default behavior.

    Hope this helps.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Dave,

    In the Transferspreadsheet method, the last argument can be used for a range where the data has to come. Maybe you can use this. I never used this, so look in the help file for the method and the samples
    Francois

  4. #4
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Thanks for the response Preston, however, I'm trying to automate the process so I can click a button, queries are run then 3 tables are exported to the same spreadsheet.

    Any other ideas would be greatly appreciated.

    Thanks again.

    Dave <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  5. #5
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Thanks Francois, I'll look into that. I'm not sure that'll do the trick but I'll give it a shot.

    Dave <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Oops! I re-tried your suggestion(s) and it works great.

    Thanks for the help Preston and Francois.

    Dave <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Thanks again for the help.

    I have one other question that I'm struggling with. Because I will run these queries over and over again, I want to be able to start with a fresh spreadsheet (the TransferSpreadsheet Method doesn't replace the existing spreadsheet) and then place the newest data in the first three sheets.

    Does that make any sense? How do I delete a spreadsheet from Access?

    TIA

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

  8. #8
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Okay I think I finally have it figured out. I used the OutputTo Method to overwrite the older spreadsheet. Here's how:


    DoCmd.OutputTo acQuery, "Sheet1", "MicrosoftExcel(*.xls)", "C:MyDocumentsMySpreadsheet.xls", False, ""

    DoCmd.TransferSpreadsheet acExport, 8, "Sheet2", "C:MyDocumentsMySpreadsheet.xls", False, ""

    DoCmd.TransferSpreadsheet acExport, 8, "Sheet3", "C:MyDocumentsMySpreadsheet.xls", False, ""


    Dave <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Dave,

    You can also use the Kill statement.
    Kill "c:mydirectorymyFile.xls"
    Francois

  10. #10
    DaveShmave
    Guest

    Re: Export table(s) to Excel (Access 2000 (SR-2))

    Yeah I tried that but was having some difficulties using the Kill statement in conjunction with the TransferSpreadsheet code.

    Thanks for the reply Francois.

    Dave

Posting Permissions

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