Results 1 to 2 of 2

Thread: recordset.filter not filtering and i dont know why

  1. #1

    recordset.filter not filtering and i dont know why

    Hi all.

    thanks for any help.

    below you will find a function that converts an array to a records set. seeing as the array size can change you will notice i use loops to define the field names. you will therefore have to trust me that their is a fieldname named period with a value of 1. the dataformat is set to varchar.

    i've tried every combination of the .filter method that i can think of. can someone please take a stab at editing my code such that the filter works.


    Private Function ConvertToRecordset(arrValues As Variant) As Recordset
        Dim oRs  As New Recordset
        Dim lRecCount
        Dim i As Integer
        Dim FieldName As String
        lRecCount = UBound(arrValues)
        Num_col = UBound(arrValues, 2)
        For icol = 0 To Num_col
        FieldName = arrValues(0, icol)
        oRs.Fields.Append FieldName, adVarChar, adFldMayBeNull
        Next icol
        For irow = 1 To lRecCount
        If (lRecCount > 0) Then
            For icol = 0 To Num_col
            FieldName = arrValues(0, icol)
            oRs(FieldName).Value = arrValues(irow, icol)
            Next icol
       End If
       Next irow
       Set ConvertToRecordset = oRs
       'up to here works
    '"some_field NOT LIKE '%ABC%' "
    ConvertToRecordset.Filter = "Period = '1'"
    With ThisWorkbook.Sheets("Sheet1").QueryTables.Add( _
            Connection:=ConvertToRecordset, _
        .Name = "Contact List"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    End Function
    Last edited by samuelimtech; 04-20-2017 at 04:05 AM.

  2. #2
    VBAX Expert
    Oct 2012
    Your code seems a little convoluted.

    I'm sure you can just copy an array to Excel.
    Since this is a disconnected recordset I don't see what you'd gain from adding it as a connection (assuming it would allow you to) because you can't refresh it.

    Anyway, you need to open a recordset from your recordset.

    oRs.Filter = "Period = '1'"
    Set ConvertToRecordset = oRs.openrecordset

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts