自动筛选Excel中使用VBA [英] Autofilter Excel with VBA

查看:365
本文介绍了自动筛选Excel中使用VBA的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想从Access中打开Excel和应用过滤器的片材。 下面是我的code:

 暗淡参考译文]字符串
设置oApp =的CreateObject(Excel.Application)
oApp.Wworkbooks.Open文件名:=dudel.xlsm
oApp.Visible = TRUE
S =AB
随着oApp
        .Rows(2:2),选择
        .Selection.AutoFilter
        .ActiveSheet.Range($ A $ 2:$ D $ 9000的),自动筛选字段:= 3,标准1:= _
             阵列(S,E,=),运营商:= xlFilterValues
        .Range(A3)。选择
结束与
 

当我跑了code,我得到这个错误:

  

矮的时间误差范围类的1004自动筛选釆失败。<​​/ P>

任何人都可以看到,为什么?

解决方案

试试这个。我评论code的细节,但如果你有一些问题 - 问:)

 子测试()
    昏暗的参考译文字符串
    昏暗oApp作为对象
    昏暗的世行作为对象
    昏暗的WS作为对象


    设置oApp =的CreateObject(Excel.Application)
    oApp.Visible = TRUE

    试图打开工作簿
    在错误恢复下一页
    更改文件路径正确的
    设置白平衡= oApp.workbooks.Open(文件名:=C:\ dudel.xlsm)
    对错误转到0

    如果工作簿成功地打开了,继续code
    如果没有工务局没有那么
        指定工作表的名称
        设置WS = wb.Worksheets(工作表Sheet1)
        S =AB
        随着WS
            禁用所有previous过滤器
            .AutoFilterMode = FALSE
            申请新的过滤器
            .Range($ A $ 2:$ D $ 9000的),自动筛选字段:= 3,标准1:=阵列(S,E),运营商:= 7
        结束与

        与储蓄的变化密切工作簿
        wb.Close的SaveChanges:= TRUE
        设置白平衡=什么
    结束如果

    接近应用程序对象
    oApp.Quit
    设置oApp =什么
结束小组
 

和也一件事:改变运营商:= xlFilterValues​​ 运营商:= 7 (Access不了解有关Excel constanst,直到你在访问中添加引用Excel的库)

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?

解决方案

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)

这篇关于自动筛选Excel中使用VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆