Results 1 to 6 of 6
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    .autofilter explination

    I am trying to figure out how to use the .Autofilter to filter a column in and excel spreadsheet from Access. It always wants an = Object at the end of the codeset.
    I tried

    XlApp = Excel.Application
    Field 8 = Column H
    Plat is the filter in Column H
    True is I want to see the filter arrow.



    Set WkBkA = Workbooks.Open("C:\Temp\BalanceSheet_New.xlsx")
    Set varSheetA = WkBkA.Worksheets(1).Range(strRangeToCheck)
    xlApp.AutoFilter(8, "Plat", , True) = varSheetA

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not 100% sure but I'll say xlApp (the Excel Application) can't have a filter. The spreadsheet (varSheetA) can have the filter.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not 100% sure but I'll say xlApp (the Excel Application) can't have a filter. The spreadsheet (varSheetA) can have the filter.
    Not sure you can set a filter and apply it to the whole sheet that way.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Micron. Thanks
    So it should be

    WkbkA.AutoFilter(8, "Plat", , True) = varSheetA?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That should take care of the current error message. However, as I mentioned, I don't think you can say the filter equals the sheet object. That doesn't make sense to me. Plus, I believe the autofilter has to be with respect to the sheet range. You should be able to apply a filter to a worksheet range, but what you're doing doesn't look right. Gotta run, so I suggest you Google "vba range autofilter"
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Micron,
    Thanks, Googling brought me to where I searched before. Between you suggestion and figuring out I don't need the ( ) around the variables after the .autofilter I figured it out.

    WkBkC.Worksheets(1).Range(strRangeToCheck).AutoFil ter 8, strColVar

    Thanks

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

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