Runtime error 3011 VBA export access report to excell

[captdkl02]

Hello,



I using excel 2010 and access 2010. I have VBA script runtime error 3011 when running script. It has problem finding access report. First I was passing in as variable with the name. Then I used a script to pull in the report name from access and it is still failing with same error.

Code is shown below.

Any help greatly appreciated.

Thank you.

David

-----------

Private Sub Command29_Click()
Dim reportname As String
Dim theFilePath As String, FilePath As String, tempStr As String

' reportname = Me.My_DBTableName I was passing in here, but comment the code out

tempStr = Application.CurrentDb.Containers("Reports").Docume nts(2).Name ' using command pulling in the report name from Access database

reportname = tempStr
theFilePath = Me.My_Export_file_path
theFilePath = theFilePath & reportname & ".xlsx"

' It is failing with code in red
DoCmd.OutputTo acOutputQuery, reportname, acFormatXLSX, theFilePath, True

' I was using the transferspreadsheet command and it was failing for the same reason of not finding report name

' DoCmd.TransferSpreadsheet acExport, 10, reportname, theFilePath, True

MsgBox "Look on your desktop for the report."

End Sub

[June7]

Use acOutputReport instead of acOutputQuery.

Be aware, reports don't output very nice.

Step debug - is the reportname variable getting correct value?

[tanvi]

As per my knowledge, this error occurs when an object can't be found. The error should tell you what object it can't find. First make sure that the object is exist or not and also check that you are correctly spell its name & pathname.

[captdkl02]

It is passing the name of the report that I was expecting. I changed the code slightly using DoCmd.OutputTo acOutputReport. I have included the code again with slight modifications. It is writing the file to the directory, however; I am receiving another errror. Error message => "Excel cannot open the file 'test.xlsx' because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Private Sub Command29_Click()
Dim reportname As String
Dim theFilePath As String, FilePath As String, tempStr As String

' reportname = Me.My_DBTableName
tempStr = Application.CurrentDb.Containers("Reports").Docume nts(2).Name

reportname = tempStr
theFilePath = Me.My_Export_file_path
theFilePath = theFilePath & reportname & ".xlsx"

' outReportData = "your report name"
DoCmd.OutputTo acOutputReport, reportname, acFormatXLS, theFilePath, -1

'Keep Excel file closed = 0; to open Excel file automatically = -1

' DoCmd.OutputTo acOutputQuery, reportname, acFormatXLSX, theFilePath, True

' DoCmd.TransferSpreadsheet acExport, 10, reportname, theFilePath, True
MsgBox "Look at the file directory you specified for the report."
End Sub

[June7]

You show xlsx in file path but only xls in the format argument.

[tanvi]

Thiserror (Excelcannot open the file 'test.xlsx')only occurs when the file is not compatible with Excel or the file got corrupted. Please visit this article for solution:http://file-repair.blogspot.in/2012/09/how-to-fix-error-excel-cannot-open-file.html

[captdkl02]

Thank you for your responses. I was able to get the code to work. The report output to excel still lost the formatting I was looking for. Anyway, I believe will have to export access into excel 1st and then format the excel file 2nd.