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 
    Formatting tags added by mark007
    Last edited by samuelimtech; 04-20-2017 at 04:05 AM.

  2. #2
    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 
    Formatting tags added by mark007

Posting Permissions

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