|
发表于 2012-8-28 22:58
|
显示全部楼层
本楼为最佳答案
本帖最后由 柳如烟 于 2012-8-28 23:15 编辑
- Sub 筛选()
- Dim arr, cn, rs, sql$, i%
- Set cn = CreateObject("adodb.connection")
- With cn
- .provider = "microsoft.ace.oledb.12.0"
- .connectionstring = "extended properties='excel 12.0;hdr=no';data source=" & ThisWorkbook.FullName
- .Open
- End With
- sql = "select f2,f3,f4,f5,f6,f7,f8,f9,f10,f11 from [库存$a2:l" & Sheet56.UsedRange.Rows.Count & "] where"
- With Sheets("录入表")
- arr = .Range("d1:l1")
- For i = 1 To UBound(arr, 2)
- If arr(1, i) <> "" Then
- If IsNumeric(arr(1, i)) Then
- sql = sql & " f" & i + 1 & "=" & arr(1, i) & " and "
- Else
- sql = sql & " f" & i + 1 & "='" & arr(1, i) & "' and "
- End If
- End If
- Next
- sql = Left(sql, Len(sql) - 6)
- Set rs = cn.Execute(sql)
- .Range("d2:p1000").ClearContents
- .Range("d2").CopyFromRecordset cn.Execute(sql)
- End With
- cn.Close
- End Sub
复制代码 修改一下 |
|