Word VBA: How to reference a Named Range/Cell from Excel so it returns the row number

Solution: See Macropod's suggestion for solution & make sure you have CorruptLoad:=xlNormalLoad and not CorruptLoad:=xlExtractData

ExtractData = copies and pastes only the data into another workbook and not the Defined Ranges

NormalLoad = business as usual

Currently Exlrow = 20. I want the code to reference "row 20" even if we add in a new row above it and it technically becomes row 21. This way I wouldn't have to update the Exlrow formula every time. I have the whole row named as StartRow.

Let me know if I need to clarify anything!

Edit: In Excel, StartRow is the defined name for row 20. Picture in Excel: My current Named Row Reference

Edit2: When using Macropod's code it works if I just reference a single cell (i.e I20 see image) but when using a named range it causes errors.

    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
ExcelFileName = SaveLocation
Set XlWkBk = objExcel.Workbooks.Open(HCLocation, ReadOnly:=True, CorruptLoad:=xlExtractData)

Set XlWkSht = XlWkBk.Sheets("EPS"): Xlr = XlWkSht.Range("StartRow").row 'This row causing issues

'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

Solution

It seems to me your code could be reduced to something like:

Sub EPS_QTD()
Application.ScreenUpdating = False
' Declare variables
Dim XlApp As New Excel.Application, XlWkBk As Excel.Workbook, XlWkSht As Excel.Worksheet
Dim r As Long, c As Long, Xlr As Long
'Open Excel Workbook
Set XlWkBk = XlApp.Workbooks.Open(HCLocation, ReadOnly:=True, CorruptLoad:=xlExtractData)
Set XlWkSht = XlWkBk.Sheets("EPS"): Xlr = XlWkSht.Range("MyRange").Row
'Do the processing
With ActiveDocument.Tables(1)
  For r = 3 To .Rows.Count
    For c = 2 To .Columns.Count
      If XlWkSht.Cells(Xlr, c + 7).Text <> "" Then .Cell(r, c).Range.Text = XlWkSht.Cells(Xlr, c + 7).Text
    Next
    Xlr = Xlr + 1
  Next
End With
XlWkBk.Close SaveChanges:=False: XlApp.Quit
'Clear variables
Set XlWkSht = Nothing: Set XlWkBk = Nothing: Set XlApp = Nothing
End Sub

where 'MyRange' is a name you give to the row in your workbook at which you want processing to start. Any rows you insert/delete before that will cause the start row to adjust automatically. You could even use your existing 'StartRow' name if the changes occur before that - it doesn't matter if the named range spans multiple rows. You also haven't defined or assigned your HCLocation variable.