Named range in a closed workbook

J

Jac Tremblay

Hi,
I read a post where someone tried to get the number of lines in a named
range in a closed workbook. Unfortunately, there has been no follow up on
that post.
I am interested in knowing if that is possible. I tried the following code:
' ****************************************
Sub NbLinesInRange()
Dim lngNbLines As Long
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Range
Set wbk = Workbooks("C:\TestFile.xls")
Set sht = wbk.Worksheets("Data")
Set rng = sht.Range("TestName")
lngNbLines = rng.Rows.Count
MsgBox "lngNbLines = " & lngNbLines
Set wbk = Nothing
Set sht = Nothing
Set rng = Nothing
End Sub
' ****************************************
I get an error on the line:
Set wbk = Workbooks("C:\TestFile.xls")
I wander if it is a question of missing reference or if is not possible all
together.
The VBA project has a reference on ADO 2.5.
Thanks for any help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference named range in closed workbook 17
Reference a workbook level named variable. 8
Clear Range problem 2
ERROR: subscript out of range 6
Exporting PivotCharts and PivotTables from a workbook without"breaking" them 0
how do i loop thru all the worksheets in 2 files one by one 0
how to code a button to export results of stored procedure to excel file 0
help with/for pivottable code 0

Top