CopyFromRecordset copy&即使Excel中存在多个记录,粘贴也只是前一行 [英] CopyFromRecordset copies & pastes only first one row even though multiple records are present in Excel
本文介绍了CopyFromRecordset copy&即使Excel中存在多个记录,粘贴也只是前一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含数据表的Excel表格
I have an Excel sheet containing table like data
strSQL = "SELECT S.FIELD_NAME1,S.FIELD_NAME2,S.FIELD_NAME3 from [SourceData$A1:IV6] S"
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
cn.Open strCon
Set rs = CmdSqlData.Execute()
Worksheets("SourceData").Cells.ClearContent
Worksheets("AnswerData").Cells(2, 1).CopyFromRecordset rs
结果:
仅忽略第一行和其他记录。
Results :
Only first row and other records are ignored.
我已经尝试过下面的查询。
I have tried below query .,
strSQL = "SELECT COUNT(*) from [SourceData$A1:IV6] S"
哪个给$ 5
作为结果。
请让我知道为什么其他记录不会复制到记录集?
Please let me know why other records not copied into recordset?
推荐答案
这是一个成功粘贴记录集的子例程。
Here's a subroutine that successfully pastes a recordset.
请注意,粘贴到的范围与记录集的大小相同intMaxRow和intMaxCol变量:
Note that the range it pastes to is the same size of the recordset via the intMaxRow and intMaxCol variables:
Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("Customers", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End Sub
使用该示例作为模型,我会尝试像这样的代码:
Using that example as a model, I'd try somehting like this for your code:
strSQL = "SELECT S.FIELD_NAME1,S.FIELD_NAME2,S.FIELD_NAME3 from [SourceData$A1:IV6] S"
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim intMaxCol as Integer
Dim intMaxRow as Integer
cn.Open strCon
Set rs = CmdSqlData.Execute()
intMaxCol = rs.Fields.Count
'- MoveLast/First to get an accurate RecordCount
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 then
'-thought you could put the MoveLast/First here but maybe not.
intMaxRow = rs.RecordCount
With Worksheets("AnswerData")
.Range(.Cells(2,1),.Cells(intMaxRow+1,intMaxColumn)).CopyFromRecordset rs
End With
End If
这篇关于CopyFromRecordset copy&即使Excel中存在多个记录,粘贴也只是前一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文