Send query separated on tabs?

[fredalina]

i have a query that lists data for all employees lumped together. The query sends out in .xls format. Management would like to separate out the data for each employee on a separate tab. Can this be done systematically through Access VBA, or does it require exporting to Excel and running Excel VBA? Eventually i would like to have this run through a batch feed system each week so i don't have to personally run it, once the bugs are worked out, so while i can certainly do the report manually in Excel, i'd rather not.

 

[namliam]

You can export multiple queries into seperate tabs of the same excelsheet just read the TransferSpreadsheet help in Access on how to do it.

 

[fredalina]

i will do that. Thanks!

 

[fredalina]

Another Question

This works perfectly, however the column widths on the tabs do not seem to correlate to the column widths in the query results (or any other logical sequence i can find). Is there a way to format this or dictate column widths (or anything that would prevent me having to send the spreadsheet out manually after formatting) from within Access?

Thanks!

 

[namliam]

You can use office automation to open the excel file(s) and adjust to columns/formating whatever to whatever you like.

Have a search at "Excel.Application" and you should find atleast some samples.

 

[fredalina]

Thanks. Is there no way to do it within Access? (That is required for it to put the reports into production).

 

[namliam]

Yes it is possible to do it from Access... but I refer you back to my previous post...

Have a search at "Excel.Application" and you should find atleast some samples.