RTF or XL report filtered (Allen Browne)

[Guest]

I have this great filtering code but what do I need to change in the
following code to have a button export to RTF or xl rather than preview the
report and then have to export or is it possible?
Thanks!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProducts by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Categories].[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Rick Wannall]

Take a look at docmd.transferspreadsheet and docmd.transfertext.

 

[Rick Wannall]

Take a look at docmd.transferspreadsheet and docmd.transfertext.

 

[Allen Browne]

The code you posted builds the string strWhere, and uses it for the
WhereCondition of OpenReport. If you want to export the query or report
instead, you could either:
a) rewrite the query statement before export of the query, or
b) handle the filter in the report's Open event.

Option (a): Export the query
=====================
You can copy the stub of the SQL statement (everything before the WHERE
clause) and the tail (everything after the WHERE clause), patch in the
strWhere string you built in your code, assign it to an existing QueryDef,
and export that:

Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE "
Const strcTail = " ORDER BY Field1;"
Const strcQuery = "Query1"
'use the code you already have to built the strWhere
dbEngine(0)(0).QueryDefs(strcQuery).SQL = strcStub & strWhere & strcTail
DoCmd.TransferText acExportDelim, , strcQuery, "C:\MyExport.txt", True

Option (b): Export the report
=====================
When you use OutputTo with a report, there is no WhereCondition. You
therefore need to pass the filter string to the report somehow. In A2002 or
2003 you could use the OpenArgs to do that, but I prefer to use a public
string variable because it works in all versions and leaves OpenArgs free
for other purposes.

1. In a standard module, General Declarations section (top, with the Option
statements):
Public gstrReportFilter As String

2. In the Open event procedure of the report, apply the string as a filter,
and clear it:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

3. In the code that builds up strWhere, assign to the public variable before
you export the report:
gstrReportFilter = strWhere
DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C\MyExport.rtf"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have this great filtering code but what do I need to change in the
following code to have a button export to RTF or xl rather than preview
the
report and then have to export or is it possible?
Thanks!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProducts by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Categories].[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note
3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

 

[Allen Browne]

The code you posted builds the string strWhere, and uses it for the
WhereCondition of OpenReport. If you want to export the query or report
instead, you could either:
a) rewrite the query statement before export of the query, or
b) handle the filter in the report's Open event.

Option (a): Export the query
=====================
You can copy the stub of the SQL statement (everything before the WHERE
clause) and the tail (everything after the WHERE clause), patch in the
strWhere string you built in your code, assign it to an existing QueryDef,
and export that:

Dim strSql As String
Const strcStub = "SELECT * FROM Table1 WHERE "
Const strcTail = " ORDER BY Field1;"
Const strcQuery = "Query1"
'use the code you already have to built the strWhere
dbEngine(0)(0).QueryDefs(strcQuery).SQL = strcStub & strWhere & strcTail
DoCmd.TransferText acExportDelim, , strcQuery, "C:\MyExport.txt", True

Option (b): Export the report
=====================
When you use OutputTo with a report, there is no WhereCondition. You
therefore need to pass the filter string to the report somehow. In A2002 or
2003 you could use the OpenArgs to do that, but I prefer to use a public
string variable because it works in all versions and leaves OpenArgs free
for other purposes.

1. In a standard module, General Declarations section (top, with the Option
statements):
Public gstrReportFilter As String

2. In the Open event procedure of the report, apply the string as a filter,
and clear it:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

3. In the code that builds up strWhere, assign to the public variable before
you export the report:
gstrReportFilter = strWhere
DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C\MyExport.rtf"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have this great filtering code but what do I need to change in the
following code to have a button export to RTF or xl rather than preview
the
report and then have to export or is it possible?
Thanks!!

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "rptProducts by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Categories].[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note
3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub