Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: Creating an autofilter in Excel from Access

    I try to create an autofilter in Excel with a certain selection from Access VBA.
    I am able to create the autofilter but the selection doesn't work. Sometimes the autofilter also get "locked", i.e. nothing happens when you click on it.

    This is the code I have used:

    DoCmd.OpenQuery stDocName, acViewNormal
    RunCommand acCmdOutputToExcel
    Excel.Application.Activesheet.Range("A1:E1").Autof ilter Field:=3, Criteria1:="Test1"

    Any idea why the code doesn't work?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Well first off, there's a space in your "Autof ilter", but assuming that's a typo here...

    I've used this with success:
    Code:
    Dim exlApp As Excel.Application, _
        exlBook As Excel.Workbook, _
        exlSheet As Excel.Worksheet
    
    Set exlApp = New Excel.Application
    Set exlBook = exlApp.Workbooks.Open(strFilePath)
    Set exlSheet = exlBook.Worksheets(strExlSheetName)
    
    exlSheet.Activate
    exlSheet.Cells.Rows(1).AutoFilter
    Assuming your excel book and worksheet have already been created. Also see pootle's post in the Code Bank if you'd like to see more help on this.
    Last edited by nckdryr; 05-23-09 at 13:16.
    Me.Geek = True

  3. #3
    Join Date
    May 2009
    Posts
    7
    I was able to create the autofilter with my code, but the problem was to filter with the selection:

    Field:=3, Criteria1:="Test1"

    Nothing happens in Excel when I add this criteria in Access VBA. Instead, the autofilter is locked and can't be used for filtering in Excel.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    First off, a dumb question but just to make sure, there isn't a typo in your code like there was on your post right?


    Else, try something like:
    Code:
    Dim exlApp As Excel.Application, _
        exlBook As Excel.Workbook, _
        exlSheet As Excel.Worksheet
    
    Set exlApp = New Excel.Application
    Set exlBook = exlApp.Workbooks.Open(strFilePath)
    Set exlSheet = exlBook.Worksheets(strExlSheetName)
    
    exlSheet.Activate
    exlSheet.Range("A1:E1").Autofilter Field:=3, Criteria1:="Test1"
    Me.Geek = True

  5. #5
    Join Date
    May 2009
    Posts
    7
    No, there is no typo in my code. The code I tried was this:

    Excel.Application.Activesheet.Range("A1:E1"). _
    Autofilter Field:=3, Criteria1:="Test1"

    I think this should work the same way as your example, but for some reason only the autofilter is created and the last part with the criteria doesn't work.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    BTW, there is a limit of characters to a word on this forum of 50. After fifty characters without a space, a space is programmatically thrown in to break up the word. This is most likely what happened to produce that typo. In CODE tags, I don't think it happens.

    [/offtopic posting]
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Thanks ST, I was beginning to wonder about something like that
    Me.Geek = True

Posting Permissions

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