Export Recordsource

[Paul H]

I have a form "frmReports" that opens a list of reports in a table. This works great. I want to give the open of exporting the data behind the report to excel. I'm having trouble nabbing this (the record source). I've gone at this several different ways, but keep stubbing my toe here.

'Opens the selected report in the default report view
Dim objReport As Report
 
If Me.cboReports = 0 Then Exit Sub
 
strReportName = Me.cboReports.Column(1)
intRptView = Me.cboReports.Column(2)
strRptName = Me.cboReports.Column(3)
 
strFileName = CurrentProject.Path & "\Excel\" & strReportName & ".xlsx"
Set objReport = "Report_" & strRptName
strRecordSource = objReport.RecordSource
 
If Me.chkExport = 0 Then
DoCmd.OpenReport strRptName, intRptView
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strRecordSource, strFileName
End If

I keep getting runtime error 424, Object required at Line 11.

I could just pop the queries in my table and reference them in my code, but some of these reports have embedded SQL.

Your help would be appreciated.

[June7]

Think either of these would work:
Set objReport = strRptName
or
Set objReport = Reports(strRptName)

[Paul H]

Runtime Error 2453 Report name is either misspelled, not open or doesn't exist.

or I get a type mismatch

My curiosity is waning. I'm not sure it can be done this way, but here what I did do.

I listed my queries in the table. Where I had no query, I open the report and grab the record source there, then I call the same function, passing the same arguments from either side. This works. It is not as elegant as I would like, but it still passes muster. Here's my function.

Public Function ExportQuery(strRecordSource, strReportName)
 
strFileName = CurrentProject.Path & "\Excel\" & strReportName & Format(Date, "_yyyymmdd") & ".xls"
 
CurrentDb.QueryDefs("qryExportReport").SQL = strRecordSource 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportReport", strFileName
 
MsgBox "This report has been exported to " & vbNewLine & vbNewLine & strFileName
 
End Function

Thank you though.