Is there a way to export a report from Access 2010 to Excel 2010 (.xlsx format)?

Tim

unread,

Jul 14, 2014, 4:34:37 PM7/14/14

to

I'm trying to "Exportwithformatting" several reports from Access to Excel. I built a macro, but it will only let me use Excel 2003 Workbook (.xls) and it leaves out the headers and formatting from Access. So I converted the macro to VBA and attempted to export it (in xlsx format), to no avail.

Here's an example of my code:

'------------------------------------------------------------
' Expt_Rpt_Click
'
'------------------------------------------------------------
Private Sub Expt_Rpt_Click()
On Error GoTo Expt_Rpt_Click_Err

DoCmd.SetWarnings False
The original:

DoCmd.OutputTo acOutputReport, "ACE Report", "Excel97-Excel2003Workbook(*.xls)", "M:\CORPACCT\SALES\FSS\MANUFACTURER CONTRACTS\Output\", False, "", 0, acExportQualityPrint

Changes I made:

DoCmd.OutputTo acOutputReport, "ACE Report", "ExcelWorkbook(*.xlsx)", "M:\CORPACCT\SALES\FSS\MANUFACTURER CONTRACTS\Output\", False, "", , acExportQualityPrint
DoCmd.SetWarnings True

Expt_Rpt_Click_Exit:
Exit Sub

Expt_Rpt_Click_Err:
MsgBox Error$
Resume Expt_Rpt_Click_Exit

End Sub

I keep receiving a "the format in which you are attempting to output the current object is not available" error message when trying to use the button. I hope that code isn't too confusing. If anyone has any advice, it would be much appreciated. Thank you

Ulrich Möller

unread,

Jul 15, 2014, 6:50:46 AM7/15/14

to

Hi Tim,

Access 2010 does not support export in .xlsx format for any reason. For
publishing reports just use pdf. If you are only interested in the data
you can try transferspreadsheet.
Perhaps HTML is an option for you.

Ulrich

(PeteCresswell)

unread,

Jul 15, 2014, 4:35:48 PM7/15/14

to

Per Tim:

>I'm trying to "Exportwithformatting" several reports from Access to Excel. I built a macro, but it will only let me use Excel 2003 Workbook (.xls) and it leaves out the headers and formatting from Access. So I converted the macro to VBA and attempted to export it (in xlsx format), to no avail.

I have not found a way.

On my "Report" screens I offer an "Excel" option that just does an
OutputTo of the report's .RecordSource... but, of course there is no
formatting/headers.

For the Real Deal, I bite the bullet and start coding VBA to create an
empty Excel document and then fill it in from the .RecordSoure -
hand-coding all the column headers, formatting, and so-forth.

First time around takes a lot of man hours, but if you do it right, the
big pieces should be clonable/modifiable for another report in about a
quarter the man hours.
--
Pete Cresswell

Phil

unread,

Jul 15, 2014, 5:42:35 PM7/15/14

to

On 15/07/2014 21:35:51, "(PeteCresswell)" wrote:
> Per Tim:
>>I'm trying to "Exportwithformatting" several reports from Access to Excel. I built a macro,

but t it will only let me use Excel 2003 Workbook (.xls) and it leaves out

the headers and formatting from Access. So I converted the macro to VBA and
attempted to export it (in xlsx format), to no avail. >
> I have not found a way.
>
> On my "Report" screens I offer an "Excel" option that just does an
> OutputTo of the report's .RecordSource... but, of course there is no
> formatting/headers.
>
> For the Real Deal, I bite the bullet and start coding VBA to create an
> empty Excel document and then fill it in from the .RecordSoure -
> hand-coding all the column headers, formatting, and so-forth.
>
> First time around takes a lot of man hours, but if you do it right, the
> big pieces should be clonable/modifiable for another report in about a
> quarter the man hours.

Back to my posting dated 8 July

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com