Word VBA：引用Excel命名区域获取行号及报错解决

问题背景 #

我在Word VBA中原本固定将ExlRow设为20，为了实现目标行上方插入新行后仍能自动引用该行（无需手动更新行号），已在Excel中将该行命名为StartRow。但使用代码引用这个命名区域时出现报错，引用单个单元格（如I20）则可正常运行，报错代码行如下：

Set XlWkSht = XlWkBk.Sheets("EPS"): Xlr = XlWkSht.Range("StartRow").row

解决方案 #

问题根源在于打开Excel文件时使用了CorruptLoad:=xlExtractData参数，该模式仅提取数据到新工作簿，不会加载Excel中的定义命名区域。只需将参数改为xlNormalLoad（常规加载模式）即可解决。

参数说明：

• xlExtractData：仅复制粘贴数据到新工作簿，忽略命名区域等结构信息
• xlNormalLoad：常规加载模式，完整加载Excel文件的所有内容，包括命名区域

修改后的完整代码 #

Sub EPS_QTD()
'
' Declare variables
Dim objExcel As New Excel.Application
Dim XlWkBk As Excel.Workbook
Dim ExcelFileName As String
Dim WrdRow As Long
Dim WrdColumn As Long
Dim ExlColumn As Long
Dim ExlRow As Long
Dim tableRow As Integer
Dim tableColumn As Integer
Dim Xlr As Long
Dim XlWkSht As Excel.Worksheet

Application.ScreenUpdating = False

'Open this file - 修改CorruptLoad参数为xlNormalLoad
ExcelFileName = SaveLocation
Set XlWkBk = objExcel.Workbooks.Open(HCLocation, ReadOnly:=True, CorruptLoad:=xlNormalLoad)

Set XlWkSht = XlWkBk.Sheets("EPS"): Xlr = XlWkSht.Range("StartRow").row '现在可正常获取命名区域的行号

'This is to see how many rows/columns are in a table in the Locals Window
tableRow = ActiveDocument.Tables(1).Rows.Count
tableColumn = ActiveDocument.Tables(1).Columns.Count


WrdRow = 3
WrdColumn = 2
ExlColumn = 9

'Will always be 1st table no matter the quarter
'Set the text of the cell from Excel to the cell in the specified table in Word (the first table in this instance)
With ActiveDocument.Tables(1)

For WrdColumn = 2 To .Columns.Count
  ExlRow = Xlr
    
    For WrdRow = 3 To .Rows.Count
            
        If XlWkBk.Sheets("EPS").Cells(ExlRow, ExlColumn).Text = "" Then
        'Need to add in a check to make sure ExlRow is not greater than amount of rows in word
         ExlRow = ExlRow + 1
                
        Else
         .Cell(WrdRow, WrdColumn).Range.Text = XlWkBk.Sheets("EPS").Cells(ExlRow, ExlColumn).Text
         ExlRow = ExlRow + 1
        End If
        
    Next WrdRow
    
    ExlColumn = ExlColumn + 1
 Next WrdColumn
End With

XlWkBk.Close SaveChanges:=False

'Clear variables
Set XlWkBk = Nothing

End Sub

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