Help with Export report to Excel in Access 2007

[rej2008]

I have a small piece of code that is supposed to write out a REPORT in to an Excel file in Excel format and open the document in Excel.

It works fine in witht he command that is now commented out. But with a similar command for Access 2007 does not perform the smae thing. The code that I am using now for Access 2007 does not work.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList"

Here is the complete code.

Public Function ExportEmailList()
 Dim Filename As String
 
 ' Get the Current Path and Make up the file name
 Filename = Application.CurrentProject.Path & "\NEW_Email_List_" & Format(Date, "yyyymmdd") & ".xls"

 MsgBox "Writing Email list to File-" & Filename
'Generate the report in the proper format with names concatenated. For Access 2003. Not for 2007.
 'DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLS, Filename, -1
 
 ' For Access 2007
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList", Filename, True
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList", "NEW_Email_List_" & Format(Date, "yyyymmdd") & ".xls", True
 MsgBox "Completed Writing Email list"
  
End Function

Can someone please tell me what command that i should use in Access 2007 (on Vista) to write out or save a report in an Excel file and open the file automatically.

 

[Vonnie]

Access 2007 Excel exports

Well you can't export a query to Excel 2007 in Access 2007.
You can use the query record source on an invisible form and export it out however

DoCmd.OutputTo acOutputForm, "frmExport", acFormatXLSX, "C:\TEMP\Export.XLSx", True


This seems to work fine for me.

 

[NickH1]

Re: Access 2007 Excel exports

Well you can't export a query to Excel 2007 in Access 2007.
You can use the query record source on an invisible form and export it out however

DoCmd.OutputTo acOutputForm, "frmExport", acFormatXLSX, "C:\TEMP\Export.XLSx", True


This seems to work fine for me.

You CAN export a query to Excel 2007 in Access 2007
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "MyQueryName", "C:\MYxlsxFileName", True

 

[gemma-the-husky]

we discussed this in a different thread

you CAN export a query to Excel in A2007

you cant save a report as an Excel file any longer

------
you used to be able to do this (save a report), but MS wouldn't pay the A2007 developer for the facility, so they withdrew it.

 

[tuxalot]

save access report to excel works in A2010

 

[Starchildren3317]

save access report to excel works in A2010

I have an application written in Access 2003 that we are bringing over to Access 2010. I have several places in my application where I export a report (with the report format) to an excel sheet. However, this does not seem to be working in Access 2010.

Does anyone have any ideas, tips, pointers to how I can convert my code to get this done in 2010 now?