自动筛选Excel中使用VBA [英] Autofilter Excel with VBA
本文介绍了自动筛选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屋!
查看全文