通过VBA导入Excel至SQL Server遇运行时错误及简化实现需求

一、先解决"Invalid object name 'dbo.Rawdata'"错误 #

• 修正连接字符串：原代码中Server部分存在格式/拼写错误，正确格式需匹配你的SQL Server实例信息：

  con.ConnectionString = _
      "Provider=MSOLEDBSQL;" & _
      "Server=localhost;" & ' 默认实例用此，命名实例改为"Server=localhost\你的实例名"
      "Database=你的实际数据库名;" & _
      "Trusted_Connection=yes;"
  

• 验证表的有效性：
  1. 打开SQL Server Management Studio，连接目标服务器并切换到指定数据库。
  2. 确认dbo.Rawdata表存在，检查表名拼写、数据库是否匹配，以及当前Windows账号是否拥有该表的插入权限。

二、修正批量导入逻辑（适配全列导入+高效处理3万+数据） #

原代码仅插入单一列，且遍历单元格的逻辑错误，以下是适配全列导入的完整修正代码：

Private Sub cmdImport_Click()
    Dim ws As Worksheet
    Set ws = Sheet1 ' 确认是数据所在工作表
    
    ' 动态获取数据范围：从第6行开始到最后一行有数据的行，包含所有列
    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(6, ws.Columns.Count).End(xlToLeft).Column
    Dim dataRange As Range
    Set dataRange = ws.Range(ws.Cells(6, 1), ws.Cells(lastRow, lastCol))
    
    ' 连接SQL Server
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.ConnectionString = _
        "Provider=MSOLEDBSQL;" & _
        "Server=localhost;" & ' 替换为你的服务器/实例名
        "Database=你的数据库名;" & _
        "Trusted_Connection=yes;"
    con.Open
    
    Dim batchSize As Integer
    batchSize = 1000 ' 批量插入大小，可根据性能调整
    Dim iRow As Long, iCol As Integer
    Dim batchInsert As String, rowValues As String
    Dim colName As String
    
    ' 从第5行获取表头（与SQL表列名完全对应）
    Dim colNames As String
    For iCol = 1 To lastCol
        colName = ws.Cells(5, iCol).Value
        colNames = colNames & IIf(iCol > 1, ",", "") & "[" & colName & "]"
    Next iCol
    
    iRow = 0
    batchInsert = ""
    
    ' 逐行处理数据
    For Each rowRange In dataRange.Rows
        iRow = iRow + 1
        rowValues = ""
        
        ' 拼接当前行的所有列值，适配不同数据类型
        For Each cell In rowRange.Cells
            Select Case VarType(cell.Value2)
                Case vbString
                    rowValues = rowValues & IIf(Len(rowValues) > 0, ",", "") & "'" & Replace(cell.Value2, "'", "''") & "'"
                Case vbDate
                    rowValues = rowValues & IIf(Len(rowValues) > 0, ",", "") & "'" & Format(cell.Value2, "yyyy-MM-dd HH:mm:ss") & "'"
                Case vbEmpty, vbNull
                    rowValues = rowValues & IIf(Len(rowValues) > 0, ",", "") & "NULL"
                Case Else
                    rowValues = rowValues & IIf(Len(rowValues) > 0, ",", "") & cell.Value2
            End Select
        Next cell
        
        batchInsert = batchInsert & IIf(iRow > 1, ",", "") & "(" & rowValues & ")"
        
        ' 达到批量大小执行插入
        If iRow = batchSize Then
            con.Execute "INSERT INTO dbo.Rawdata (" & colNames & ") VALUES " & batchInsert
            iRow = 0
            batchInsert = ""
        End If
    Next rowRange
    
    ' 插入剩余的不足一批的数据
    If Len(batchInsert) > 0 Then
        con.Execute "INSERT INTO dbo.Rawdata (" & colNames & ") VALUES " & batchInsert
    End If
    
    MsgBox "数据导入完成，共导入 " & lastRow - 5 & " 条记录"
    con.Close
    Set con = Nothing
End Sub

三、额外优化建议 #

• 关闭屏幕刷新：代码开头加入Application.ScreenUpdating = False，结尾加入Application.ScreenUpdating = True，减少Excel卡顿。
• 添加错误处理：加入On Error GoTo ErrorHandler语句，捕获数据类型不匹配等异常，避免程序直接崩溃。
• 改用SQLBulkCopy：若数据量持续增大（10万+），可使用ADODB.Recordset结合SQLBulkCopy，比拼接SQL语句的插入效率更高。

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