Export Access Query to a formatted Excel sheet

[ezra]

I have an access report based on a bunch of queries that does a quarterly summary of some research related stats. Now my boss wants this report to export into excel based on a specific format she has developed. This formatted excel sheet also needs to keep all previous quarters information stored in it and just add subsequent quarters. I don't know where to start!

[Stewart Ross]

Hi. You may get some ideas of how to open and write to Excel by having a look at post 6 of this thread, which shows two VBA subroutines replacing the TransferSpreadsheet method to output to an existing Excel workbook instead of a new blank workbook.

In your case you are almost certain to have to write record values directly to Excel rather than outputting the whole recordset as shown in the above thread. You would need to learn how to refer to individual cells in Excel using the current active worksheet and the Cells method. Another thread shows examples of this and is linked here.

As you wish to retain previous worksheets it also means copying and pasting worksheets within the workbook. All this is easy enough once you understand the Excel object model; I'd suggest, though, that the development task is likely to take a bit longer than your boss thinks it will whilst you get to this stage!

-Stewart

[ezra]

Thanks. I'll check these post out and see what happens.

Hi. You may get some ideas of how to open and write to Excel by having a look at post 6 of this thread, which shows two VBA subroutines replacing the TransferSpreadsheet method to output to an existing Excel workbook instead of a new blank workbook.

In your case you are almost certain to have to write record values directly to Excel rather than outputting the whole recordset as shown in the above thread. You would need to learn how to refer to individual cells in Excel using the current active worksheet and the Cells method. Another thread shows examples of this and is linked here.

As you wish to retain previous worksheets it also means copying and pasting worksheets within the workbook. All this is easy enough once you understand the Excel object model; I'd suggest, though, that the development task is likely to take a bit longer than your boss thinks it will whilst you get to this stage!

-Stewart