Results 1 to 9 of 9
  1. #1
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    27

    Error Object Required when retrieving with autofiltered data in excel


    hi i'm trying to get data from filtered row in excel but it gives me Error: Object Required.

    Code:
                            Set objWorkbook = Workbooks.Open(NewStrGenNNLoc)
                            
                            Set Rngdata = objWorkbook.Worksheets(1).Range("A6").CurrentRegion
                            RowCount = Rngdata.Rows.Count
                            StrToCompare = Format(NewArr(z), "0000000")
                            objWorkbook.Worksheets(1).AutoFilterMode = False
                            objWorkbook.Worksheets(1).Range("A6").AutoFilter Field:=1, Criteria1:=StrToCompare
                            objWorkbook.Worksheets(1).Range("A6").AutoFilter Field:=8, Criteria1:=Format(CDate(Form_TG_NNR_New.lblNewNNRReptDate.Caption), "MM/DD/YY")
                            LCount = objWorkbook.Worksheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count
                            NewPHPCurr = 0
                            NewHKDCurr = 0
                            For i = 1 To LCount
                                StrProgFind = ""
                                GetProgramType = objWorkbook.Worksheets(1).Range("A6").AutoFilter.Range.offset(1).SpecialCells(xlCellTypeVisible).cells(i + 6, 6).Select <-- this area give's me error.
                            Next i

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I get that as well. Exactly what do you expect that line to do?

    I see the filter is removed and all records display.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    27
    hi june7 thanks for your reply.. actually i want to get the visible data from filtered row one by one. by the way i tried my LCount.. it seems not working also...

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'get' - code is trying to Select cell(s) and only SpecialCells.

    If you want value from cell, doesn't really require selecting. Still not clear what that loop should accomplish. Do you want to read each row from start to end of filtered set?

    Yes, LCount is not returning number of records in filtered set.

    What is GetProgramType?

    Does this help https://stackoverflow.com/questions/...-filtered-rows
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    27
    yes sir, i want to read each row from start to end of filtered set. im sorry what i mean is to retrieve data. getprogramtype is just a declared variable.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I figured out how to get count of filtered dataset but the count is not useful.
    Here is example code that works for me.
    Code:
    Dim objExApp As Excel.Application, objWB As Excel.Workbook, objWS As Excel.Worksheet
    Dim rngData As Excel.Range, rw As Excel.Range, RowCount As Integer, StrToCompare As String
    Set objExApp = CreateObject("Excel.Application")
    Set objWB = objExApp.Workbooks.Open("C:\Users\june\Condos.xlsx")
    Set objWS = objWB.Worksheets("Sheet1")
    Set rngData = objWS.Range("A1").CurrentRegion
    objExApp.Visible = True
    StrToCompare = "OK"
    objWS.AutoFilterMode = False
    objWS.Range("A1").AutoFilter Field:=3, Criteria1:=StrToCompare
    RowCount = rngData.Columns(1).SpecialCells(xlCellTypeVisible).Count
    For Each rw In Intersect(objWS.UsedRange, objWS.Range("A:A").SpecialCells(xlCellTypeVisible))
        Debug.Print objWS.Range("A" & rw.Row)
        Debug.Print objWS.Range("B" & rw.Row)
    Next
    
    Well, sort of works. Seems to leave Excel process running even after closing Excel and that causes an error next time procedure runs. Rats!

    This variation doesn't seem to have that issue.
    Code:
    For Each rw In rngData.SpecialCells(xlCellTypeVisible)
        If rw.Address Like "*A*" Then Debug.Print objWS.Range("A" & rw.Row)
        If rw.Address Like "*B*" Then Debug.Print objWS.Range("B" & rw.Row)
    Next
    

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    27
    i guess, as of the moment i'll stick with the loop instead of filter.. my gosh 5000 records to loop.. still not working for me. i dont know why

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What doesn't work for you - my code?

    My code does have a loop and it has filter.

    Sorry, just don't understand line inside the loop in your code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    atzdgreat is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2019
    Posts
    27
    thank you so much Sir June7. it was my mistake why your code doesnt work. too bad for me. my file is in csv. so when i converted from csv to excel.. tsadann!!! your code works... i dont know why filter in csv doesnt work. again thank you so much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Object required error 424
    By GraeagleBill in forum Programming
    Replies: 5
    Last Post: 06-22-2018, 12:00 PM
  2. Error Object required: Please help
    By lccrews in forum Programming
    Replies: 1
    Last Post: 06-14-2018, 10:59 AM
  3. Object Required Error
    By sgp667 in forum Programming
    Replies: 1
    Last Post: 11-06-2012, 03:15 AM
  4. Error: Object Required
    By compooper in forum Programming
    Replies: 6
    Last Post: 06-22-2011, 07:52 AM
  5. Object Required Error.
    By Robeen in forum Forms
    Replies: 1
    Last Post: 03-28-2011, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums