|
- Sub test()
- Dim sql As String
- Dim mybook As String
- Dim cnn As Object
- Dim rs As Object
- Set cnn = CreateObject("adodb.connection")
- Set rs = CreateObject("adodb.recordset")
- mybook = ThisWorkbook.FullName
- With cnn
- If Application.Version = "11.0" Then
- .Provider = "microsoft.jet.oledb.4.0"
- .ConnectionString = "extended properties=""excel 8.0;HDR=YES;IMEX=1"";data source=" & mybook
- Else
- .Provider = "microsoft.ACE.oledb.12.0"
- .ConnectionString = "extended properties=""excel 12.0;HDR=YES;IMEX=1"";data source=" & mybook
- End If
- .Open
- End With
- sql = "select 序号 from [sheet1$a1:i] where 结果='PASS' group by 序号,结果 having count(序号)=4"
- rs.Open sql, cnn, adOpenKeyset, adLockOptimistic
- With Worksheets("sheet1")
- .Range("m6:m" & .Rows.Count).Clear
- .Range("m6").CopyFromRecordset rs
- End With
- End Sub
复制代码 |
|