Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Anyone have anything that will take an Access query and put it to Excel, but place data on different tabs based on a field?

    For example, I have a bunch of data that has a field named BU. That BU field can have any of 15 different values. I need to put that data to Excel and need a different tab for each BU.

    I am NOT using a template Excel sheet, either...it would be built from scratch.

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have several options:

    - Write code in Access that modifies the SQL of the query to return records for one value of BU only, exports it, then repeats these steps for each value of BU.

    - Export the query (with all values of BU) to Excel, then run a macro in Excel to create a worksheet for each value of BU. If you want to use this method, I can post a link to relevant topics in the Excel forum.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    One other option would be to use Automation and actually put the values on the correct worksheet as you process the records. That does of course mean that you need to understand the Excel object model, as well how to manipulate Excel using Automation.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Abraxus' post='796934' date='09-Oct-2009 01:06']Anyone have anything that will take an Access query and put it to Excel, but place data on different tabs based on a field?

    For example, I have a bunch of data that has a field named BU. That BU field can have any of 15 different values. I need to put that data to Excel and need a different tab for each BU.

    I am NOT using a template Excel sheet, either...it would be built from scratch.

    Thanks![/quote]
    Another option is to use a different named query and use DoCmd.TransferSpreadsheet using the same filename.

    Name the queries after the differing BU's you have.

Posting Permissions

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