Report Recordsets

[EriRobert]

Hello All,

I am trying to create a generic function that exports the recordset of the current report to Excel. My code is as below:

Public Function ExcelPrint()

Dim rpt As Report
Dim rst As ADODB.Recordset
Dim objExcel As Excel.Application
Dim intRow As Integer
Dim intColumn As Integer

Set rpt = Screen.ActiveReport
Set rst = rpt.Recordset

Set objExcel = New Excel.Application
objExcel.Visible = True
objExcel.SheetsInNewWorkbook = 1
objExcel.Workbooks.Add

With objExcel.ActiveSheet
.Cells(1, 1).Value = rpt.Name
For intColumn = 0 To rst.Fields.Count - 1
.Cells(intRow, intColumn + 1) = rst.Fields(intColumn).Name
Next
intRow = 4
Do Until rst.EOF
For intColumn = 0 To rst.Fields.Count - 1
.Cells(intRow, intColumn + 1) = rst.Fields(intColumn)
Next
intRow = intRow + 1
rst.MoveNext
Loop
End With

objExcel.Close True
objExcel.Quit
Set objExcel = Nothing

End Function

My problem occurs on the line:

Set rst = rpt.Recordset

as you don't seem to be able to access the recordset method for a report like you can for a form.

How do I access a report's recordset? I can't go through the reports recordsource as this would involve redefining the queries parameters which will differ from report to report.

Any ideas?

Thanks in advance.


Robert

 

[markgrizzle]

How do you define the report recordsets now? I usually build a sql statement on the fly, then open the report with it.

You might try updating a generic querydef's sql and then outputting that to excel.

 

[EriRobert]

Mark,

This function will be called from a button on the toolbar of all my reports when they are being shown in Print Preview mode (the button will say something like 'Export Base Data to Excel').

In my experience formatted reports do not export very well to Excel as the formatting confuses Excel. Therefore I thought I would instead export the underlying recordset to Excel.

As I want this routine to work for all my reports I thought the easiest method was to extract the reports recordset (irrespective of how it is build (table, view or stored procedure)) then export this record set to Excel. I can isolate the Report though Screen.ActiveReport and I know you can isolate a FORMS recordset though a forms recordset or recordsetclone methods. However a REPORT doesn't have these methods - so how do I access its recordset? I know I could go via the reports RecordSource - something like:

Dim rpt as Report
Dim qdf As QueryDef
dim rst as recordset

Set rpt = Screen.ActiveReport
Set qdf = rpt.QueryDefs(rpt.RecordSource)
' need to define parameters
Set rst = qdf.OpenRecordset(, dbReadOnly)


But I would have to define the parameters, which I don't know because they differ from report to report.

Any ideas?

Thanks

 

[markgrizzle]

Wouldn't the report's parameters already be defined if you're using the active report's recordsource?

 

[EriRobert]

Correct me if I'm wrong, but as I understand it (etc. etc.) the difference between using a recordsource and a recordset is that by using the recordsource you are starting again and therefore have to redefine any parameters. By using the recordset you are accessing what the report has already got - a usable recordset.

Robert

 

[danvlas]

Reports do NOT have the Recordset property.
That's why you get the error.

One way is to run the report's recordsource as an SQL statement and fill in the Excel worksheet. In this case you have to take care of any Filter/OrderBy property you may have, otherwise your report may show a filtered dataset while the recordsource would output everything.

Another way is to use the Format event of report's sections to output data to Excel while formatting the report.

Or fill in a global recordset variable with data while formatting the report, then copy it to the spreadsheet.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[EriRobert]

Thank you Daniel,

You're right I could achieve this by adding code to the report. However I really wanted to avoid adding code to each of my reports and wanted to develop something independent of the report itself, hence isolating the recordset in a called function. As you said this is not possible.

The recordsource for the reports are generally stored procedures (I'm a Access Project), so I'm can't really rerun it from the function without respecifying input parameters to the SP.