当将访问查询导入到excel表中时出现“无效的过程调用或参数”

Question

我有一个具有链接到Excel表格的数据透视表和图表的Excel工作簿。我想创建一个按钮，当按下时，删除表中的所有数据，并将其替换为访问数据库中保存的查询的更新数据。我写了下面的代码，但当我运行该过程时，出现“无效过程调用或参数”错误。有谁知道我哪里出了问题？

'button on excel form runs procedure to remove current data in table then 
    'import data from query in MS access database 

Sub ImportData() 
    Call CleanTheTable("MMPres_MainData", "MainData") 
    Call ImportAccessData("qry_MMPres_Main", "MMPres_MainData", "MainData" 
End Sub 

    'procedure finds database file and imports query into excel table 

Sub ImportAccessData(qry As String, sht As String, tbl As String) 

On Error GoTo ErrorHandler 

    Const dbLoc As String = "D:\AccessPractice\CTDB\CardiothoracicDB_v2_Current.accdb" 
    Dim db As DAO.Database 
    Dim rs As DAO.Recordset 
    Dim bk As Workbook 
    Dim Wsht As Worksheet 

    Set bk = ActiveWorkbook 
    Set Wsht = bk.Worksheets(sht) 
    Set db = DBEngine.OpenDatabase(dbLoc) 
    Set rs = db.OpenRecordset(qry, dbOpenSnapshot) 

    Wsht.ListObjects(tbl).Range("A1").CopyFromRecordset (rs) 

    rs.Close 
    Set rs = Nothing 
    db.Close 
    Set db = Nothing 
    Set Wsht = Nothing 
    Set bk = Nothing 
    Exit Sub 

ErrorHandler: 
    Application.StatusBar = "" 
    MsgBox ("Error: " & Err.Number & " " & Err.Description) 
    rs.Close 
    Set rs = Nothing 
    db.Close 
    Set db = Nothing 
    Set Wsht = Nothing 
    Set bk = Nothing 

End Sub 

Answer 1

假设你已经为DAO设置必要的库引用，你的错误是在这条线：

Wsht.ListObjects(tbl).Range("A1").CopyFromRecordset (rs) 

把它变成这样：

Wsht.ListObjects(tbl).Range(2, 1).CopyFromRecordset rs 

也有可能：

Wsht.ListObjects(tbl).HeaderRowRange.Offset(1).CopyFromRecordset rs 

总结：

1-除去各处(rs)

2-括号不要重写该表的标头（它是第1行），复制该记录开始于第二行。

3-所述的方法的ListObject的Range比较混乱，它返回表的整个范围内，但不能被直接送入一“A1式”参考，它更像工作表的所述Cells方法。