VBA导出Excel数据到MySQL时运行时错误排查请求

你的VBA代码出错的核心原因是：当前连接的是MySQL数据库，无法在MySQL的SQL语句中直接引用Excel数据源。你写的INSERT INTO feed SELECT * FROM [Excel 12.0;...]语法是用于Excel内部或连接Excel时的查询，不能在MySQL连接上下文里执行。

以下是两种可行的解决方案：

方案一：通过ADODB.Recordset中转数据（通用可靠） #

先把Excel数据读取到ADODB记录集，再批量写入MySQL，兼容性更好：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' 仅当修改Feed工作表时执行，避免无意义触发
    If Sh.Name <> "Feed" Then Exit Sub
    
    Dim mysqlConn As ADODB.Connection
    Set mysqlConn = New ADODB.Connection
    mysqlConn.Open "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=engine;" & _
        "USER=root;" & _
        "PASSWORD=;" & _
        "Option=3"
    
    ' 清空MySQL目标表
    mysqlConn.Execute "DELETE FROM feed"
    
    ' 连接Excel并读取数据
    Dim excelConn As ADODB.Connection
    Dim dataRs As ADODB.Recordset
    Set excelConn = New ADODB.Connection
    excelConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    
    ' 获取实际有数据的行数（比UsedRange更准确）
    Dim lastRow As Integer
    lastRow = Worksheets("Feed").Cells(Rows.Count, "A").End(xlUp).Row
    Set dataRs = excelConn.Execute("SELECT * FROM [Feed$A1:G" & lastRow & "]")
    
    ' 批量插入到MySQL
    If Not dataRs.EOF Then
        dataRs.Open dataRs.Source, mysqlConn, adOpenForwardOnly, adLockOptimistic
        dataRs.UpdateBatch
    End If
    
    ' 清理资源
    dataRs.Close
    excelConn.Close
    mysqlConn.Close
    
    Set dataRs = Nothing
    Set excelConn = Nothing
    Set mysqlConn = Nothing
End Sub

方案二：使用MySQL LOAD DATA INFILE（高效适合大数据） #

如果数据量较大，用MySQL的LOAD DATA命令导入效率更高，需要先把Excel数据导出为临时CSV文件：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name <> "Feed" Then Exit Sub
    
    Dim mysqlConn As ADODB.Connection
    Set mysqlConn = New ADODB.Connection
    mysqlConn.Open "DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
        "SERVER=localhost;" & _
        "DATABASE=engine;" & _
        "USER=root;" & _
        "PASSWORD=;" & _
        "Option=3"
    
    ' 清空目标表
    mysqlConn.Execute "DELETE FROM feed"
    
    ' 导出Excel数据为临时CSV
    Dim tempCsvPath As String
    tempCsvPath = Environ("TEMP") & "\feed_temp.csv"
    Worksheets("Feed").UsedRange.Export Filename:=tempCsvPath, FileFormat:=xlCSV, Local:=True
    
    ' 生成LOAD DATA语句（注意路径转义为双反斜杠）
    Dim sqlStr As String
    sqlStr = "LOAD DATA LOCAL INFILE '" & Replace(tempCsvPath, "\", "\\") & "' " & _
             "INTO TABLE feed " & _
             "FIELDS TERMINATED BY ',' " & _
             "ENCLOSED BY '""' " & _
             "LINES TERMINATED BY '\r\n' " & _
             "IGNORE 1 ROWS;" ' 跳过CSV表头
    
    mysqlConn.Execute sqlStr
    
    ' 删除临时文件
    Kill tempCsvPath
    
    mysqlConn.Close
    Set mysqlConn = Nothing
End Sub

额外注意事项 #

• 确保你的电脑安装了Microsoft Access Database Engine（ACE驱动），否则方案一的Excel连接会失败。
• 方案二中需要确保MySQL服务器允许LOCAL INFILE，可以在my.ini中设置local_infile=ON，或者连接字符串中添加ALLOW LOCAL INFILE=1参数。
• Workbook_SheetChange事件会在单元格修改时频繁触发，建议添加更多判断（比如只在修改特定区域时执行），避免重复操作。

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