Need help creating a Excel file from a report

[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.
Code:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewEmailList"

Here is the complete code.
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.

 

[khawar]

In access 2007 you cannot convert report to excel you have to export data through a query

 

[rej2008]

Yes, the script I have is precisely doing that and it works fine in Access 2003. But when I use that script in Access 2007, I get an error. And that is the real issue here. How do you export data through a quesry in Access 2007.

In Access 2003, this line works, but not in Access 2007:

DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLS, Filename, -1

 

[Brianwarnock]

I think that you should start again and in the title ask how to export a query in 2007, it would stop us non2007 people getting frustrated and attract the correct experts. Also say what the error is, not just that you got one.

Brian

 

[rej2008]

Thanks... I will do that. menawhile, if anybody who is an expert on 2007 see this, please respond.

 

[khawar]

Use this to send query to excel 97-2003 format

DoCmd.OutputTo acOutputQuery, "Your Query Name Here", acFormatXLS

Use this to send query to excel 2007 format

DoCmd.OutputTo acOutputQuery, "Your Query Name Here", acFormatXLSX

Use this to open a prompt which will ask you in which format to convert

DoCmd.OutputTo acOutputQuery, "Your Query Name Here"

 

[rej2008]

Thanks for the reply. But I need to send a report to Excel2007 or Excel 2003 format.

DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLS, Filename, -1

The above command that was working in Access 2003 would actually write the report out to an Excel file and opens the file automatically.

I am trying to achieve the same in Access 2007.

 

[Brianwarnock]

What happens if you use

DoCmd.OutputTo acOutputReport, "rptNewEmailList", acFormatXLSX, Filename, -1

as khawar is suggesting.

Brian

 

[rej2008]

Yes, I tried that already, but I get the following error message.

Run time error '2282'
The format in which you are attempting to output the currnet object is not available.

In the action failed msg window, the error number displayed is 2950.

When I click on the debug button, it will take me that particular line.

I am using Access 2007 on Vista.

 

[Brianwarnock]

Ok
perhaps a vista user will be able to help, I'm sorry that I can't.

Brian

 

[khawar]

I have already told you that you cannot convert report to excel like you used to in Access 2003 using acreport method
I gave you an alternative to send the query to excel it is access 2003 code but also works in access 2007

 

[rej2008]

Thanks for the clarification. I wasn't aware that the feature for converting report to Excel is not available in 2007. It is surprising that they would remove such a basic feature instead of adding new ones.

So is there no other alternative methods to convert a REPORT to Excel or is it just that my method using acReport won't work.

 

[Bilbo_Baggins_Esq]

Previously, the only times I had need to export reports to Excel, the databases were in Access 2000 format.
As far as I could determine, this was not possible in Access 2000 format, whether the database was running in Access 2000, or Access 2003.

I never had the opportunity to actually use the ability to export an actual report in Access 2003 and frankly, I did not even know it was ever possible.

However, while I suspect I know this answer (based on prior experience running 2000 format in 2003), I do find myself wondering if an Access 2003 formatted database running in Access 2007 will allow the report to export to Excel?

Going back to my previous experiences, not being able to do this presents some frustrating issues.
Many reports are actually the result of several queries being displayed at the same time in one report.
The need to export individual queries not only adds code lines for each query, but also places each query in seperate tabs of the workbook instead of all on the same sheet.
To get around this, I have had to "build" the report in a temp table, line by line, and then export that table to Excel.

So question 1 is, will an Access 2003 db running in Access 2007 allow reprot exporting to Excel or does it have to be running in Access 2003 proper?

Then question 2 is, am I missing something about exporting multi-query based reports in Excel? Do I really have to "build" the report in a seperate temp table?