Excel VBA导出SQL Server时跳过隔数字段问题排查求助

嘿，你遇到的“跳过每隔一个字段”的问题根源其实很明显——你在字段循环里不小心手动递增了计数器，导致循环步长变成了2！

问题出在哪？ #

看这段核心循环代码：

For ColCounter = 1 To NoOfFields
    'On Error Resume Next
    rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
    ColCounter = ColCounter + 1 ' 就是这行搞的鬼！
Next ColCounter

For...Next循环本身会自动让ColCounter每次加1，而你又手动加了一次ColCounter = ColCounter + 1，这就意味着每次循环ColCounter会直接增加2。比如第一次循环是1，执行完手动加1变成2，然后Next让它变成3，直接跳过了索引2对应的字段，自然就出现了“每隔一个字段就跳过”的现象。

修复后的完整代码 #

只需要删掉那行手动递增ColCounter的代码即可，修复后的代码如下：

Sub testexportsql()
    Dim Cn As ADODB.Connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.Recordset
    Dim RowCounter As Long
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim ColCounter As Integer
    Set rs = New ADODB.Recordset
    
    ServerName = "server_name" ' Enter your server name here
    DatabaseName = "db_name" ' Enter your database name here
    TableName = "customer_master" ' Enter your Table name here
    UserID = "" ' Enter your user ID here
    ' (Leave ID and Password blank if using windows Authentification")
    Password = "" ' Enter your password here
    NoOfFields = 331 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading records
    EndRow = 106695 ' Enter row of last record in sheet
    
    ' CHANGES
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("customer_master")
    '********
    
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
            ";Uid=" & UserID & ";Pwd=" & Password & ";"
    rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic
    
    'EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 1 To NoOfFields
            ' 建议去掉On Error Resume Next，否则会隐藏其他潜在错误
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
            ' 已删除手动递增ColCounter的错误代码行
        Next ColCounter
        Debug.Print RowCounter
    Next RowCounter
    
    rs.UpdateBatch
    
    ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

额外优化建议 #

1. 移除On Error Resume Next：这行代码会屏蔽所有错误提示，万一遇到字段类型不匹配、空值异常等问题，你根本无法定位根源，建议换成针对性的错误处理逻辑。
2. 优先使用字段名而非索引：比如用rs("CustomerName")代替rs(1)，这样即使SQL表的字段顺序发生变化，代码也不会出错，稳定性更强。
3. 校验NoOfFields数值：确保这个数值和工作表的列数、SQL表的字段数完全一致，避免出现越界写入的问题。

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