Query to Excel – Different tabs based on field value

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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!

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.

Reply | Quote