vba listobject CopyFromRecordset

Question

如何将记录集中的数据填充到列表对象中？下面的代码没有完全正常工作：

oCN.ConnectionString = "DRIVER={SQL Server};Server=SRVSQL;Database=TEST;"
oCN.Open
Dim sqlString As String
sqlString = "SELECT * FROM MYTABLE"
oRS.Open sqlString, oCN

With Feuil3.ListObjects("TableArticles")
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If

    ' This make a 91 error
    Call .DataBodyRange.CopyFromRecordset(oRS)
    ' This copy data into sheet, not into listobject
    Call Feuil3.Range("A2").CopyFromRecordset(oRS)
End With

Answer 1

如果总是先删除.DataBodyRange，可以使用`.InsertRowRange‘。

With Feuil3.ListObjects("TableArticles")
    If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
    .InsertRowRange.CopyFromRecordset(oRS)
End With

Answer 2

最后我找到了解决方案。只需调整listobject的大小以适应内容：

With Feuil3.ListObjects("TableArticles")
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If

    Call .Range(2, 1).CopyFromRecordset(oRS)
    Call .Resize(Feuil3.UsedRange)
End With