从Word表格单元格提取数据报类型不匹配错误的技术咨询

解决方案 #

核心修改逻辑如下：

• 你猜测的嵌套子表格是报错主要原因：当「Interest Payment Dates (t)」所在行属于主表格单元格内的嵌套子表时，oRow.Index是子表内部的行号，直接加1取主表单元格会出现索引越界或取值错误
• 修正原代码的单元格取值写法，必须明确取Range.Text才能拿到单元格内的文本内容，否则直接赋值Cell对象会触发类型错误
• 新增嵌套表格检测逻辑，兼容主表、子表两种场景

修改后的完整代码：

Public Function GetCellValue(ByRef pTable As Word.Table, ByVal pRow As Long, ByVal otext As String) As Variant
On Error GoTo Err_GetCellValue

Dim procName As String
Dim retVal As Variant
Dim strValue As String
Dim oRow As Variant
Dim oCell As Variant
Dim found As Boolean
Dim DQ As Variant
Dim targetTable As Word.Table ' 存储目标文本实际所属的表格

procName = "basGeneral::GetCellValue()"
found = False
DQ = Chr(34)

For Each oRow In pTable.Rows
    For Each oCell In oRow.Cells
        ' 遍历当前单元格内的嵌套子表
        If oCell.Tables.Count > 0 Then
            For Each targetTable In oCell.Tables
                Dim subRow As Variant
                For Each subRow In targetTable.Rows
                    Dim subCell As Variant
                    For Each subCell In subRow.Cells
                        If subCell.Range.Text Like otext Then
                            If otext Like "*Interest Payment Dates (t)*" Then
                                ' 从子表取对应位置的单元格文本
                                retVal = targetTable.Cell(subRow.Index + 1, 5).Range.Text
                            Else
                                retVal = targetTable.Cell(subRow.Index, 3).Range.Text
                            End If
                            GoTo ProcessResult
                        End If
                    Next subCell
                Next subRow
            Next targetTable
        End If
        
        ' 主表单元格匹配逻辑
        If oCell.Range.Text Like otext Then
            Set targetTable = pTable
            If otext Like "*Interest Payment Dates (t)*" Then
                retVal = targetTable.Cell(oRow.Index + 1, 5).Range.Text
            Else
                retVal = targetTable.Cell(oRow.Index, 3).Range.Text
            End If
            GoTo ProcessResult
        End If
    Next oCell
Next oRow

ProcessResult:
    retVal = WorksheetFunction.Clean(retVal)
    If IsNumeric(retVal) Then
        retVal = Replace(retVal, DQ, "")
    End If
    ' 如需仅提取黄色高亮内容，可添加以下判断
    ' If subCell.Range.HighlightColorIndex = wdYellow Then
    '    GetCellValue = retVal
    ' End If
    GetCellValue = retVal
    Exit Function
    
Err_GetCellValue:
    ' 异常兜底，可根据需求调整返回值
    GetCellValue = ""
End Function

优化说明：将匹配后的处理逻辑抽为独立分支，避免重复代码，新增异常兜底规则，出现报错时不会直接中断程序运行。

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