VBA return number of rows from an external workbook

[Andrew.Noble]

Hi

I'm trying to determine the number of rows within a named range in an external workbook, to use in my VBA code.

I have tried the following approach but receive an error:

Dim rangeRows as Integer
Dim rangeAddress as String
rangeAddress = "'C:\Temp\Book1.xlsx'!NamedRange"
rangeRows = Range(rangeAddress).Rows.Count

Thanks for any help.

 

[Worf]

Hi
Please test this:

Sub Andrew()


    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Data2.xlsm")
    MsgBox wb.Names("Adelaide").RefersToRange.Rows.Count
    wb.Close


End Sub

 

[Andrew.Noble]

Hi
Please test this:

Sub Andrew()


    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Data2.xlsm")
    MsgBox wb.Names("Adelaide").RefersToRange.Rows.Count
    wb.Close


End Sub

Worf, thanks for your reply.

The code works fine, except that it has to open the workbook rather than extract the information while it is closed.

I have found a workaround that does the trick for now.