Export from Access 2007 Report to excel

[Peter]

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]

Peter,

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,
etc.
.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
widths.
For x = 2 To 7
.Columns(x).ColumnWidth = 18
.Columns(x).HorizontalAlignment = &HFFFFEFF4
'xlcenter
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

rs.MoveNext
r = r + 1
Loop

rs.Close
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.

 

[Kathy]

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
ErrHandler:
......

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.