Transfer Spreadsheet

[Worker]

Urgent - Please Help!!! Transfer Spreadsheet Question

When doing a transfer spreadsheet macro in Access to transfer the results of a query into Excel, is there a way to specify a certain Excel tab to export to??? Also, to run the transfer spreadsheet, is there a way to run it several times with a prompt for criteria, and based on that criteria, put it into a certain tab? For example, run the transfer spreadsheet query... It prompts for a hub site. You can enter 01.... and it will export to a certain Excel file into the tab named 01. Then run it again and enter 02. That data is exported into a tab named 02. Any help is greatly appreciated as I am under a huge deadline and have been just copying and pasting for several hundred thousand records.... Thanks in advance!

 

[cfmiles]

I'm sure there is a better way to do this...

You could create multiple queries based on the different Hubs. Name the query what you want the tab on the spreadsheet to be called.

Then - create your macro with a transfer for each query with the same file name for each query. Each query will assign its own tab to the file.

I'm sure there is a simpler way, but for those of us who don't use a lot of code - this works.

HTH

 

[Worker]

Thanks - I will try this and let you know....

 

[Chris RR]

Dev Ashish has a great example of how to do a controlled transfer of data from Access to Excel on his web site. It's also really fast. Once you figure out the basic technique, you can use this to control quite a bit of what Excel does with the data.
The link is: http://www.mvps.org/access/modules/mdl0035.htm