|
本帖最后由 vicrly 于 2014-2-13 16:33 编辑
我的源码是这样的
Sub 查询()
Dim sql As String, cnn As Object, rs
Set cnn = CreateObject("Adodb.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
sql = "Select * From [汇总表$a2:r65536] where 名称='电脑'"
rs.Open sql, cnn
Sheet4.Range("a3:r65536").ClearContents //一旦使用这句清除,查询结果就只显示一条。不清除没有任何异常。
Sheet4.Cells(20, 1).CopyFromRecordset rs
rs.Close
Set rs = Nothing
Set cnn = Nothing
MsgBox ("执行完成!")
End Sub
因为当前表的数据较多,想查询后清理当前表内容,返回查询结果。
按照我的理解,数据已经查询完成,结果已放入Recordset对象中,为什么我清除当前表,会对查询结果有影响?请解释并给一个解决这个问题的方法,不甚感激。
已经上传附件
- Sub 查询()
- Dim sql As String, cnn As Object, rs
- Set cnn = CreateObject("Adodb.Connection")
- cnn.CursorLocation = 3
- Set rs = CreateObject("ADODB.Recordset")
- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
- sql = "Select * From [汇总表$a2:r65536] where 名称='电脑'"
- rs.Open sql, cnn
- Sheet4.Range("a3:r65536").ClearContents
- Sheet4.Cells(20, 1).CopyFromRecordset rs
- rs.Close
- Set rs = Nothing
- Set cnn = Nothing
- MsgBox ("执行完成!")
- End Sub
复制代码
|
|