用VBA自动过滤Excel

Question

我想从Access打开Excel，并将筛选器应用于工作表。下面是我的代码：

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

当我运行代码时，我得到了以下错误：

range类的runt时间误差1004自动滤波方法失败

有人知道为什么吗？

Answer 1

试试这个。我已经对代码进行了详细的注释，但是如果您有一些问题--问：)

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

还有一件事:将Operator:=xlFilterValues更改为Operator:=7 (access不知道excel，除非在access中添加对excel库的引用)