Exporting Access queries to Excel

Hi

Is there any way to export the results of two Crosstab queries into a single Excel Worksheet, specifying destinations for each fields result using VBA?

I am looking at placing the code in a buttons On_Click event so that the user doesn't have to manually copy the results into the Spreadsheet, therefore eliminating the risk of human error when sending out figures to management.

I have attached a copy of the Database and the Template Spreadsheet. The queries in the Database to be exported are "7e Completion by Manager Append_Crosstab" & "8f Progress Against Test Plan Append_Crosstab" and a quick look at the Spreadsheet should show where I want the results to go.

Thanks in advance!!!

It is possible but not simple....
You will have to export the crosstabs into seperate sheets first (docmd.transfersheet)
Then once that is done, open the file in code and manipulate it to do what you want it too.
Check out the "Excel.Application" object to get an idea and get started. Let me/us know if/when you run into trouble.

I've decided to go another route as I decided it would be very messy as there could be a different number of columns and rows in my Crosstab queries each time they are run, therefore specifying cell locations for results would be tricky.

Rather than exporting the Crosstab queries, I have decided to export the source tables, then use array (Ctrl-Shift-Enter) formulae in Excel to summarise in a similar way to a Pivot Table, which will bypass any errors when there are a different number of columns or rows.

Therefore my command button should now run a macro which will run all delete and append queries to update the values in my source tables and then export the tables to the Template Spreadsheet which will automatically update. Hopefully this should be simple...


Thanks for your help though!!!

Well apparently TransferSpreadsheet doesn't like a named range when exporting, only importing so had to have a rethink.

I ended up exporting the 2 tables as new worksheets in the workbook, then used a simple macro in Excel to copy and paste special values the data into 2 sheets that are used for the calculations, then delete the 2 new sheets.

Its not as smooth as I would have liked, but it does the job!!!