In workbook Book1.xls I have a named range "Scores." On Sheet1, the
range looks like the following

Header1 Header2 Header3
1 2 3
a b c

I am reading the data in the range using the code from "Professional
Excel Development" at the bottom of this post.


1. If I define "Scores" as

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

when I try to read the range I get the error message

"The Microsoft Jet database engine could not find the object 'Scores'.
Make sure the object exists and that you spell its name and the path
name correctly."

However, if I define the range as

=Sheet1!$A$1:$C$3

I can read the range without problem. Is there a way to allow "Scores"
to automatically update when new data are added and still be able to
use the code below? I want to avoid hardcoding the address of the data
to be copied.


2. Using the code below, when HDR=YES I get the following when copying
the data

1 2 3

If I have HDR=NO I get the following.

Header1 Header2 Header3
(blank row)
a b c


Is there a reason I can't read both rows of data in the range?


3. If I am using a sheet as the data storage layer in the workbook, is
it good/bad/acceptable practice to use the code below to read stored
data in the same workbook as the code, or should it only be used for
data stored in other workbooks?


Thank you.


Sub QueryWorksheet()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Excel\Book1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"

szSQL = "SELECT * FROM Scores"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText

If Not rsData.EOF Then
Sheet1.Range("A1").CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

rsData.Close
Set rsData = Nothing
End Sub