Export withFormatting Macro

[yagerlegi]

Hello,



I need help exporting a data that is selected from a listbox. I have already created the listbox and have a button to print only the selected item.
I want to create a button to export excel data related to listbox. (The listbox contain a city and I want the user to be able to export the list zip code from a table when they click on the button.)

Thank you,

[June7]

Why do you need export to Excel?

Options:

1. dynamic parameterized query, export the query object

2. apply filter criteria to a report, open report, export report

[yagerlegi]

I will have to say for with both options. I want to provide the user the ability to select the information they want and when they click on the button it give them an excel with the data from the table. I already have it set to go to report.

[June7]

You will have to what?

Export of report to Excel can give unsatisfactory results - depends how complicated the report is.

What will user do with the export?

[yagerlegi]

Sorry I was talking about the option you gave me in your previous reply. I want it to export to excel based on the selection made on the listbox. I want the users to be able to save the data on their computer.

[June7]

And that's all they do with it, save it to their computer? Do they actually do anything with the exported data?

Is this resolved?

[yagerlegi]

No, It has not been resolved. Yes, I just want to give the user an option to export the data to excel if they choose to. The VB code below is what I'm using for report.I want to be able to do that for Excel Report.

Private Sub cmdOpenReport_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.city.ItemsSelected.Count = 0 Then
MsgBox " Must select a City"
Exit Sub
End If

'add selected values to string
Set ctl = Me.city
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'Open the report, restricted to the selected items
DoCmd.OpenReport "City", acViewReport, , "City IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
End Sub

[June7]

Does that much work so far?

After report is opened:

DoCmd.OutputTo acOutputReport, "City", acFormatXLS, "C:\somename.xls"
DoCmd.Close acReport, "City", acSaveNo