Access VBA导入Excel数据失败：脚本执行后无数据填充

核心错误点 #

1. 起始行选错
   代码里intLine = 6指向的是Excel的表头行，实际数据从第7行开始，导致第一次循环就尝试插入表头内容，后续语法错误直接中断流程。

2. SQL语句语法彻底错误
   拼接INSERT语句时，G列和H列之间缺少逗号，H列与I列之间多了一个单引号且无分隔逗号，导致SQL完全无法执行。加上错误处理里把SQL语法错误（错误码3075）当成“完成”提示，直接误导你以为导入成功。

3. 字符串转义逻辑无效
   原来的Replace(..., "", """")是替换空字符串为双引号，完全起不到SQL转义的作用。SQL里需要把单引号转成两个单引号，避免破坏语句结构。

4. 循环终止条件不合理
   仅靠IsEmpty(xlWs.Cells(intLine, 1))判断停止，而你的Excel文件末尾还有非空的管理数据，要么会错误导入这些无效行，要么会在数据行中间有A列空值时提前终止。

修正后的完整代码 #

Private Sub cmdImportExcel_Click()
    On Error GoTo cmdImportExcel_Click_err:
    
    Dim fdObj As Office.FileDialog
    Dim varfile As Variant
    
    Set fdObj = Application.FileDialog(msoFileDialogFilePicker)
    With fdObj
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel 文件", "*.xls;*.xlsx"
        .Title = "请选择要导入的Excel文件…"
        .Show
    
        If .SelectedItems.Count = 1 Then
            Dim xlApp As Object ' 改用后期绑定，避免版本兼容问题
            Dim xlWb As Object
            Dim xlWs As Object
            Dim intLine As Long
            Dim strSqlDml As String
            Dim strColumnAcleaned As String
            Dim strColumnBcleaned As String
            Dim strColumnCcleaned As String
            Dim strColumnDcleaned As String
            Dim strColumnEcleaned As String
            Dim strColumnFcleaned As String
            Dim strColumnGcleaned As String
            Dim strColumnHcleaned As String
            Dim strColumnIcleaned As String
            Dim strColumnJcleaned As String
            Dim strColumnKcleaned As String
            
            varfile = .SelectedItems(1)
            
            ' 清空现有表数据
            CurrentDb.Execute "DELETE * FROM Sheet", dbFailOnError
            
            ' 初始化Excel应用
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Visible = False
            Set xlWb = xlApp.Workbooks.Open(varfile)
            Set xlWs = xlWb.Worksheets(1)
            intLine = 7 ' 从第7行（数据行）开始循环
            
            Do
                ' 正确转义SQL中的单引号
                strColumnAcleaned = Replace(Nz(xlWs.Cells(intLine, 1).Value2, ""), "'", "''")
                strColumnBcleaned = Replace(Nz(xlWs.Cells(intLine, 2).Value2, ""), "'", "''")
                strColumnCcleaned = Replace(Nz(xlWs.Cells(intLine, 3).Value2, ""), "'", "''")
                strColumnDcleaned = Replace(Nz(xlWs.Cells(intLine, 4).Value2, ""), "'", "''")
                strColumnEcleaned = Replace(Nz(xlWs.Cells(intLine, 5).Value2, ""), "'", "''")
                strColumnFcleaned = Replace(Nz(xlWs.Cells(intLine, 6).Value2, ""), "'", "''")
                ' G列如果是数值类型，不需要加单引号，需确保Access表字段类型匹配
                strColumnGcleaned = Nz(xlWs.Cells(intLine, 7).Value2, 0)
                strColumnHcleaned = Replace(Nz(xlWs.Cells(intLine, 8).Value2, ""), "'", "''")
                strColumnIcleaned = Replace(Nz(xlWs.Cells(intLine, 9).Value2, ""), "'", "''")
                strColumnJcleaned = Replace(Nz(xlWs.Cells(intLine, 10).Value2, ""), "'", "''")
                strColumnKcleaned = Replace(Nz(xlWs.Cells(intLine, 11).Value2, ""), "'", "''")
               
                ' 修正SQL语句的语法错误，确保列之间用逗号分隔，字符串用单引号包裹
                strSqlDml = "INSERT INTO Sheet VALUES('" & strColumnAcleaned & "','" & strColumnBcleaned & "', '" & _
                            strColumnCcleaned & "','" & strColumnDcleaned & "','" & strColumnEcleaned & "','" & _
                            strColumnFcleaned & "', " & strColumnGcleaned & ", '" & strColumnHcleaned & "','" & _
                            strColumnIcleaned & "','" & strColumnJcleaned & "','" & strColumnKcleaned & "')"
            
                CurrentDb.Execute strSqlDml, dbFailOnError
                
                intLine = intLine + 1
                
                ' 优化终止条件：如果当前行A列是管理表头（合并单元格）或为空，则停止
            Loop Until IsEmpty(xlWs.Cells(intLine, 1)) Or xlWs.Cells(intLine, 1).MergeCells = True
            
            xlWb.Close False
            xlApp.Quit
            
            Set xlWs = Nothing
            Set xlWb = Nothing
            Set xlApp = Nothing
    
            DoCmd.OpenTable "Sheet", acViewNormal, acEdit
        Else
            MsgBox("未选择任何文件。")
        End If
    End With
Exit Sub

cmdImportExcel_Click_err:
    ' 如实显示错误信息，避免误导
    MsgBox(Err.Number & " – " & Err.Description, vbCritical + vbOKOnly, "系统错误…")
    ' 清理Excel对象，避免进程残留
    On Error Resume Next
    xlWb.Close False
    xlApp.Quit
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
End Sub

额外说明 #

• 改用后期绑定创建Excel对象，不需要手动添加Excel引用，避免不同Office版本的兼容问题。
• 用Nz函数处理空值，避免单元格为空时出现错误。
• 优化了循环终止条件，判断A列是否为空或是否是合并单元格（对应管理数据行），避免导入无效内容。
• 错误处理部分新增了Excel对象的清理逻辑，防止Excel进程在后台残留。

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