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

If it is in the same workbook

v = Range("Scores").Value

would put all the data in an array

set rng = Range("Scores").Value
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
v = rng.Address
would put just the data with no headers in an array

Using ADO on an open workbook causes a memory leak as I recall and it seems
1) much more complex

What does it buy you - what are you going to do with it once it is in a
recordset.

http://support.microsoft.com/default...;319998&Produc...
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

If scores is in a closed workbook, then I am not sure it is defined when the
workbook is closed. It isn't something I have tested, but I would not be
surprised that it needs to be in an open workbook to be valid. You migh
consider putting beforeclose code to define a hard coded scores and then save
the workbook. The disadvantage is that you force the workbook to be saved.

Back to your data

Application.ScreenUpdating = False
' suppress any workbook level events in myfile.xls
Application.EnableEvents = False
set bk = workbooks.Open("C:\Myfolder\Myfile.xls")
set rng = bk.Names("Scores").RefersToRange
rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1")
bk.close savechanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True

would be another way to get your data if the workbook where it is stored
doesn't take a long time to open

--
Regards,
Tom Ogilvy



"Mark Driscol" wrote:

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

Thank you, Tom.

The workbook Book1.xls is closed. I could open it and read the range
in question, but I was hoping to avoid this. (Among other reasons, I'm
trying my hand at using recordsets, which I have not used before.) Do
you know why I would get the results mentioned in my second question?
If all the data are numeric or all text, I get correct results.
However, with some rows as text and some as numeric, I don't pull back
all the data.

Thanks.

Mark


Tom Ogilvy wrote:
If it is in the same workbook

v = Range("Scores").Value

would put all the data in an array

set rng = Range("Scores").Value
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
v = rng.Address
would put just the data with no headers in an array

Using ADO on an open workbook causes a memory leak as I recall and it seems
1) much more complex

What does it buy you - what are you going to do with it once it is in a
recordset.

http://support.microsoft.com/default...;319998&Produc...
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

If scores is in a closed workbook, then I am not sure it is defined when the
workbook is closed. It isn't something I have tested, but I would not be
surprised that it needs to be in an open workbook to be valid. You migh
consider putting beforeclose code to define a hard coded scores and then save
the workbook. The disadvantage is that you force the workbook to be saved.

Back to your data

Application.ScreenUpdating = False
' suppress any workbook level events in myfile.xls
Application.EnableEvents = False
set bk = workbooks.Open("C:\Myfolder\Myfile.xls")
set rng = bk.Names("Scores").RefersToRange
rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1")
bk.close savechanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True

would be another way to get your data if the workbook where it is stored
doesn't take a long time to open

--
Regards,
Tom Ogilvy



"Mark Driscol" wrote:

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

su
http://www.dicks-blog.com/archives/2...ed-data-types/

also
http://support.microsoft.com/default...b;en-us;257819
How To Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy

"Mark Driscol" wrote:

Thank you, Tom.

The workbook Book1.xls is closed. I could open it and read the range
in question, but I was hoping to avoid this. (Among other reasons, I'm
trying my hand at using recordsets, which I have not used before.) Do
you know why I would get the results mentioned in my second question?
If all the data are numeric or all text, I get correct results.
However, with some rows as text and some as numeric, I don't pull back
all the data.

Thanks.

Mark


Tom Ogilvy wrote:
If it is in the same workbook

v = Range("Scores").Value

would put all the data in an array

set rng = Range("Scores").Value
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
v = rng.Address
would put just the data with no headers in an array

Using ADO on an open workbook causes a memory leak as I recall and it seems
1) much more complex

What does it buy you - what are you going to do with it once it is in a
recordset.

http://support.microsoft.com/default...;319998&Produc...
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

If scores is in a closed workbook, then I am not sure it is defined when the
workbook is closed. It isn't something I have tested, but I would not be
surprised that it needs to be in an open workbook to be valid. You migh
consider putting beforeclose code to define a hard coded scores and then save
the workbook. The disadvantage is that you force the workbook to be saved.

Back to your data

Application.ScreenUpdating = False
' suppress any workbook level events in myfile.xls
Application.EnableEvents = False
set bk = workbooks.Open("C:\Myfolder\Myfile.xls")
set rng = bk.Names("Scores").RefersToRange
rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1")
bk.close savechanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True

would be another way to get your data if the workbook where it is stored
doesn't take a long time to open

--
Regards,
Tom Ogilvy



"Mark Driscol" wrote:

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

Thank you very much, Tom.

Mark


Tom Ogilvy wrote:
su
http://www.dicks-blog.com/archives/2...ed-data-types/

also
http://support.microsoft.com/default...b;en-us;257819
How To Use ADO with Excel Data from Visual Basic or VBA

--
Regards,
Tom Ogilvy

"Mark Driscol" wrote:

Thank you, Tom.

The workbook Book1.xls is closed. I could open it and read the range
in question, but I was hoping to avoid this. (Among other reasons, I'm
trying my hand at using recordsets, which I have not used before.) Do
you know why I would get the results mentioned in my second question?
If all the data are numeric or all text, I get correct results.
However, with some rows as text and some as numeric, I don't pull back
all the data.

Thanks.

Mark


Tom Ogilvy wrote:
If it is in the same workbook

v = Range("Scores").Value

would put all the data in an array

set rng = Range("Scores").Value
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
v = rng.Address
would put just the data with no headers in an array

Using ADO on an open workbook causes a memory leak as I recall and it seems
1) much more complex

What does it buy you - what are you going to do with it once it is in a
recordset.

http://support.microsoft.com/default...;319998&Produc...
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO

If scores is in a closed workbook, then I am not sure it is defined when the
workbook is closed. It isn't something I have tested, but I would not be
surprised that it needs to be in an open workbook to be valid. You migh
consider putting beforeclose code to define a hard coded scores and then save
the workbook. The disadvantage is that you force the workbook to be saved.

Back to your data

Application.ScreenUpdating = False
' suppress any workbook level events in myfile.xls
Application.EnableEvents = False
set bk = workbooks.Open("C:\Myfolder\Myfile.xls")
set rng = bk.Names("Scores").RefersToRange
rng.copy Thisworkbook.Worksheets("Sheet1").Range("A1")
bk.close savechanges:=False
Application.EnableEvents = True
Application.ScreenUpdating = True

would be another way to get your data if the workbook where it is stored
doesn't take a long time to open

--
Regards,
Tom Ogilvy



"Mark Driscol" wrote:

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