Autofilter Excel with VBA

问题:

I would like to open Excel from Access and apply filters to a sheet. Below is my code:

Dim s as String
Set oApp = CreateObject("Excel.Application")
oApp.Wworkbooks.Open FileName:="dudel.xlsm"
oApp.Visible = True
s = "AB"
With oApp
        .Rows("2:2").Select
        .Selection.AutoFilter
        .ActiveSheet.Range("$A$2:$D$9000").AutoFilter Field:=3, Criteria1:= _ 
             Array(s, "E", "="), Operator:=xlFilterValues
        .Range("A3").Select 
End With

When I ran the code, I got this error:

runt time error 1004 Autofilter methond of range class failed

Can anyone see why?

回答1:

Try this one. I've commented code in details, but if you have some questions - ask:)

Sub test()
    Dim s As String
    Dim oApp As Object
    Dim wb As Object
    Dim ws As Object


    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True

    'tries to open workbook
    On Error Resume Next
    'change file path to the correct one
    Set wb = oApp.workbooks.Open(FileName:="C:\dudel.xlsm")
    On Error GoTo 0

    'if workbook succesfully opened, continue code
    If Not wb Is Nothing Then
        'specify worksheet name
        Set ws = wb.Worksheets("Sheet1")
        s = "AB"
        With ws
            'disable all previous filters
            .AutoFilterMode=False
            'apply new filter
            .Range("$A$2:$D$9000").AutoFilter Field:=3, Criteria1:=Array(s, "E"), Operator:=7
        End With

        'close workbook with saving changes
        wb.Close SaveChanges:=True
        Set wb = Nothing
    End If

    'close application object
    oApp.Quit
    Set oApp = Nothing
End Sub

and also one more thing: change Operator:=xlFilterValues to Operator:=7 (access doesn't know about excel constanst until you add reference to the excel library in access)