Export a report to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ana - 14 Nov 2007 12:54 GMT

Hi,

I'd like to export a report to Excel via a cmd. The report is linked to a
stored procedure with a variable bonded to a form's combox.

I guess I'd use a cmd button as below but I don't know the rt syntax. Any
help is appreciated.

Ana

Private Sub CmdExcel_Click()

On Error GoTo Err_CmdExcel_Click

Dim query As String
Dim file As String

query = StoredProcedureFile @Client = Forms![frmParent]![ComboBox]
file = 'c:\..\Desktop\test.xls'

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, query,
file, false

Exit_ CmdExcel _Click:
Exit Sub
Err_ CmdExcel _Click:
MsgBox Err.Description
Resume Exit_ CmdExcel _Click
End Sub

Reply to this Message

alecwood - 14 Nov 2007 13:35 GMT

I tried but could not get this method to work with a stored procedure, so I
loaded the appropriate stored procedure into a recordset (rsMyRecordSet),
defined and opened an Excel spreadsheet object (objMyExcelSheet), and used
this

objMyExcelSheet.Cells(1, 1).CopyFromRecordset rsMyRecordSet

I have no doubt there's a more elegant solution, but this worked for me.

Alec

>Hi,
>
[quoted text clipped - 25 lines]
> Resume Exit_ CmdExcel _Click
>End Sub

Reply to this Message

Tony Williams - 14 Nov 2007 15:57 GMT

When you preview a report there is an option to export it to an Excel or
word file look at the tool bar at the top and you will see the Word "W"
which if you click will give you the option to export to excel. Have you
tried that?
Tony

>I tried but could not get this method to work with a stored procedure, so I
> loaded the appropriate stored procedure into a recordset (rsMyRecordSet),
[quoted text clipped - 36 lines]
>> Resume Exit_ CmdExcel _Click
>>End Sub

Reply to this Message

Ana - 14 Nov 2007 16:16 GMT

Thank you Tony,
I'm aware of the option but in my ade project it's not available (has been
blocked out) that's why I'm using a cmd option.
Ana

> When you preview a report there is an option to export it to an Excel or
> word file look at the tool bar at the top and you will see the Word "W"
[quoted text clipped - 43 lines]
>>> Resume Exit_ CmdExcel _Click
>>>End Sub

Reply to this Message

Dorothy - 14 Nov 2007 17:57 GMT

> Thank you Tony,
> I'm aware of the option but in my ade project it's not available (has been
[quoted text clipped - 53 lines]
>
> - Show quoted text -

Hi Ana.

Here's a code for exporting a report to Excel. Command5 is a button
that was created on a form.

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String

stDocName = "Table1" 'Table1 is the name of the report
DoCmd.OutputTo acReport, stDocName

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Good luck!

Reply to this Message

Dorothy - 14 Nov 2007 19:01 GMT

> Hi,
>
[quoted text clipped - 25 lines]
> Resume Exit_ CmdExcel _Click
> End Sub

Hi there. I believe instead of TransferSpreadsheet you need something
like this.

Dim stDocName As String

stDocName = "Report"
DoCmd.OutputTo acReport, stDocName, acFormatXLS, "Report.xls"

Good luck.

Dorothy

Reply to this Message