Export a report to excel that uses a query and additional filters.

[smcdonald]

I have a report that opens up using a pretty complex query. I then pop up a form with combo boxes so the user can apply a filter to the existing report and then refresh the report. I need to export the report's records with the applied query to excel in datasheet form and it needs to contain the additional filters that the user selects. The reports datasource is the original query.

Right now I have this and get the datasheet format, but I need the filters that were passed to the report - how can I export the query with the additional filters?

'Export the query as a datasheet in excel - missing filters from user
DoCmd.OutputTo acOutputQuery, "BOM_QTY_Status _qry", acFormatXLS, strFileName, True

Thanks

[PEB]

Hi,

Simply set up those filters directly in your query instaed of your reports

:)

[pks00]

Is it possible to export your report in excel?
I mean run your report in preview mode then analyze it with excel

e.g


DoCmd.OpenRepor t "MyReport", acViewPreview
DoCmd.RunComman d acCmdOutputToEx cel

[NeoPa]

It is possible, but crap ;).

Not entirely, but the resultant spreadsheet has data in columns all over the place and is hard work to format into a useful document.

I am working on a similar job ATM and I decided to implement, in code, the application of the various filters into the underlying recordsource of the Report.

It doesn't use the data already run for the report.
I'll copy in the routine I'm using for ideas, but it uses functions you won't have available so it won't work for you directly. Even this one routine is quite large. I will however dig up the other stuff if requested - I don't wanna post a load of code here that no-one wants. I myself am always very cautious about using code developed elsewhere.

'ExportToExcel exports recordset {strRS} to {strFolder}\{strName}.Xls
'Returns the eventual file name used
Public Function ExportToExcel(strName As String, _
                              Optional ByVal strRS As String = "", _
                              Optional ByVal strWhere As String = "", _
                              Optional ByVal strFolder As String = "") As String
    Dim strOut As String, strFile As String, strType As String
    Dim objRS As Object
 
    'If no RecordSet supplied assume "tbl" & strName
    If strRS = "" Then strRS = "tbl" & strName
    'Determine the DataSet type (strType) - (T)able; (Q)uery; (S)QL
    On Error Resume Next
    Set objRS = CurrentDb.TableDefs(strRS)
    If Not objRS Is Nothing Then
        strType = "T"
    Else
        Set objRS = CurrentDb.QueryDefs(strRS)
        strType = IIf(objRS Is Nothing, "S", "Q")
    End If
    On Error GoTo 0
    'Code to insert a WHERE clause into the SQL from strWhere
    If strWhere > "" Then
        strRS = ParamReplace("SELECT * FROM (%S)%A WHERE (%W)", _
                             "%S", strRS, _
                             "%A", IIf(strType = "S", " AS subQ", ""), _
                             "%W", strWhere)
        strType = "S"
    End If
    'If SQL string to be used - create temp query
    If strType = "S" Then _
        Set objRS = TempQuery(strName:=strName, strSQL:=strRS, blnCopy:=True)
    'If no folder supplied, use the 'Temp' folder
    If strFolder = "" Then strFolder = Environ("Temp")
    'For uniformity, remove any trailing "\" from folder name
    If Right(strFolder, 1) = "\" Then _
        strFolder = Left(strFolder, Len(strFolder) - 1)
    strOut = strFolder & "\" & strName & "New.Xls"
    strFile = strFolder & "\" & strName & ".Xls"
    'Delete temp file we will use, if it exists - if not, then not a problem
    On Error Resume Next
    Call Kill(strOut)
    On Error GoTo 0
    'Export to temp file - if this works then rename to required file
    strName = objRS.Name
    Set objRS = Nothing
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:=strName, _
                                   FileName:=strOut)
    On Error Resume Next
    Call Kill(strFile)
    On Error GoTo 0
    Name strOut As strFile
    If strType = "S" Then Call CurrentDb.QueryDefs.Delete(Name:=strName)
    ExportToExcel = strFile
End Function

[smcdonald]

To export a report to Excel is messy looking. Export a query instead, it exports in datasheet form. What I did was create a query that the report uses and a button on a form that says 'Export to Excel'. On the click event I call the query for the report being displayed and it has the command:

DoCmd.OutputTo acOutputQuery, "query_name_her e", acFormatXLS, "path_and_file_ name_here", True

[NeoPa]

I'm doing a lot of work on exporting to Excel in my main database at work ATM.
I wasn't aware of that construct - I'll check into it tomorrow in case it's better than what I'm currently using (DoCmd.Transfer Spreadsheet).

[hunterbowker]

My name is hunter bowker.
came here through google.
this forum seems to be ranking high on google.
btw what is the most busiest time on this forum so that i could interact with peak members or is it ever this lonely.