export to excel

[ahau]

Hi,

I have a form that has a button to export access report to excel. I've got that button working. It creates an excel file based on the access report. However, i want to do something more. In the form, i also have a listbox showing records filtered by a couple of comboboxes. So what i would like to do is to be able to see the excel file with the records only shown on the listbox, not the entire records in the table. I noticed that with the following code, i can't really put thru the stlinkcriteria.

DoCmd.OutputTo acOutputReport, stDocName

Here is my code looks like;

Private Sub CM_Export2excel_Click()
On Error GoTo Err_CM_Export2excel_Click

Dim stDocName As String
Dim stLinkCriteria As String

nList = Me.mylist.ListCount

For ncount = 0 To nList - 1
stLinkCriteria = "," & Me.mylist.ItemData(ncount) & stLinkCriteria
Next ncount

If Len(stLinkCriteria) > 0 Then
stLinkCriteria = "[student_ID] In (" & Mid(stLinkCriteria, 2) & ")"
End If

stDocName = "CHW Job_Report"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.Close acReport, stDocName, acSaveYes
DoCmd.OutputTo acOutputReport, stDocName

From the code above, i was trying to create a report with the records from the listbox and save it, then export it to excel and it doesn't work. So i was wondering what would be the better way of doing this.

Thank you in advance

 

[mp9]

What happens if you try outputting your report BEFORE you close it, i.e.

.
.
.
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.OutputTo acOutputReport, stDocName
DoCmd.Close acReport, stDocName, acSaveYes
.
.
.

 

[CautionMP]

Here is a variation on what you had. I used the [tt]IN[/tt] clause to do the filtering and applied the new filter to the form in design view.

Private Sub CM_Export2excel_Click()
...
Dim varSelected As Variant
Dim stLinkCriteria as String

...

[green]'Build the In Clause[/green]
For Each varSelected In Me.MyList.ItemsSelected
  [green]'Add the comma between items[/green]
  If stLinkCriteria <> "" Then
    stLinkCriteria = stLinkCriteria & ","
  End If
  [green]'Chr(39) = '[/green]
  stLinkCriteria = stLinkCriteria & Chr(39) & _
                   Me.MyList.ItemData(varSelected) & Chr(39)
Next varSelected

[green]'Open the report in design view and change the filter[/green]
DoCmd.OpenReport stDocName, acViewDesign
With Reports(stDocName)
  .Filter = "[b][i]TheFieldToFilter[/i][/b] In(" & stLinkCriteria & ")"
  .FilterOn = True
End With
DoCmd.Close acReport, stDocName, acSaveYes

...

The only drawback to this method is the user will see the report open in design view.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

I just figured out that you can use [tt]Application.Echo False[/tt] before opening the report in Design View to keep Access from painting the report on the screen.

CMP
P.S. Be sure you turn the Echo back on when your done.

(GMT-07:00) Mountain Time (US & Canada)