用VBA从EXCEL导出到Access

Sub ExportToAccess()
    Dim conn As Object
    Dim rs As Object
    Dim dbPath As String
    Dim tableName As String
    Dim rng As Range
    Dim arrData() As Variant
    Dim i As Long, j As Long
    
    ' 设置Access数据库路径和表格名
    dbPath = "C:\path\to\database.accdb"
    tableName = "TableName"
    
    ' 设置Excel数据范围
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:D10")
    
    ' 将数据读取到数组中
    arrData = rng.Value
    
    ' 创建Access数据库连接
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    
    ' 创建记录集对象
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open tableName, conn, 2, 3
    
    ' 清空Access表格数据
    conn.Execute "DELETE FROM " & tableName
    
    ' 将数据插入到Access表格中
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        rs.AddNew
        For j = LBound(arrData, 2) To UBound(arrData, 2)
            rs.Fields(j - 1).Value = arrData(i, j)
        Next j
        rs.Update
    Next i
    
    ' 关闭连接和记录集
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "数据导出成功！"
End Sub