Question What is the simplest method to export a Report to Excel file in Access 2007?

[SimonSezz]

I am converting an old database that was from Access 2002 to Access 2007. Everything has gone well and I'm at the final point. The problem I have run into is with a function in VBA that is no longer valid in Access 2007. It is something like this:

Select Case ReportName
        Case "Report1"
            'some code
        Case "Report2"
            'some code
        Case "Report3"
            'some code
End Select
 
txt = InputBox("Enter file name:", , "C:\ExcelFiles\" & ReportName & "_" & Format(Date, "yyyymmdd") & ".xls")
 
DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, txt

From what I gather, outputting a report directly to Excel is no longer an included function in Access 2007. The problem is I have a bunch of reports and the user can select if they want to export them to Excel. Is there a simple way to do this?

 

[rommelgenlight]

wow you have quite a problem there. what's the problem using your previous ms access? you don't have problem there isnt it?

 

[SimonSezz]

Well there weren't any problems with the previous version but they converted all of the computers in the office to Office 2007 so I don't really have a choice.

 

[rommelgenlight]

As I can see in your syntax above, did you use DIM for your "txt" and for your "reportname".

Moreover, what is that date in your inputbox syntax, is that a criteria or a default value?
if that is a default value then is should be date(). if that is a criteria then you should also dim it.

 

[SimonSezz]

As I can see in your syntax above, did you use DIM for your "txt" and for your "reportname".

Moreover, what is that date in your inputbox syntax, is that a criteria or a default value?
if that is a default value then is should be date(). if that is a criteria then you should also dim it.

The code I posted is sort of pseudo-code, it's not meant to be taken that literally. The issue is outputting the report into Excel form.

 

[HiTechCoach]

I use something like this:

Export to Excel