Export a report to Excel

[Ana]

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

 

[alecwood via AccessMonster.com]

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,

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

 

[Tony Williams]

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),
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,

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

 

[Ana]

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"
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),
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,

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

 

[Dorothy]

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

"Tony Williams" <[email protected]> escribió en el mensaje de noticias

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),
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
Ana wrote:
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

- 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!

 

[Dorothy]

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

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