Word VBA: Running a macro that takes the row number of Named Range for coordinates but seeing error Method 'Range of object'_Worksheet.

[circusnight]

Hey Everyone,

I am in Word VBA trying to populate a table that sources from an Excel file. The Excel file has hardcoded numbers and no formulas at all. There are several worksheets and currently, there is only one Named Ranged, which is StartRow. StartRow is workbook scoped and highlights the entire row 20. When I test the code using cell coordinates (i.e. I20) it works however the moment I change to NamedRanged, whether an entire row or just the cell, it causes the error Method 'Range of object'_Worksheet. One thing I have noticed is that every time the macro opens up the Excel, a repair message pops, even though I have already tried to repair it. Code's purpose is to ensure that I don't have to manually update the row number in case we add/remove rows in Excel. I have posted this in another forum however it seems we may be stuck so wanted to see if anyone else has any ideas (https://stackoverflow.com/q/79324694/25349484). See below the code and picture reference of excel. I cannot download the add-in since I am on work laptop. Let me know if any additional information is needed.

Line highlighted during debugging: Xlr = XlWkSht.Range("StartRow").RefersToRange.Row

Option Explicit
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("StartRow").RefersToRange.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

 

[Joe4]

Welcome to the Board!

For one thing, I don't think that code in the line being highlighted is valid:

Xlr = XlWkSht.Range("StartRow").RefersToRange.Row

If "StartRow" is truly a range with just a single row, then this should work:

Xlr = XlWkSht.Range("StartRow").Row

 

[circusnight]

Hey Joe,

Thanks for the warm welcome. I knew I missed some information when creating this post. Your suggested code was one of the first iterations I tried and the same error pops up.

 

[Joe4]

I just looked over at your other thread, and am trying to follow it.
It seems to suggest that when you made the change, it might appear in your code like this:

Xlr = XlWkSht.Range("StartRow").row

With a lower case "r" at the start of "row".

Can you confirm that? Is that indeed happening?
If so, that means that you probably have a variable, procedure, or function that you named "row", which could be causing confusion and errors (as VBA would not be able to determine when you type "row" whether you are referring to the "Row" property, or your custom variable/procedure/function.

If that is the case, rename your variable/procedure/function. You should NEVER used reserved words like "Row" for those sort of things, as they can create errors and/or unexpected results.


EDIT: I could be off-base, as I am not as familiar with Word VBA as I am Excel VBA. I am not up-to-date on all the differences between the two.

 

[circusnight]

The "r" issue has been resolved. I have run the code using .Row and .row and it works, yet with a Named Range it causes an error. So I am starting to feel like the issue isn't the code but the Named Range itself or perhaps I have to add in some library from Tools - Reference?

 

[circusnight]

The "r" issue has been resolved. I have run the code using .Row and .row and it works, yet with a Named Range it causes an error. So I am starting to feel like the issue isn't the code but the Named Range itself or perhaps I have to add in some library from Tools - Reference?

To provide more context, if the code is referencing that exact cell's coordinates like this: Xlr = XlWkSht.Range("I20").Row it works, yet the moment a Named Range goes in and error.

 

[Joe4]

To provide more context, if the code is referencing that exact cell's coordinates like this: Xlr = XlWkSht.Range("I20").Row it works, yet the moment a Named Range goes in and error.

Maybe Word VBA doesn't like Excel named ranges? I have no idea.
But I see that you have macropod helping you on your other thread. He posts here sometimes too, and is probably one of (if not the most) knowledgeable Word person we have on this site.
So I think he is going to be a much more valuable resource than I am. He is a Word expert - trust what he says!

 

[circusnight]

Hey Joe,

Thanks for the help! Macropod's code definitely works and looks a lot cleaner than what I originally had so no question about that. The solution or at least the fix right now is to set CorruptLoad to xlNormalLoad and not xlExtractData. ExtractData copies and pastes only the data over to a new workbook in an attempt to "fix" it, which means no Defined Range is carried over.