自动将文件夹(现有的和要添加的)中的所有excel数据导入Microsoft Access

Sub ImportExcelToAccess()
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Dim fso As New Scripting.FileSystemObject
    Dim folder As Scripting.Folder
    Dim file As Scripting.File
    Dim xlApp As Object
    Dim xlWB As Object
    Dim strPath As String
    Dim strSheetName As String
    Dim strSQL As String
    
    ' 设置 Access 数据库路径
    strPath = "C:\Path\To\Your\AccessDatabase.accdb"
    
    ' 打开 Access 数据库
    Set db = OpenDatabase(strPath)
    
    ' 设置 Excel 文件夹路径
    Set folder = fso.GetFolder("C:\Path\To\Your\ExcelFiles")
    
    ' 遍历文件夹中的所有 Excel 文件
    For Each file In folder.Files
        If LCase(Right(file.Name, 4)) = ".xls" Or LCase(Right(file.Name, 5)) = ".xlsx" Then
            ' 打开 Excel 文件
            Set xlApp = CreateObject("Excel.Application")
            Set xlWB = xlApp.Workbooks.Open(file.Path)
            
            ' 获取第一个工作表名称
            strSheetName = xlWB.Sheets(1).Name
            
            ' 构建 SQL 导入语句
            strSQL = "SELECT * INTO [" & strSheetName & "] FROM [Excel 8.0;HDR=YES;DATABASE=" & file.Path & "].[Sheet1$]"
            
            ' 执行导入操作
            db.Execute strSQL
            
            ' 关闭 Excel 文件
            xlWB.Close False
            xlApp.Quit
        End If
    Next file
    
    ' 关闭 Access 数据库
    db.Close
End Sub

# 设置 Access 数据库路径
$accessDbPath = "C:\Path\To\Your\AccessDatabase.accdb"

# 设置 Excel 文件夹路径
$excelFolderPath = "C:\Path\To\Your\ExcelFiles"

# 加载 Access 数据库
$accessDb = New-Object -ComObject DAO.Database
$accessDb.Open($accessDbPath)

# 遍历文件夹中的所有 Excel 文件
Get-ChildItem -Path $excelFolderPath -Filter *.xls, *.xlsx | ForEach-Object {
    $excelFilePath = $_.FullName
    $sheetName = [System.IO.Path]::GetFileNameWithoutExtension($_.Name)
    
    # 构建 SQL 导入语句
    $sql = "SELECT * INTO [$sheetName] FROM [Excel 8.0;HDR=YES;DATABASE=$excelFilePath].[Sheet1$]"
    
    # 执行导入操作
    $accessDb.Execute($sql)
}

# 关闭 Access 数据库
$accessDb.Close()