Exporting from Access 2003 & Access 2007 to Excel

[Richard Hannah]

In a mixed Office 2003 & 2007 I am trying to run a macro to export a Query to
Excel. This fails in Acess 2007 if I use any of the Excel options for the
output format (e.g. Microsoft Excel 5-7 (*.xls), Microsoft Excel 97-2003
(*.xls)) and if I amend it in Access 2007 using the
"Excel97-Excel2003Workbook(*.xls)" format it fails in Access 2003.

 

[Richard Hannah]

In a mixed Office 2003 & 2007 I am trying to run a macro to export a Query to
Excel. This fails in Acess 2007 if I use any of the Excel options for the
output format (e.g. Microsoft Excel 5-7 (*.xls), Microsoft Excel 97-2003
(*.xls)) and if I amend it in Access 2007 using the
"Excel97-Excel2003Workbook(*.xls)" format it fails in Access 2003.

The following works but relies on Microsoft not changing error numbers etc.
and may need amendment in later versions:

Sub OutputQuery(QueryName)
OutputFormat = "Excel97-Excel2003Workbook(*.xls)" ' Access 2007
On Error GoTo OutputError
' Export
DoCmd.OutputTo acQuery, QueryName, OutputFormat, "", True, "", 0
Exit Sub
' If the OUTPUT fails
OutputError:
Select Case Err
Case 2501 ' Cancel
Resume Next
Case 2282 ' Format not recognised
OutputFormat = "Microsoft Excel 97-2003 (*.xls)" ' try Access 2003
Resume
Case Else
MsgBox "Error No:" & Err & " - " & Err.Description
Resume Next
End Select
End Sub