How to export to excell a report from a list control.

[memcha]

I have a problem with my command to export a report in to excell, I create a form with a list control that populates all my reports, I am able to preview the report or sent it to print when I choose one. but now I would like to create a command that can send the desire report to excell.

[ItsMe]

My preffered method for exporting data to excel is the "DoCmd.TransferSpreadsheet" method. I am not sure how your report and listbox come into play but here is an example of some code that uses a Saved Query Object and a string variable to define the file path to use.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQDF, strDirectory, True

[memcha]

this is what I have on my command to open any report from my list. I have not touch Access for long time I am starting to do it but it getting harder to recall everithing.


Private Sub cmdOpenReport_Click()
On Error GoTo cmdOpenReport_ClickErr
If Not IsNull(Me.lstReports) Then
DoCmd.OpenReport Me.lstReports, IIf(Me.ChkPreview.Value, acViewPreview, acViewNormal)
End If
Exit Sub
cmdOpenReport_ClickErr:
Select Case Err.Number
Case 2501 ' Cancelled by user, or by NoData event.
MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
Case Else
MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
End Select
Resume Next
End Sub

and this is what I have to populate the reports list . all everything are on a FORM

Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
' Purpose: Supplies the name of all saved reports to a list box.
' Dim db As Database, dox As Documents, i As Integer
Static sRptName(255) As String ' Array to store report names.
Static iRptCount As Integer ' Number of saved reports.
' Respond to the supplied value of "code".
Select Case code
Case acLBInitialize ' Called once when form opens.
Set db = CurrentDb()
Set dox = db.Containers!Reports.Documents
iRptCount = dox.Count ' Remember number of reports.
For i = 0 To iRptCount - 1
sRptName(i) = dox(i).Name ' Load report names into array.
Next
EnumReports = True
Case acLBOpen
EnumReports = Timer ' Return a unique identifier.
Case acLBGetRowCount ' Number of rows
EnumReports = iRptCount
Case acLBGetColumnCount ' 1 column
EnumReports = 1
Case acLBGetColumnWidth ' 2 inches
EnumReports = 2 * 1440
Case acLBGetValue ' The report name from the array.
EnumReports = sRptName(row)
Case acLBEnd
Erase sRptName ' Deallocate array.
iRptCount = 0
End Select
End Function




and My problem is with command5 I would like to grab the selected report and exported to excell.

thank you for answer and your help.

[ItsMe]

This is how I have done it in the past. I had to look at some old code. Nowadays things are moving to Macros and the OutputTo method's days may be limited. If you have Office Objects referenced this should work.

DoCmd.OpenReport , acViewPreview, , strWhereCondition, acHidden
DoCmd.SelectObject acReport, "ReportName"
DoCmd.OutputTo acOutputReport, "ReportName", acSpreadsheetTypeExcel9, strPath
DoCmd.Close acReport, "ReportName"