Lotus Notes拆分多值字段时出现类型不匹配错误求助

错误根源 #

你遇到的「类型不匹配」错误，直接原因是**arrUM是GetItemValue返回的Variant数组**（Lotus Notes多值字段调用GetItemValue会返回数组，哪怕只有一个值），而Split函数要求输入参数是字符串类型，直接把数组传给Split就会触发类型不匹配。

修复步骤 #

1. 正确拆分多值字段值 #

先从arrUM数组中取出目标元素（比如第0位），转成字符串后再调用Split，同时处理字段为空的情况：

arrRecdQty = doc.GetItemValue("fldRecdQty")
arrUM = doc.GetItemValue("fldUM")
' 先处理arrUM为空的情况，再取第0位转字符串拆分
Dim umStr As String
umStr = ""
If UBound(arrUM) >= 0 Then
    umStr = CStr(arrUM(0))
End If
arr = Split(umStr, ",")

2. 修正循环计数逻辑 #

原来的Do Until arr=""完全错误，数组不能和空字符串比较，应该用UBound获取数组的最大索引来确定循环次数：

Dim n As Integer
n = -1
' 如果拆分后的数组不为空，获取其最大索引
If Not IsEmpty(arr) Then
    n = UBound(arr)
End If

3. 其他潜在问题修复 #

• 后续For l=0 To n循环要先判断n >= 0，避免循环次数无效报错
• 循环结束后l的值为n+1，原代码中调用doc.GetItemValue("fldRemarks_" + CStr(l + 1))(0)会导致字段名错误，需调整逻辑匹配实际字段命名规则
• 多值字段arrPartNo、arrRecdQty的索引要增加越界判断，避免数组访问错误

修正后的核心代码片段 #

If Trim(doc.GetItemValue("fldPartNo_2")(0)) <> "" Then
    arrPartNo = ArrayAppend(doc.GetItemValue("fldPartNo_1"), doc.GetItemValue("fldPartNo_2"))
Else
    arrPartNo = doc.GetItemValue("fldPartNo_1")
End If

arrRecdQty = doc.GetItemValue("fldRecdQty")
arrUM = doc.GetItemValue("fldUM")
Dim umStr As String
umStr = ""
If UBound(arrUM) >= 0 Then
    umStr = CStr(arrUM(0))
End If
arr = Split(umStr, ",")

Dim n As Integer
n = -1
If Not IsEmpty(arr) Then
    n = UBound(arr)
End If

sno = sno + 1
worksheet.cells(i,j).value= doc.Universalid
j=j+1
worksheet.cells(i,j).value=sno
j=j+1

' 仅当拆分后有有效数据时执行循环
If n >= 0 Then
    For l=0 To n
        ' 增加数组越界判断，避免访问错误
        If l <= UBound(arrPartNo) Then
            worksheet.cells(i,j).value= arrPartNo(l)
        Else
            worksheet.cells(i,j).value= ""
        End If
        j=j+1
        
        If l <= UBound(arrUM) Then
            worksheet.cells(i,j).value= arrUM(l)
        Else
            worksheet.cells(i,j).value= ""
        End If
        j=j+1
        
        If l <= UBound(arrRecdQty) Then
            worksheet.cells(i,j).value= arrRecdQty(l)
        Else
            worksheet.cells(i,j).value= ""
        End If
        j=j+1
        
        ' 处理拒收数量字段
        Dim rejectItem As Variant
        rejectItem = doc.Getitemvalue("fldReject_" + CStr(l+1))
        If UBound(rejectItem) >=0 Then
            worksheet.cells(i,j).value= rejectItem(0)
        Else
            worksheet.cells(i,j).value= ""
        End If
        j=j+1

        ' 计算合格数量
        Dim recdQtyVal As String
        recdQtyVal = ""
        If l <= UBound(arrRecdQty) Then
            recdQtyVal = Trim(CStr(arrRecdQty(l)))
        End If
        If IsNumeric(recdQtyVal) Then
            Dim rejectVal As String
            rejectVal = ""
            If UBound(rejectItem) >=0 Then
                rejectVal = Trim(CStr(rejectItem(0)))
            End If
            If IsNumeric(rejectVal) Then
                worksheet.cells(i,j).value= CDbl(recdQtyVal) - CDbl(rejectVal)
            Else
                worksheet.cells(i,j).value= CDbl(recdQtyVal)
            End If
        Else
            worksheet.cells(i,j).value= ""
        End If
        j=j+1
    Next l
End If

' 调整备注字段的索引逻辑，匹配实际字段命名规则
Dim remarksItem As Variant
remarksItem = doc.GetItemValue("fldRemarks_" + CStr(n + 2))
If UBound(remarksItem) >=0 Then
    worksheet.cells(i,j).value= remarksItem(0)
Else
    worksheet.cells(i,j).value= ""
End If
j=j+1
worksheet.cells(i,j).value= "N_MRN"
j=j+1
worksheet.cells(i,j).value= ""
j=j+1
worksheet.cells(i,j).value= ""
j=j+1
worksheet.cells(i,j).value= "True"
j=j+1
Set doc = vw.Getnextdocument(doc)
j = j + 1   
sno=0   
i = i + 1
j = 1   
Wend

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