Recordset / Named Range Questions

M

Mark Driscol

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),COUNTA(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
 
T

Tom Ogilvy

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.aspx?scid=kb;en-us;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
 
M

Mark Driscol

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
 

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

SQL not importing records from ACCDB 2
ADO question 15
ADO access to excel workbook on website 1
SQL String 1
Follow up question. Get value of closed file. 5
Problem when copying a range from closed workbooks (ADO) 4
Malformed GUID error 2
Limits in SQL query 3

Top