Apply filter prior to circling through recordset and copying to spreadsheet (Access 365 version)

%3CLINGO-SUB%20id%3D%22lingo-sub-2817899%22%20slang%3D%22en-US%22%3EApply%20filter%20prior%20to%20circling%20through%20recordset%20and%20copying%20to%20spreadsheet%20(Access%20365%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817899%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20form%20in%20my%20access%20database%20which%20filters%20data%20from%20a%20query%20to%20pull%20up%20only%20information%20with%20a%20user%20entered%20numerical%20field.%26nbsp%3B%20Ultimately%2C%20I%20would%20like%20the%20user%20to%20be%20able%20to%20enter%20the%20number%20to%20search%20in%20a%20text%20box%2C%20click%20to%20allow%20the%20data%20to%20be%20filtered%20so%20that%20only%20data%20with%20that%20information%20is%20presented%20and%20then%20export%20the%20result%20to%20excel.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20above%20functional%20with%20a%20DoCmd.ApplyFilter%3B%20however%2C%20some%20of%20the%20data%20is%20stored%20within%20a%20variable%20which%20allows%20rich%20text%20format.%26nbsp%3B%20Exporting%20the%20result%20to%20excel%20after%20filtering%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Faccess%2F04-rich-text-formating-is-stripped-when-exporting-from-access-to%2Fm-p%2F2175405%23M2665%22%20target%3D%22_self%22%3Estrips%20this%20minimal%20formatting%3C%2FA%3E%2C%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Faccess%2F03-access-output-to-excel-truncates-data%2Fm-p%2F2175364%22%20target%3D%22_self%22%3Esometimes%20truncates%20the%20data%3C%2FA%3E.%26nbsp%3B%20Following%20the%20suggestion%20provided%20under%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Faccess%2F04-rich-text-formating-is-stripped-when-exporting-from-access-to%2Fm-p%2F2175405%23M2665%22%20target%3D%22_self%22%3E04%20-%20Rich%20Text%20formatting%20is%20stripped%3C%2FA%3E%2C%20I%20have%20applied%20the%20code%20referenced%20in%20the%20Microsoft%20Docs%20article%3A%26nbsp%3B%3CEM%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-US%2Fprevious-versions%2Foffice%2Ftroubleshoot%2Foffice-developer%2Ftransfer-excel-data-from-ado-recordset%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20to%20transfer%20data%20from%20an%20ADO%20Recordset%20to%20Excel%20with%20automation%3C%2FA%3E%3C%2FEM%3E.%26nbsp%3B%20With%20some%20minimal%20edits%2C%20this%20worked%20wonderfully%20to%20produce%20a%20spreadsheet%20which%20resolves%20both%20of%20those%20issues%3B%20however%2C%20I%20am%20only%20able%20to%20output%20the%20entire%20query%2C%20not%20the%20filtered%20query%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20edit%20the%20code%20below%20so%20that%20the%20output%20is%20filtered%20by%20the%20value%20entered%20in%20the%20textbox%20on%20the%20form%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Automate_Excel_Click()%0A'%20Code%20adapted%20from%20following%20location%3A%20https%3A%2F%2Fdocs.microsoft.com%2Fen-US%2Fprevious-versions%2Foffice%2Ftroubleshoot%2Foffice-developer%2Ftransfer-excel-data-from-ado-recordset%20%2C%20Also%20used%20code%20from%20https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D7HckYjH_wg4%0ADim%20db%20As%20Database%0ADim%20rst%20As%20DAO.Recordset%0A%0ADim%20xlApp%20As%20Object%0A%20%20%20%20Dim%20xlWb%20As%20Object%0A%20%20%20%20Dim%20xlWs%20As%20Object%0A%0ADim%20recArray%20As%20Variant%0A%0ADim%20strDB%20As%20String%0A%20%20%20%20Dim%20fldCount%20As%20Integer%0A%20%20%20%20Dim%20recCount%20As%20Long%0A%20%20%20%20Dim%20iCol%20As%20Integer%0A%20%20%20%20Dim%20iRow%20As%20Integer%0A%0ASet%20db%20%3D%20CurrentDb%0ASet%20rst%20%3D%20db.OpenRecordset(%22qryCorresTracking%22)%0A%0A%0A%0A%0A'%20Create%20an%20instance%20of%20Excel%20and%20add%20a%20workbook%0A%20%20%20%20Set%20xlApp%20%3D%20CreateObject(%22Excel.Application%22)%0A%20%20%20%20Set%20xlWb%20%3D%20xlApp.Workbooks.Add%0A%20%20%20%20Set%20xlWs%20%3D%20xlWb.Worksheets(%22Sheet1%22)%0A%0A'%20Display%20Excel%20and%20give%20user%20control%20of%20Excel's%20lifetime%0A%20%20%20%20xlApp.Visible%20%3D%20True%0A%20%20%20%20xlApp.UserControl%20%3D%20True%0A%0A'%20Copy%20field%20names%20to%20the%20first%20row%20of%20the%20worksheet%0A%20%20%20%20fldCount%20%3D%20rst.Fields.Count%0A%20%20%20%20For%20iCol%20%3D%201%20To%20fldCount%0A%20%20%20%20%20%20%20%20xlWs.Cells(1%2C%20iCol).Value%20%3D%20rst.Fields(iCol%20-%201).Name%0A%20%20%20%20Next%0A%0A'%20Check%20version%20of%20Excel%0A%20%20%20%20If%20Val(Mid(xlApp.Version%2C%201%2C%20InStr(1%2C%20xlApp.Version%2C%20%22.%22)%20-%201))%20%26gt%3B%208%20Then%0A%20%20%20%20%20%20%20%20'EXCEL%202000%2C2002%2C2003%2C%20or%202007%3A%20Use%20CopyFromRecordset%0A%0A'%20Copy%20the%20recordset%20to%20the%20worksheet%2C%20starting%20in%20cell%20A2%0A%20%20%20%20%20%20%20%20xlWs.Cells(2%2C%201).CopyFromRecordset%20rst%0A%20%20%20%20%20%20%20%20'Note%3A%20CopyFromRecordset%20will%20fail%20if%20the%20recordset%0A%20%20%20%20%20%20%20%20'contains%20an%20OLE%20object%20field%20or%20array%20data%20such%0A%20%20%20%20%20%20%20%20'as%20hierarchical%20recordsets%0A%0AElse%0A%20%20%20%20%20%20%20%20'EXCEL%2097%20or%20earlier%3A%20Use%20GetRows%20then%20copy%20array%20to%20Excel%0A%0A'%20Copy%20recordset%20to%20an%20array%0A%20%20%20%20%20%20%20%20recArray%20%3D%20rst.GetRows%0A%20%20%20%20%20%20%20%20'Note%3A%20GetRows%20returns%20a%200-based%20array%20where%20the%20first%0A%20%20%20%20%20%20%20%20'dimension%20contains%20fields%20and%20the%20second%20dimension%0A%20%20%20%20%20%20%20%20'contains%20records.%20We%20will%20transpose%20this%20array%20so%20that%0A%20%20%20%20%20%20%20%20'the%20first%20dimension%20contains%20records%2C%20allowing%20the%0A%20%20%20%20%20%20%20%20'data%20to%20appears%20properly%20when%20copied%20to%20Excel%0A%0A'%20Determine%20number%20of%20records%0A%0ArecCount%20%3D%20UBound(recArray%2C%202)%20%2B%201%20'%2B%201%20since%200-based%20array%0A%0A'%20Check%20the%20array%20for%20contents%20that%20are%20not%20valid%20when%0A%20%20%20%20%20%20%20%20'%20copying%20the%20array%20to%20an%20Excel%20worksheet%0A%20%20%20%20%20%20%20%20For%20iCol%20%3D%200%20To%20fldCount%20-%201%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20iRow%20%3D%200%20To%20recCount%20-%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20Take%20care%20of%20Date%20fields%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20IsDate(recArray(iCol%2C%20iRow))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20recArray(iCol%2C%20iRow)%20%3D%20Format(recArray(iCol%2C%20iRow))%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20'%20Take%20care%20of%20OLE%20object%20fields%20or%20array%20fields%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ElseIf%20IsArray(recArray(iCol%2C%20iRow))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20recArray(iCol%2C%20iRow)%20%3D%20%22Array%20Field%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20iRow%20'next%20record%0A%20%20%20%20%20%20%20%20Next%20iCol%20'next%20field%0A%0A%0A%0A'%20Transpose%20and%20Copy%20the%20array%20to%20the%20worksheet%2C%0A%20%20%20%20%20%20%20%20'%20starting%20in%20cell%20A2%0A%20%20%20%20%20%20%20%20xlWs.Cells(2%2C%201).Resize(recCount%2C%20fldCount).Value%20%3D%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20TransposeDim(recArray)%0A%20%20%20%20End%20If%0A%0A'%20Auto-fit%20the%20column%20widths%20and%20row%20heights%20(added%20additional%20line%20to%20set%20columns%20as%20wrap%20text.%0A%20%20%20%20xlApp.Selection.CurrentRegion.Columns.AutoFit%0A%20%20%20%20xlApp.Selection.CurrentRegion.Columns.WrapText%20%3D%20True%0A%20%20%20%20xlApp.Selection.CurrentRegion.Rows.AutoFit%0A%0A'%20Close%20ADO%20objects%0A%20%20%20%20rst.Close%0A%20%20%20%20Set%20rst%20%3D%20Nothing%0A%0A'%20Release%20Excel%20references%0A%20%20%20%20Set%20xlWs%20%3D%20Nothing%0A%20%20%20%20Set%20xlWb%20%3D%20Nothing%0A%0ASet%20xlApp%20%3D%20Nothing%0A%0AEnd%20Sub%0A%0AFunction%20TransposeDim(v%20As%20Variant)%20As%20Variant%0A'%20Custom%20Function%20to%20Transpose%20a%200-based%20array%20(v)%0A%0ADim%20X%20As%20Long%2C%20Y%20As%20Long%2C%20Xupper%20As%20Long%2C%20Yupper%20As%20Long%0A%20%20%20%20Dim%20tempArray%20As%20Variant%0A%0AXupper%20%3D%20UBound(v%2C%202)%0A%20%20%20%20Yupper%20%3D%20UBound(v%2C%201)%0A%0AReDim%20tempArray(Xupper%2C%20Yupper)%0A%20%20%20%20For%20X%20%3D%200%20To%20Xupper%0A%20%20%20%20%20%20%20%20For%20Y%20%3D%200%20To%20Yupper%0A%20%20%20%20%20%20%20%20%20%20%20%20tempArray(X%2C%20Y)%20%3D%20v(Y%2C%20X)%0A%20%20%20%20%20%20%20%20Next%20Y%0A%20%20%20%20Next%20X%0A%0ATransposeDim%20%3D%20tempArray%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2817899%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2828760%22%20slang%3D%22en-US%22%3ERe%3A%20Apply%20filter%20prior%20to%20circling%20through%20recordset%20and%20copying%20to%20spreadsheet%20(Access%20365%20version)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2828760%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20the%20following%20update%20to%20the%20code%20and%20the%20filter%20seems%20to%20work%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESet%20rst%20%3D%20db.OpenRecordset(%3CFONT%20color%3D%22%230000FF%22%3E%22Select%20*%20FROM%20qryCorresTracking%20WHERE%20AppNumber%20LIKE%20'%22%20%26amp%3B%20Me.AppSearch%20%26amp%3B%20%22*'%22%3C%2FFONT%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good morning, 

 

I have a form in my access database which filters data from a query to pull up only information with a user entered numerical field.  Ultimately, I would like the user to be able to enter the number to search in a text box, click to allow the data to be filtered so that only data with that information is presented and then export the result to excel.  

I have the above functional with a DoCmd.ApplyFilter; however, some of the data is stored within a variable which allows rich text format.  Exporting the result to excel after filtering strips this minimal formatting, and sometimes truncates the data.  Following the suggestion provided under 04 - Rich Text formatting is stripped, I have applied the code referenced in the Microsoft Docs article: How to transfer data from an ADO Recordset to Excel with automation.  With some minimal edits, this worked wonderfully to produce a spreadsheet which resolves both of those issues; however, I am only able to output the entire query, not the filtered query result.

 

How can I edit the code below so that the output is filtered by the value entered in the textbox on the form?

 

Private Sub Automate_Excel_Click()
' Code adapted from following location: https://docs.microsoft.com/en-US/previous-versions/office/troubleshoot/office-developer/transfer-excel-data-from-ado-recordset , Also used code from https://www.youtube.com/watch?v=7HckYjH_wg4
Dim db As Database
Dim rst As DAO.Recordset

Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

Dim recArray As Variant

Dim strDB As String
    Dim fldCount As Integer
    Dim recCount As Long
    Dim iCol As Integer
    Dim iRow As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("qryCorresTracking")




' Create an instance of Excel and add a workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True

' Copy field names to the first row of the worksheet
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next

' Check version of Excel
    If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
        'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
        xlWs.Cells(2, 1).CopyFromRecordset rst
        'Note: CopyFromRecordset will fail if the recordset
        'contains an OLE object field or array data such
        'as hierarchical recordsets

Else
        'EXCEL 97 or earlier: Use GetRows then copy array to Excel

' Copy recordset to an array
        recArray = rst.GetRows
        'Note: GetRows returns a 0-based array where the first
        'dimension contains fields and the second dimension
        'contains records. We will transpose this array so that
        'the first dimension contains records, allowing the
        'data to appears properly when copied to Excel

' Determine number of records

recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array

' Check the array for contents that are not valid when
        ' copying the array to an Excel worksheet
        For iCol = 0 To fldCount - 1
            For iRow = 0 To recCount - 1
                ' Take care of Date fields
                If IsDate(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                ' Take care of OLE object fields or array fields
                ElseIf IsArray(recArray(iCol, iRow)) Then
                    recArray(iCol, iRow) = "Array Field"
                End If
            Next iRow 'next record
        Next iCol 'next field



' Transpose and Copy the array to the worksheet,
        ' starting in cell A2
        xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
            TransposeDim(recArray)
    End If

' Auto-fit the column widths and row heights (added additional line to set columns as wrap text.
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Columns.WrapText = True
    xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
    rst.Close
    Set rst = Nothing

' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing

Set xlApp = Nothing

End Sub

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

Xupper = UBound(v, 2)
    Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
    For X = 0 To Xupper
        For Y = 0 To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

TransposeDim = tempArray

End Function

 

1 Reply

I made the following update to the code and the filter seems to work:

 

Set rst = db.OpenRecordset("Select * FROM qryCorresTracking WHERE AppNumber LIKE '" & Me.AppSearch & "*'")