Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export Multiple Queries (Access 97 SR-2)

    Is there a way to export multiple queries to Excel without having to export one query at a time? I have 20 queries that I want to export into the same spreadsheet and it becomes tedious to go thru the motions over and over again.

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    Are they going to different sheets?
    If these queries output the same columns and they are going to end up in the same sheet you could use a UNION query to solve this.
    You could set them up in code and do a series of OpenQuery's from VBA to run these queries.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    Pat,
    No, each query is going to a different sheet in a single workbook.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    From Access 97 help :
    Caution With three exceptions, if you export to an existing spreadsheet file, Microsoft Access deletes and replaces the data in that spreadsheet. The exceptions occur when you export to a Microsoft Excel version 5.0, 7.0/95, or 8.0/97 workbook, where the data is copied to the next available worksheet.
    This would imply that if you use the TransferSpreadsheet to output to a particular Spreadsheet file, that it would use the next available in the Workbook.
    If this is the case you could use VBA to use the TransferSpreadsheet method.
    If not, then I'm out of ideas.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    I did a similar thing recently..

    I created a macro, and added the Transferspeadsheet function to each query i wanted to send to excel.
    Set the Transfertype to 'Export'
    The file name to be all the same

    Then assign the macro to a button. It saves out all the queries to one workbook, with each query on a different tab.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    Hi Steve,
    I figured out how to create a macro to export data. But I don't understand what you mean by "adding the Transferspeadsheet function to each query i wanted to send to excel". I put the 1st Query name in the Table Name field and it exports it nicely. But how do I get it to do the rest of the queries? Is there a way to attach a macro to a Query.
    As you can tell, this is new to me, so thanks for your patience.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    Why don't you convert the macro to VBA code and you will probably see the TransferSpreadsheet function he describes.
    After you have done this you can duplicate the TransferSpreadsheet line of code 20 times and just change the queryname in each one of them.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export Multiple Queries (Access 97 SR-2)

    Ack... very sorry Sherry, i DID end up doing it in VBA as patt suggests,

    The syntax i think is:-

    DoCmd.TransferSpreadsheet acExport, 3, "Your_Query_Here","C:My DocumentsMyFolderMyFilename", True

    I'm by no means any good at this myself. but i think thats close to the syntax. you can look it up under help

    So make a new button, on the 'onclick' event, click the 3 little dots, then add that line in. If you want more Queries in the same workbook, just add the same line again, and change the Query name. This puts all the queries into the same workbook.

Posting Permissions

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