Export from Access 2007 Report to excel



In Access 2003 I could export to an excel file from Report Preview, or by
using the output to macro command, choosing a Report name, and choosing Excel
2003 format.
the file type of output format does not seem to be included in the
equivalent areas in 2007 (unless I export from a table or query, but not from
a report)

Scott Lichtenberg


From what I gather, this feature was taken out of Access 2007. There's no
way to duplicate this functionality, but there are a couple of workarounds.
You can use the DoCmd.TransferSpreadsheet command to export data to a
spreadsheet. It can export tables and Select queries, so you can export the
recordsource of your report directly.

Another option is to write data directly to a spreadsheet. Here's some code
that shows you some of the techniques.

Dim db as Database
Dim rs as Recordset

Dim objXL As Object
Dim objActiveWkb As Object

Dim r as Long 'Counters for row and column
Dim c as Long

Set db = CurrentDb
Set rs = db.OpenRecordset("MyRecordset", dbOpenSnapshot)

'Set reference to Excel. Note that this requires Excel to be open.
There are methods to open Excel if it is not open.
Set objXL = GetObject(, "Excel.Application")
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb

'Create a new sheet in workbook and set up headers, column widths,
.Worksheets.Add.Move after:=.Worksheets(.Worksheets.Count)
.ActiveSheet.Name = "MySheetName"
.ActiveSheet.Cells.Font.Name = "Arial" 'You can use these
settings if you want, or you use the defaults
.ActiveSheet.Cells.Font.Size = 8

objXL.ActiveWindow.Zoom = 80

With .ActiveSheet.PageSetup
.CenterHeader = "My Report Name"
.TopMargin = objXL.InchesToPoints(0.75)
.BottomMargin = objXL.InchesToPoints(0.5)
.LeftMargin = objXL.InchesToPoints(0.5)
.RightMargin = objXL.InchesToPoints(0.5)
End With

With .ActiveSheet
.Columns(1).ColumnWidth = 2 'I'm adjusting column
For x = 2 To 7
.Columns(x).ColumnWidth = 18
.Columns(x).HorizontalAlignment = &HFFFFEFF4
Next x
End With

'Add column headers
r = 1
For c = 1 to rs.Fields.Count
With .ActiveSheet
.Cells(r, c) = rs(c - 1).Name ' Columns start a 1,
fields start at 0
End With
Next c

'Loop through your records
r = 2 'Data starts on second row
Do While Not rs.EOF

For c = 1 to rs.Fields.Count
With .ActiveSheet
.Cells(r, c ) = rs(c - 1)
End With
Next c

r = r + 1

Set rs=Nothing

End With

Set objActiveWkb = Nothing
Set objXL = Nothing

One additional note. There is an add-on available on Microsofts web site
that allows you to export reports to PDF and XPS formats.

Hope this helps.


According to http://support.microsoft.com/kb/934833/en-us this functionality
has been disabled by design in Access 2007.

Here's a code snippet of a workaround I use which outputs to a datasheet
that can be copied then pasted into Excel:

Public Function ViewAsSpreadsheet()
On Error GoTo ErrHandler
Dim rptName As String
Dim strSql As String

rptName = Application.CurrentObjectName
strSql = Reports(rptName).RecordSource
If ChkObject("Query", "TempQry") = True Then ' is object defined?
CurrentDb.QueryDefs.Delete "TempQry"
End If
CurrentDb.CreateQueryDef "TempQry", strSql
DoCmd.OpenQuery "TempQry", acViewNormal, acReadOnly

Exit Function

This gets the query string from the recordsource for the report that is
open, then creates a temporary query using the query string. From there you
can open the query as a datasheet.
Apr 18, 2011
Reaction score
The problem for me is that I dont want to create an additional step by xporting to a db. I am already thrice removed and feel that another leap is going overboard. I need a more streamlined workaround.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question