Results 1 to 4 of 4
  1. #1
    h20boynz is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    2

    Multiple excel worksheets based on query returns

    Hi there.



    I have a query that gets exported to Excel via:

    DoCmd.OutputTo acOutputQuery, "qrydetail", acFormatXLS, "qrydetail.xls", True

    This is great so far but I'd like to go a little further.
    The query returns transactions and is ordered by Customer Name, Inv date etc etc.
    What I'd like to do is split the query output and create a seperate worksheet for each Customer.
    Each customer may have multiple transactions and the query has a date range criteria.

    Can this be done with code and keep the query as is or do I need to take a new approach to the query?

    Thanks all!

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Yes there is a way to automate this but it would involve VBA skills. What is you skills level?

    David

  3. #3
    h20boynz is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    2
    Hi...

    I wouldn't say I'm an expert by any means but I have a few books and work things out generally...if someone can point me in the right direction with an example I can then manipulate to suit my purpose

    Thanks!

  4. #4
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    In laymans terms you need to create an new Excel Workbook then use a recordset that is grouped by customer

    You would start by enumerating throught the recordset by creating a new recordset that is based on the current customer.

    Use the CopyFromRecordset Rst method to copy to the current worksheet
    Then every time the recorset encounters a new customer you add an new worksheet to the workbook and make this the active workbook and repeat.

    Code:
     
    Create Excel Workbook Instance
     
    Rs1 = CurrentDb.OpenRecordset("QryGroupedBycustomerId")
     
    Do While Rs1.EOF
       Rs2 = CurrentDb.OpenRecordset("Select * From Query Where CustId=" & Rs1("CustId"))
         .WorkSheets.Add(Rs1("CustId"))
         .Range("A1").CopyFromRecordset Rs2
       Rs1.MoveNext
    Loop
     
    Close Recordsets
    Close and Quit Excel
    As you can see this is aircode and is untested and used for brevity.

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 AM
  2. export to multiple sheets based on change in sort
    By mws5872 in forum Programming
    Replies: 0
    Last Post: 06-30-2009, 07:55 AM
  3. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 PM
  4. Query returns null..based on two tables
    By shsh_shah in forum Queries
    Replies: 1
    Last Post: 03-08-2009, 01:45 PM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums