Question run time error 3011 when exporting access report to excel

[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​

 

[DavidAtWork]

have you tried debugging and inspecting the value set in tempStr, would interested to see what value this variable is holding after the line:
tempStr = Application.CurrentDb.Containers("Reports").....

David

 

[captdkl02]

It is passing the name of the report that I was expecting. I changed the code slightly using DoCmd.OutputTo acOutputReport. 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").Documents(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

 

[DavidAtWork]

Just remove the last x in your line:
theFilePath = theFilePath & reportname & ".xlsx"
change it to:
theFilePath = theFilePath & reportname & ".xls"
and should be ok
David