这样应该OK了
- Private Sub CommandButton1_Click()
- Dim Cnn, strSql$, Rst, a%
- Set Cnn = CreateObject("adodb.connection")
- Application.ScreenUpdating = False
- Cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- strSql = "select 姓名,班级,学号,入学年月,性别,喜好,数据1,数据2 FROM [数据表$A:H] where "
- For a = 3 To 7
- strSql = strSql & Cells(a, "j") & " like '%" & Cells(a, "k") & "%' and "
- Next
- strSql = Left(strSql, Len(strSql) - 5)
- Set Rst = Cnn.Execute(strSql)
- Range([h2], Cells(Rows.Count, "a").End(3)).ClearContents
- [a2].CopyFromRecordset Rst
- Rst.Close
- Set Rst = Nothing: Set Cnn = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码 |