Results 1 to 19 of 19

Thread: Apply filter and import results.

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Apply filter and import results.

    Hi.

    i have a file that is saved down to a particular folder that I wish to import.

    w:\.team documents\freehold team\freeholders managers\reporting\reports\Insurance Ledger.csv

    However I don't want to import the whole file because its too big.

    Instead I want to only import data after I have applied a filter on particular column headers.

    Column Header Filter to apply

    Insurance Type Buildings - Property Owners

    Status Active
    Status Scheduled

    Once the filters have been applied. Import columns A:S and paste special value into tab Insurance.

    Thanks

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    i would probably use sql to query the data and import

    post some sample data
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Hello.

    Yes SQL would probably be easier but that would involve setting up a database which is not possible.

    I've included attachments.

    Here you can see the data I wish to import and I 've highlighted the columns i wish to filter.


    Attachment 179399


    Now you can see the first filter I've applied in Column B.

    Attachment 179400

    Second filter applied in Column P. Once all the filters have been applied. Import the whole file and paste into tab called Insurance.

    Attachment 179401

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    es SQL would probably be easier but that would involve setting up a database which is not possible.
    you can also query against a worksheet
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    ok fair enough. Not sure how to do that though. Would prefer to code it via VBA. That would be my preference

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    none of your attachments can be opened

    this is just proof of concept, using sql in VBA, you will need a reference to ADO (activex data objects)
    Code:
    Sub csvsql()
    Dim cn As Connection, rs As Recordset, cat As ADOX.Catalog
    
    Set cn = New Connection
    Set rs = New Recordset
    Set cat = New Catalog
    mypath = "w:\.team documents\freehold team\freeholders managers\reporting\reports\"
    
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
                "Extended properties='text;hdr=no;fmt=csvdelimited'"
                .Open
            End With
    rs.Open "select * from Insurance Ledger.csv where Insurance Type Buildings - Property Owners = 'john' ", cn    ' as it contains spaces the filename, it may require brackets
    '  you can use a where clause in the above line to do the filtering on multiple columns
    
    Sheets("insurance").Range("a1").CopyFromRecordset rs    'copy the recordset to whatever desired cell
    rs.close
    cn.close
    End Sub
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Thanks for that. Very interested in this.

    Just want to understand for my own learning. Why is it preferable to use SQL for this particular problem?

    I've now included a workbook so we can test the new VBA code.

    Insurance example.zip

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    Why is it preferable to use SQL
    faster than looping cells and does not in any way change any data in worksheet, selecting on multiple criteria much easier
    downside is you can not copy any formatting, only data

    i tested this, appeared to work correctly, returned 3 records
    Code:
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & ActiveWorkbook.FullName & ";" & _
                "Extended properties='Excel 12.0; header  = yes'"
    
    '            .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
                "Extended properties='text;hdr=no;fmt=csvdelimited'"
                .Open
            End With
    rs.Open "select * from [Insurance Ledger$] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn
    
    Sheets("insurance").Range("a1").CopyFromRecordset rs
    note as the sample data is in a worksheet now, not .csv, i had to change the connection string, should work fine with previous connection string, commented out above
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    ok the below code is breaking.

    cat As ADOX.Catalog

    I presume I need to reference this somehow. Which option do I select in the tools>reference table?

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    yes it would require a reference, BUT
    you can delete that line, as while i was using a catalog for testing, is not used in the finished code
    you must also delete the line Set cat = New Catalog, as it will also error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Ok I'm getting an error message on this line..... - No value given for one or more required parameters

    rs.Open "select * from [Insurance Ledger.csv] where [Type]='Buildings - Property Owners' and [Status]='Active' or [Status]='Scheduled'", cn



    Sub Insurance()

    Dim cn As Connection, rs As Recordset

    Set cn = New Connection
    Set rs = New Recordset
    mypath = "W:\.Team documents\Freehold team\Freehold managers\Reporting\Reports"

    With cn
    .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
    "Extended properties='text;hdr=no;fmt=csvdelimited'"
    .Open
    End With
    rs.Open "select * from [Insurance Ledger.csv] where [Type]='Buildings - Property Owners' and [Status]='Active' or [Status]='Scheduled'", cn

    Sheets("Insurance").Range("a1").CopyFromRecordset rs
    rs.Close
    cn.Close

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    please always use code tags when posting code, much better for me to see

    i tested all the code with the sample file you posted, but that was using a worksheet for the source data as supplied in the sample
    i had done some other testing on .csv files and would expect that to with the code as posted directly above,
    if you post a .csv, i can test with that, i will not create a .csv file here for testing as it may not be a correct sample (been there, done that)

    i just noticed you are missing the required brackets on the status alternatives, put them in and test again
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Hi. Sorry. Not sure what you mean by posting with code tags??

    I've attached a new zip file that includes a CSV file which hopefully you can test.

    Is it better to use "microsoft.Jet.oledb. rather than microsoft.ace.oledb for csv files? Does it depend on what version of Excel I'm running?


    Once tested can you post the full code?

    Thanks for doing this
    Attached Files Attached Files

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    Is it better to use "microsoft.Jet.oledb. rather than microsoft.ace.oledb for csv files? Does it depend on what version of Excel I'm running?
    jet is the older version and may not work with all versions of excel, but probably no difference for .csv files
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    here is a tested code using your .csv source file, i was of course using a different path
    Code:
    Dim cn As Connection, rs As Recordset
    Set cn = New Connection
    Set rs = New Recordset
    mypath = "w:\.team documents\freehold team\freeholders managers\reporting\reports\"
            With cn
                .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
                "Extended properties='text;hdr=yes;fmt=csvdelimited'"
                .Open
            End With
            rs.Open "select * from [Insurance example.csv] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn
            Sheets("insurance").Range("a1").CopyFromRecordset rs
            rs.close
            cn.close
    the code tags around the code improve the appearance and readability of the code
    Last edited by westconn1; Dec 9th, 2020 at 03:41 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Hi,

    Code breaks on the below line.

    The microsoft access database engine cannot open or write to the file ". It is already opened exclusively by another user , or you need permission to view and write its data.


    rs.Open "select * from [Insurance example.csv] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    do you or someone else already have the file open?
    it is a very specific error, unlike the previous one
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Lively Member
    Join Date
    May 2019
    Posts
    112

    Re: Apply filter and import results.

    Ok nearly there. Problem has been fixed.

    Just one further requirement. I need the column headers in the CSV file to be copied over as well.

  19. #19
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,182

    Re: Apply filter and import results.

    try this in place of the copyfromrecordset line
    Code:
    with Sheets("insurance").Range("a1")
        for col =  0 to rs.fields.count
             .offset(,col) = rs.fields(col).name
        next
        .offset(1).copyfromrecordset rs
    end with
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width