如何用Excel VBA将ListBox所有数据批量存入MySQL Workbench？

你的代码仅能存入ListBox第一行数据，核心问题有两个：

• 读取ListBox数据的代码在循环外部，只会获取初始值（第一行），循环过程中没有更新每行数据
• 数据库执行语句con.Execute sql在循环外部，仅会执行最后一次循环生成的SQL（实际还是第一行数据）

修改后的完整代码 #

Dim i As Integer ' 声明循环变量
Dim sql As String
Dim RecordsAffected As Long

' 开启事务（若原有代码未添加，必须补上）
con.BeginTrans

' 循环遍历ListBox的每一行数据
For i = 0 To frmMySales.ListBox3.ListCount - 1
    ' 每次循环读取当前行的所有列数据
    db_date = frmMySales.ListBox3.Column(0, i)
    db_product_name = frmMySales.ListBox3.Column(1, i)
    db_product_category = frmMySales.ListBox3.Column(2, i)
    db_customer_name = frmMySales.ListBox3.Column(3, i)
    db_unit_of_sale = frmMySales.ListBox3.Column(4, i)
    db_qty = frmMySales.ListBox3.Column(5, i)
    db_sales_price = frmMySales.ListBox3.Column(6, i)
    db_total_sales = frmMySales.ListBox3.Column(7, i)
    db_profit = frmMySales.ListBox3.Column(8, i)
    db_total_purchases = frmMySales.ListBox3.Column(9, i)
    
    ' 生成当前行的插入SQL语句
    sql = "INSERT INTO mydb_stock.sql_product_sales(sales_transactn_date, invoice_number, " & _
         "product_name_ID, " & _
         "category_ID, " & _
         "customer_ID, " & _
         "unit_ID, " & _
         "product_qty, " & _
         "sales_price, " & _
         "total_sales, " & _
         "total_purchases, " & _
         "profit_loss) " & _
         "VALUES( Now(), '" & db_date & _
         "',(SELECT product_name_ID FROM mydb_stock.sql_product_name WHERE product_name = '" & db_product_name & _
         "'),(SELECT category_ID FROM mydb_stock.sql_product_category WHERE category_name = '" & db_product_category & _
         "'),(SELECT customer_ID FROM mydb_stock.sql_customer WHERE customer_name = '" & db_customer_name & _
         "'),(SELECT unit_ID FROM mydb_stock.sql_product_unit WHERE unit_name = '" & db_unit_of_sale & _
         "')," & db_qty & _
         ",  " & db_sales_price & _
         ",  " & db_total_sales & _
         ",  " & db_total_purchases & _
         ",  " & db_profit & ")"
    
    ' 执行当前行的插入操作
    con.Execute sql, RecordsAffected
Next i

' 提交事务
con.CommitTrans
MsgBox "Transaction Saved", vbDefaultButton2, "Sales Transaction"

关键修改说明 #

• 将读取ListBox列数据的代码移到For循环内部，确保每次循环都获取当前行i的最新数据
• 将con.Execute sql移到循环内部，每遍历一行就执行一次插入，保证所有行数据都被写入数据库
• 新增循环变量i的声明，避免未定义变量导致的潜在问题
• 补充事务开启语句con.BeginTrans，确保事务逻辑完整

额外优化建议 #

• 当前代码直接拼接SQL字符串存在SQL注入风险，建议改用参数化查询，示例如下：

  ' 参数化查询简化示例
  sql = "INSERT INTO mydb_stock.sql_product_sales(sales_transactn_date, invoice_number, product_name_ID) VALUES(Now(), ?, ?)"
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = con
  cmd.CommandText = sql
  cmd.Parameters.Append cmd.CreateParameter("invoice_num", adVarChar, adParamInput, 50, db_date)
  cmd.Parameters.Append cmd.CreateParameter("prod_id", adInteger, adParamInput, , product_id)
  cmd.Execute RecordsAffected
  

• 若ListBox数据量较大，逐行插入效率偏低，可将多行数据合并为单条INSERT语句批量插入，减少数据库交互次数

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