Access链接Excel表读取记录触发Run-time error 3349的解决问询

当Access链接的Excel表包含#NA错误单元格时，执行VBA代码读取字段并判断值时会触发Run-time error 3349（数据类型转换错误）。以下是针对该问题的几种解决方案：

原问题代码 #

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT SFDR_SUSTAINABLE, E_SUSTAINABLE, SDG_E_CONTRIBUTION, SDG_S_CONTRIBUTION FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable" 'SFDR Sustainable
    If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust." 'Environmentally Sustainable
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)" 'Sustainable Contribution (Environmental)
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)" 'Sustainable Contribution (Social)
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

End Function

解决方案1：检查字段Status属性 #

DAO字段对象的Status属性可识别Excel错误值，当值为dbStatusInvalidValue时，说明对应单元格是#NA错误。修改代码如下：

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT SFDR_SUSTAINABLE, E_SUSTAINABLE, SDG_E_CONTRIBUTION, SDG_S_CONTRIBUTION FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    ' 仅当字段无错误时执行判断
    If Not rs.Fields("SFDR_SUSTAINABLE").Status = dbStatusInvalidValue Then
        If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable"
    End If
    If Not rs.Fields("E_SUSTAINABLE").Status = dbStatusInvalidValue Then
        If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust."
    End If
    If Not rs.Fields("SDG_E_CONTRIBUTION").Status = dbStatusInvalidValue Then
        If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)"
    End If
    If Not rs.Fields("SDG_S_CONTRIBUTION").Status = dbStatusInvalidValue Then
        If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)"
    End If
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

' 释放对象
Set rs = Nothing
Set db = Nothing
End Function

解决方案2：临时错误捕获 #

使用On Error Resume Next临时跳过错误，执行完判断后恢复默认错误处理：

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT SFDR_SUSTAINABLE, E_SUSTAINABLE, SDG_E_CONTRIBUTION, SDG_S_CONTRIBUTION FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    ' 捕获错误并跳过判断
    On Error Resume Next
    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable"
    On Error GoTo 0 ' 恢复默认错误处理
    
    On Error Resume Next
    If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust."
    On Error GoTo 0
    
    On Error Resume Next
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)"
    On Error GoTo 0
    
    On Error Resume Next
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)"
    On Error GoTo 0
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

Set rs = Nothing
Set db = Nothing
End Function

解决方案3：SQL层面转换错误值 #

在查询中使用Nz函数将#NA错误转换为默认值（如0），避免VBA触发错误：

Dim db As DAO.Database
Dim rs As DAO.Recordset
' 修改SQL，用Nz转换错误值
Set rs = db.OpenRecordset("SELECT Nz(SFDR_SUSTAINABLE, 0) AS SFDR_SUSTAINABLE, " & _
                          "Nz(E_SUSTAINABLE, 0) AS E_SUSTAINABLE, " & _
                          "Nz(SDG_E_CONTRIBUTION, 0) AS SDG_E_CONTRIBUTION, " & _
                          "Nz(SDG_S_CONTRIBUTION, 0) AS SDG_S_CONTRIBUTION " & _
                          "FROM ShareClasses WHERE ShareClassISIN='" & ISIN & "'")

Dim strMsg As String
If rs.RecordCount = 0 Then
    Exit Function
Else
    ' 原判断逻辑可直接使用，无需修改
    If rs.Fields("SFDR_SUSTAINABLE") = 1 Then strMsg = vbNewLine & "SFDR Sustainable"
    If rs.Fields("E_SUSTAINABLE") = 1 Then strMsg = strMsg & vbNewLine & "Envir. Sust."
    If rs.Fields("SDG_E_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Envir.)"
    If rs.Fields("SDG_S_CONTRIBUTION") = 1 Then strMsg = strMsg & vbNewLine & "Sust. Cont. (Social)"
End If

strMsg = Mid(strMsg, 3)
ESG_Rating = strMsg

Set rs = Nothing
Set db = Nothing
End Function

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