如何使用VBA代码从Excel复制并粘贴到Access (Excel)

Sub CopyDataToAccess()
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    Dim db As Object
    Dim rs As Object
    Dim strSQL As String
    Dim i As Integer
    
    ' 创建Excel对象
    Set xlApp = CreateObject("Excel.Application")
    ' 打开Excel文件
    Set xlWorkbook = xlApp.Workbooks.Open("Excel文件路径")
    ' 选择要复制的工作表
    Set xlWorksheet = xlWorkbook.Sheets("工作表名称")
    
    ' 创建Access对象
    Set db = CreateObject("Access.Application")
    ' 打开Access数据库
    db.OpenCurrentDatabase "Access数据库路径"
    
    ' 创建记录集对象
    Set rs = db.CurrentDb.OpenRecordset("Access表名称")
    
    ' 清空Access表中的数据
    strSQL = "DELETE FROM Access表名称"
    db.CurrentDb.Execute strSQL
    
    ' 复制Excel数据到Access表
    For i = 1 To xlWorksheet.UsedRange.Rows.Count
        rs.AddNew
        rs.Fields("字段1名称") = xlWorksheet.Cells(i, 1).Value
        rs.Fields("字段2名称") = xlWorksheet.Cells(i, 2).Value
        ' 继续添加其他字段的赋值
        rs.Update
    Next i
    
    ' 关闭记录集和数据库对象
    rs.Close
    db.CloseCurrentDatabase
    
    ' 释放对象
    Set rs = Nothing
    Set db = Nothing
    xlWorkbook.Close SaveChanges:=False
    xlApp.Quit
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
    
    MsgBox "数据已成功复制到Access数据库。"
End Sub