借助Power Automate将多份Excel表追加导入Access数据库的方案咨询

一、Power Automate 实现导入的可行路径 #

虽然Power Automate没有原生的Access连接器，但可以通过两种方式绕开限制完成批量追加：

1. Office Scripts + Power Automate 组合

   • 在Excel中编写Office Script，通过ADODB连接Access执行数据追加的SQL语句，脚本负责读取当前工作表数据并逐条/批量写入Access。
   • 在Power Automate中设置循环，遍历目标文件夹下所有Excel文件，调用上述脚本完成数据导入。
   • 核心代码示例（Office Script）：

     function main(workbook: ExcelScript.Workbook) {
       const sheet = workbook.getActiveWorksheet();
       const dataRange = sheet.getUsedRange();
       const values = dataRange.getValues();
       const conn = new ActiveXObject("ADODB.Connection");
       // 替换为你的Access文件路径
       conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\YourDB.accdb;");
       
       // 跳过表头，从第二行开始读取数据
       for (let i = 1; i < values.length; i++) {
         // 替换为你的表名和对应字段
         const sql = `INSERT INTO TargetTable (Col1, Col2) VALUES ('${values[i][0]}', '${values[i][1]}')`;
         conn.Execute(sql);
       }
       conn.Close();
     }
     

   • 注意：运行环境需安装Microsoft Access Database Engine驱动。

2. 本地网关 + ODBC 连接

   • 在本地机器配置Access的ODBC数据源，通过Power Automate的本地网关建立连接。
   • 可视化流程步骤：
     • 触发节点：获取指定文件夹下所有Excel文件
     • 循环节点：对每个文件读取数据，使用ODBC连接器执行INSERT语句追加到Access表
   • 优势：无需编写代码；缺点：依赖本地网关，配置复杂度略高。

二、偏好Access时的最优替代方案 #

如果Power Automate的配置成本过高，推荐直接使用VBA宏批量处理：

• 编写VBA脚本，遍历目标文件夹下所有Excel文件，读取数据后直接追加到Access表，全程在本地完成，速度更快。
• 核心代码示例：

  Sub BatchImportToAccess()
    Dim accConn As Object
    Dim excelFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    
    Set accConn = CreateObject("ADODB.Connection")
    ' 替换为你的Access文件路径
    accConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\YourDB.accdb;"
    
    excelFile = Dir("C:\\ExcelFilesFolder\\*.xlsx")
    Do While excelFile <> ""
      Set wb = Workbooks.Open("C:\\ExcelFilesFolder\\" & excelFile)
      Set ws = wb.Sheets(1) ' 假设数据在第一个工作表
      
      ' 从第二行开始读取数据（跳过表头）
      For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        ' 替换为你的表名和对应字段
        Dim sql As String
        sql = "INSERT INTO TargetTable (Col1, Col2) VALUES ('" & ws.Cells(i, 1).Value & "', '" & ws.Cells(i, 2).Value & "')"
        accConn.Execute sql
      Next i
      
      wb.Close SaveChanges:=False
      excelFile = Dir()
    Loop
    
    accConn.Close
    Set accConn = Nothing
    MsgBox "批量导入完成"
  End Sub
  

三、SQL Server 替代方案（可选） #

若可切换至SQL Server，操作会更简便：

• Power Automate有原生SQL Server连接器，可直接读取Excel数据并批量追加到SQL Server表，无需额外配置驱动或网关。
• 后续若需Access使用数据，可通过Access的"链接表"功能连接SQL Server表，实现数据共享。

内容的提问来源于stack exchange，提问作者lberecek