aspexcel导入access数据库

Sub ImportExcelToAccess()
    Dim db As DAO.Database
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim strSQL As String
    Dim i As Integer
    
    ' 打开 Access 数据库
    Set db = OpenDatabase("C:\path\to\your\database.mdb")
    
    ' 打开 Excel 文件
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\path\to\your\file.xlsx")
    Set xlSheet = xlWB.Sheets(1)
    
    ' 创建 Access 表（如果表不存在）
    strSQL = "CREATE TABLE IF NOT EXISTS ImportedData (ID AUTOINCREMENT, Column1 TEXT(50), Column2 NUMBER)"
    db.Execute strSQL
    
    ' 导入数据
    For i = 1 To xlSheet.UsedRange.Rows.Count
        strSQL = "INSERT INTO ImportedData (Column1, Column2) VALUES ('" & xlSheet.Cells(i, 1).Value & "', " & xlSheet.Cells(i, 2).Value & ")"
        db.Execute strSQL
    Next i
    
    ' 关闭 Excel 和 Access
    xlWB.Close SaveChanges:=False
    xlApp.Quit
    db.Close
    
    Set xlSheet = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing
    Set db = Nothing
End Sub