导入Excel到Access时如何将公式错误值替换为Null避免报错？

核心原理 #

Excel公式返回的错误值（如#N/A、#VALUE!、#DIV/0!等）属于特殊类型，既不能直接赋值给强类型变量，也无法直接拼接成合法的SQL语句，我们只需要在读取单元格时自动判断是否为错误值，替换为Null即可。

第一步：新增两个通用辅助函数 #

把这两个函数放到你代码所在的模块顶部，和ImportSheet同级：

' 读取单元格值，自动替换错误值为Null
Private Function GetCellValue(cellVal As Variant) As Variant
    If IsError(cellVal) Then
        GetCellValue = Null
    Else
        GetCellValue = cellVal
    End If
End Function

' 处理SQL拼接，自动处理Null、单引号转义、日期格式
Private Function FormatSqlValue(val As Variant, Optional isDateType As Boolean = False) As String
    If IsNull(val) Then
        FormatSqlValue = "Null"
        Exit Function
    End If
    If isDateType Then
        FormatSqlValue = "#" & Format(val, "yyyy-mm-dd") & "#"
    Else
        ' 转义内容中的单引号避免SQL语法错误
        FormatSqlValue = "'" & Replace(CStr(val), "'", "''") & "'"
    End If
End Function

第二步：修改ImportSheet主逻辑 #

主要修改3个点：

1. 所有单元格读取都用GetCellValue包裹
2. 把SQL插入逻辑移到循环内部（你原来的写法只会插入最后一个Excel文件的数据）
3. 拼接SQL时用FormatSqlValue处理每个字段值，自动适配Null、字符串、日期格式
   修改后的完整代码如下：

Public Function ImportSheet()
    Dim xl As Object
    ' 以下变量默认都是Variant类型，支持存储Null
    Dim jobno, Address, PM As String
    Dim EDate As Date
    Dim SID As String, SM, SDepth, SCon As String
    Dim SDate As Date
    Dim Sby, SDesc, Tby, Inc, Crack, Crumb As String
    Dim AD1, AD2, AD3, AL1, AL2, AL3 As Double
    Dim SHCID As String
    Dim SHCMass, ILength, IDiam, M0, M1, M2, M3, M4, M5, L0, L1, L2, L3, L4, L5, MC0, MC1, MC2, MC3, MC4, MC5 As Double
    Dim ST0, ST1, ST2, ST3, ST4, ST5, SW0, SW10, SW30, SW1h, SW21h, SW24h, SWih, Esw As Double
    Dim BSWCID As String
    Dim BCMass, BIM, BFM As Double
    Dim ASWCID As String
    Dim ACMass, AIM, AFM, MCI, MCISw, MCFSw, SFS, WD, DD, ISS As Double
        
    Set xl = CreateObject("Excel.Application")
        
    Dim xfileName As Variant
    xfileName = Dir("C:\Users\username\Desktop\Database\Sheets\*.xls")
    
    DoCmd.SetWarnings False
    
    While xfileName <> ""
        With xl.Workbooks.Open(fileName:="C:\Users\username\Desktop\Database\Sheets\" & xfileName)
            With .Sheets("Working Sheet")
                jobno = GetCellValue(.Cells(3, "G").Value)
                Address = GetCellValue(.Cells(3, "C").Value)
                PM = GetCellValue(.Cells(2, "G").Value)
                EDate = GetCellValue(.Cells(4, "C").Value)
                SID = GetCellValue(.Cells(5, "C").Value)
                SM = GetCellValue(.Cells(6, "C").Value)
                SDepth = GetCellValue(.Cells(7, "C").Value)
                SCon = GetCellValue(.Cells(8, "C").Value)
                SDate = GetCellValue(.Cells(5, "G").Value)
                Sby = GetCellValue(.Cells(6, "G").Value)
                SDesc = GetCellValue(.Cells(10, "C").Value)
                Tby = GetCellValue(.Cells(4, "G").Value)
                Inc = GetCellValue(.Cells(7, "G").Value)
                Crack = GetCellValue(.Cells(8, "G").Value)
                Crumb = GetCellValue(.Cells(9, "G").Value)
                AD1 = GetCellValue(.Cells(13, "C").Value)
                AD2 = GetCellValue(.Cells(14, "C").Value)
                AD3 = GetCellValue(.Cells(15, "C").Value)
                AL1 = GetCellValue(.Cells(13, "D").Value)
                AL2 = GetCellValue(.Cells(14, "D").Value)
                AL3 = GetCellValue(.Cells(15, "D").Value)
                SHCID = GetCellValue(.Cells(12, "G").Value)
                SHCMass = GetCellValue(.Cells(13, "G").Value)
                ILength = GetCellValue(.Cells(14, "G").Value)
                IDiam = GetCellValue(.Cells(15, "G").Value)
                M0 = GetCellValue(.Cells(19, "C").Value)
                M1 = GetCellValue(.Cells(20, "C").Value)
                M2 = GetCellValue(.Cells(21, "C").Value)
                M3 = GetCellValue(.Cells(22, "C").Value)
                M4 = GetCellValue(.Cells(23, "C").Value)
                M5 = GetCellValue(.Cells(24, "C").Value)
                L0 = GetCellValue(.Cells(19, "D").Value)
                L1 = GetCellValue(.Cells(20, "D").Value)
                L2 = GetCellValue(.Cells(21, "D").Value)
                L3 = GetCellValue(.Cells(22, "D").Value)
                L4 = GetCellValue(.Cells(23, "D").Value)
                L5 = GetCellValue(.Cells(24, "D").Value)
                MC0 = GetCellValue(.Cells(19, "E").Value)
                MC1 = GetCellValue(.Cells(20, "E").Value)
                MC2 = GetCellValue(.Cells(21, "E").Value)
                MC3 = GetCellValue(.Cells(22, "E").Value)
                MC4 = GetCellValue(.Cells(23, "E").Value)
                MC5 = GetCellValue(.Cells(24, "E").Value)
                ST0 = GetCellValue(.Cells(19, "F").Value)
                ST1 = GetCellValue(.Cells(20, "F").Value)
                ST2 = GetCellValue(.Cells(21, "F").Value)
                ST3 = GetCellValue(.Cells(22, "F").Value)
                ST4 = GetCellValue(.Cells(23, "F").Value)
                ST5 = GetCellValue(.Cells(24, "F").Value)
                SW0 = GetCellValue(.Cells(29, "B").Value)
                SW10 = GetCellValue(.Cells(30, "B").Value)
                SW30 = GetCellValue(.Cells(31, "B").Value)
                SW1h = GetCellValue(.Cells(32, "B").Value)
                SW21h = GetCellValue(.Cells(33, "B").Value)
                SW24h = GetCellValue(.Cells(34, "B").Value)
                SWih = GetCellValue(.Cells(28, "G").Value)
                Esw = GetCellValue(.Cells(34, "G").Value)
                BSWCID = GetCellValue(.Cells(43, "F").Value)
                BCMass = GetCellValue(.Cells(44, "F").Value)
                BIM = GetCellValue(.Cells(45, "F").Value)
                BFM = GetCellValue(.Cells(46, "F").Value)
                ASWCID = GetCellValue(.Cells(43, "G").Value)
                ACMass = GetCellValue(.Cells(44, "G").Value)
                AIM = GetCellValue(.Cells(45, "G").Value)
                AFM = GetCellValue(.Cells(46, "G").Value)
                MCI = GetCellValue(.Cells(50, "D").Value)
                MCISw = GetCellValue(.Cells(51, "D").Value)
                MCFSw = GetCellValue(.Cells(52, "D").Value)
                SFS = GetCellValue(Abs(.Cells(52, "E").Value))
                WD = GetCellValue(.Cells(53, "G").Value)
                DD = GetCellValue(.Cells(54, "G").Value)
                ISS = GetCellValue(.Cells(56, "G").Value)
            End With
            .Close SaveChanges:=False
        End With
        
        ' 拼接SQL
        Dim SQL As String
        SQL = "INSERT INTO Results ( JobNo, Address, PM, EDate, SID, SM, SDepth, SCon, SDate, SBy, SDesc, TBy, Inc, Crack, Crumb, " _
            & "AD1, AD2, AD3, AL1, AL2, AL3, SHCID, SHCMass, ILength, IDiam, M0, M1, M2, M3, M4, M5, L0, " _
            & "L1, L2, L3, L4, L5, MC0, MC1, MC2, MC3, MC4, MC5, ST0, ST1, ST2, ST3, ST4, ST5, SW0, SW10, SW30, SW1h, SW21h, " _
            & "SW24h, SWih, Esw, BSWCID, BCMass, BIM, BFM, ASWCID, ACMass, AIM, AFM, MCI, MCISw, MCFSw, SFS, WD, DD, Iss ) " _
            & "VALUES ( " _
            & FormatSqlValue(jobno) & ", " _
            & FormatSqlValue(Address) & ", " _
            & FormatSqlValue(PM) & ", " _
            & FormatSqlValue(EDate, True) & ", " _
            & FormatSqlValue(SID) & ", " _
            & FormatSqlValue(SM) & ", " _
            & FormatSqlValue(SDepth) & ", " _
            & FormatSqlValue(SCon) & ", " _
            & FormatSqlValue(SDate, True) & ", " _
            & FormatSqlValue(Sby) & ", " _
            & FormatSqlValue(SDesc) & ", " _
            & FormatSqlValue(Tby) & ", " _
            & FormatSqlValue(Inc) & ", " _
            & FormatSqlValue(Crack) & ", " _
            & FormatSqlValue(Crumb) & ", " _
            & FormatSqlValue(AD1) & ", " _
            & FormatSqlValue(AD2) & ", " _
            & FormatSqlValue(AD3) & ", " _
            & FormatSqlValue(AL1) & ", " _
            & FormatSqlValue(AL2) & ", " _
            & FormatSqlValue(AL3) & ", " _
            & FormatSqlValue(SHCID) & ", " _
            & FormatSqlValue(SHCMass) & ", " _
            & FormatSqlValue(ILength) & ", " _
            & FormatSqlValue(IDiam) & ", " _
            & FormatSqlValue(M0) & ", " _
            & FormatSqlValue(M1) & ", " _
            & FormatSqlValue(M2) & ", " _
            & FormatSqlValue(M3) & ", " _
            & FormatSqlValue(M4) & ", " _
            & FormatSqlValue(M5) & ", " _
            & FormatSqlValue(L0) & ", " _
            & FormatSqlValue(L1) & ", " _
            & FormatSqlValue(L2) & ", " _
            & FormatSqlValue(L3) & ", " _
            & FormatSqlValue(L4) & ", " _
            & FormatSqlValue(L5) & ", " _
            & FormatSqlValue(MC0) & ", " _
            & FormatSqlValue(MC1) & ", " _
            & FormatSqlValue(MC2) & ", " _
            & FormatSqlValue(MC3) & ", " _
            & FormatSqlValue(MC4) & ", " _
            & FormatSqlValue(MC5) & ", " _
            & FormatSqlValue(ST0) & ", " _
            & FormatSqlValue(ST1) & ", " _
            & FormatSqlValue(ST2) & ", " _
            & FormatSqlValue(ST3) & ", " _
            & FormatSqlValue(ST4) & ", " _
            & FormatSqlValue(ST5) & ", " _
            & FormatSqlValue(SW0) & ", " _
            & FormatSqlValue(SW10) & ", " _
            & FormatSqlValue(SW30) & ", " _
            & FormatSqlValue(SW1h) & ", " _
            & FormatSqlValue(SW21h) & ", " _
            & FormatSqlValue(SW24h) & ", " _
            & FormatSqlValue(SWih) & ", " _
            & FormatSqlValue(Esw) & ", " _
            & FormatSqlValue(BSWCID) & ", " _
            & FormatSqlValue(BCMass) & ", " _
            & FormatSqlValue(BIM) & ", " _
            & FormatSqlValue(BFM) & ", " _
            & FormatSqlValue(ASWCID) & ", " _
            & FormatSqlValue(ACMass) & ", " _
            & FormatSqlValue(AIM) & ", " _
            & FormatSqlValue(AFM) & ", " _
            & FormatSqlValue(MCI) & ", " _
            & FormatSqlValue(MCISw) & ", " _
            & FormatSqlValue(MCFSw) & ", " _
            & FormatSqlValue(SFS) & ", " _
            & FormatSqlValue(WD) & ", " _
            & FormatSqlValue(DD) & ", " _
            & FormatSqlValue(ISS) & ")"
        
        ' 执行插入
        DoCmd.RunSQL SQL
        
        xfileName = Dir
    Wend
    
    Set xl = Nothing
    DoCmd.SetWarnings True
    MsgBox "Done"
End Function

其他优化说明 #

1. 新增的FormatSqlValue函数自动转义了字符串里的单引号，避免内容里有单引号时出现SQL语法错误
2. 把插入逻辑移到了循环内部，现在可以批量导入所有Excel文件的数据
3. 不需要全局的On Error Resume Next，IsError已经可以处理所有公式错误的情况

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