How do I output a simple Access Report as an Excel spreadsheet?

Thanks for the prompt response, peter57r.

I downloaded and installed the latest Microsoft updates, including the Office 2007 SP2 pack.
I am now getting a different error message (# 2587), telling me my application "can't complete the Output operation".

There is some discussion of this on the web, and I tried ticking a couple of new items in my VBA - Tools - References list : "Microsoft Office 12.0 Access database engine Object Library" and "Microsoft Excel 12.0 Object Library" but to no avail.

Any thoughts, anyone, on this new error message I am getting?

Thanks. Colin.

If you need fancy formatting, you can prepare an Excel template, create a new workbook from it, export the data to specific columns, and create subtotals and totals using Automation code.

Thanks, Helen.

The underlying SQL Server "View" generates more rows than I want to export to Excel at any one time.
I am building "WHERE" and "ORDER BY" clauses to filter the rows that are of interest.
I was then opening an Access Report and applying these clauses in the "Report_Open" procedure.
I'm not sure that I can modify the "view" on-the-fly (especially as the ultimate users will be using a "runtime" version of Access).
Am I better to use a "stored procedure" to copy my required rows to a "temporary" table and then try to export from that table? I'll give it a try.

Colin.

Thanks, Helen.

The underlying SQL Server "View" generates more rows than I want to export to Excel at any one time.
I am building "WHERE" and "ORDER BY" clauses to filter the rows that are of interest.
I was then opening an Access Report and applying these clauses in the "Report_Open" procedure.
I'm not sure that I can modify the "view" on-the-fly (especially as the ultimate users will be using a "runtime" version of Access).
Am I better to use a "stored procedure" to copy my required rows to a "temporary" table and then try to export from that table? I'll give it a try.

Colin.

"the ultimate users will be using a "runtime" version of Access"

My understanding is that OutputTo is broken in the runtime.

Sorry, ...I left my first paragraph "Hanging"...
Should be:

>As a general statement here.
>The Export To Excel Option in Access never was, and never will be, perfect.
>So even if you got it working, it may not be in the exact format you would want.

Thanks for the further comments.

Helen_Feddema : I moved away from trying to "OutputTo" an Access Report to Excel, to exporting a simple Access Form using the attached code. (This was quicker to achieve than populating a "temporary" Table with a Stored Procedure). This appeared to work OK and a spreadsheet was created OK (albeit with a couple of oddities described below).

peter57r : "My understanding is that OutputTo is broken in the runtime."
What does this mean in lay terms? That it doesn't work?
I created an ".ADE" version of my ".ADP" application and launched it with my "full" Access in "runtime" mode and it appeared to work OK. This might however be different from running a true "runtime" version on a PC that doesn't have a "full" version.

boag2000 :
Apologies for mixing my Access and SQL Server terminology. Using the VBA code behind my Access Forms I can execute Stored Procedures which are indeed held within my SQL Server database. However, with an Access "project", these two systems are so closely connected, that I can create and edit SQL Server Stored Procedures using the Access Navigation Pane that also displays them.

The move to the "Form" as the basis of my "OutputTo" Excel appears to have worked, although there are some formatting issues as you suggested. Some "quantity" fields in my Access Form were formatted as "Standard" with 0 "Decimal Places", but appeared with 2 decimal places in my Excel spreadsheet. When I "blanked" my "Format" property of these fields in my Access Form, they appeared as "integer" values in Excel (as I wanted them to). Also, the columns in the spreadsheet are in "tab index" order rather than as they appear on the screen in the Access Form (but that's easily sorted).

Can you point me to an article that describes how VBA can be used to edit my Excel formatting? Presumably this would be done after my existing code has created the spreadsheet, and the new code tweaks the formatting within it.

Many thanks for all your contributions. Now I need to decide how to award points.

Regards. Colin.

DoCmd.OpenForm strFormName, , , , , , strLongOpenArgs
        
DoCmd.OutputTo acOutputForm, strFormName, acFormatXLS, _
                                "C:\STKHOLDINGS.XLS", False
        
DoCmd.Close acForm, strFormName

                                              

1:
2:
3:
4:
5:
6:

Try this Access Archon article on creating Excel timesheets from Access data (can't attach it because it contains a forbidden extension):  http://www.helenfeddema.com/Files/accarch127.zip

Yes there are many links here as well, but you need to specify exactly what manner of formatting you require.

Most code like this will focus on moving/deleting Columns and/or rows.
Ex:.
    Columns(4).EntireColumn.Delete
    Rows(8).EntireRow.Delete
   
But mostly formatting.
Ex.:
Range("A1").Font.Bold = True
Range("A1").NumberFormat = "$#,##0.00"


;-)

JeffCoachman

The ever-thorough A.D. Tejpal has provided a resource for outputting report content to Word and Excel.
See the entry at Roger's Library: Report Export.
The intention being to maintain greater formatting than the default exports. (Regardless of version or service pack).

Cheers.