Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looping through queries (Access 97 SR 2)

    I have created a large group of queries that I would like ouput to Excel. I often need to add or delete. I've created a macro through the Macro tab to output them but I would like to use VBA instead. I would like to just loop through, running each query and sending the results to an Excel file with the file name that of the query. Any help would be greatly appreciated.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Looping through queries (Access 97 SR 2)

    Lookup "TransferSpreadsheet" in Help, and I think you will find what you need for correctly writing the code to export a query to Excel.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Looping through queries (Access 97 SR 2)

    In the database window, Tab Macros, right-click on a macro. Click Save As/ Export. Check Save as Visual Basic Module and press OK. Check or uncheck Add error... and Include Macro Comments and click Convert. In the Module tab of the database windows you have now a module named Converted Macro - Name Of the Macro. This module will act the same as the macro. Start with this to learn VBA code
    Francois

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping through queries (Access 97 SR 2)

    Thanks for the reply. I had the code to export ,what I can't figure out is how to loop through each query. Something like
    For each query in database
    docmd...

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Looping through queries (Access 97 SR 2)

    Hi,
    You need to look at the querydefs collection. Something like:
    for each qdfTemp in currentdb.querydefs
    ....
    next qdfTemp
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping through queries (Access 97 SR 2)

    THIS IS PERFECT! THANK YOU!

Posting Permissions

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