Emil,

It seems as though the code that you are using should
work, although I tested the method with Excel 2000. I'd
hate to ask but are you sure your query is returning data?
Can you add a line of code such as
MsgBox adoRecordset.RecordCount
MsgBox adoRecordset.Fields(0).Value
to quickly verify that data is being returned? Also your
code doesn't specify what shtData is, but I'd assumed that
it's defined somewhere otherwise Excel would have given an
error message about that, thus my next question is are you
sure you're looking at the right sheet for results? The
fact that Excel doesn't generate any errors seems to
indicate that Excel is functioning as it should (you don't
have a on error resume next, that's not shown below right?)
I can't spot any errors with your code and can't reproduce
the error so it's making it a little difficult, but I'd
look at the steps above to help debug and pin down the
error.

good luck,
Tim

-----Original Message-----
Hello all.

I am using Excel 2002 and Windows 2000.

I've been working on a Excel VBA program that uses ADO to
get data
from an Access database. Several connections and queries
are made
throughout the program, and I've always just used
ADO's .GetRows
method to assign the data in the recordset to a two-
dimensional
variant array. After that, of course, the data has to be
TRANSPOSEd,
and all is fine.

I thought this was "just how it's done".

Then I stumbled across the Range
object's .CopyFromRecordset method.
Lo and behold, it seemed that I would be able to put my
recordset data
directly into a spreadsheet! No more putting it into a
two-dimensional variant array and TRANSPOSE-ing it!

Alas, after making the adjustments to my code, it does
not seem to
work.

I get no errors at all-- it's just that after the line of
code with
the .CopyFromRecorset method, my spreadsheet is still
blank.

Makes me realize why errors are nice. At least then you
have a
starting point for troubleshooting.

Here is the relevant code:

----------------------------------------------------------
--
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim strConnection As String
Dim strSQL As String

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset

' strFilepath is defined in a module
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" +
strFilepath
adoConnection.Open strConnection

' build the query string
strSQL = "SELECT * FROM ExcelAnalyteGraph WHERE Location
= '" &
lstWell.Text _
& "' AND Name = '" & lstAnalyte.Text

' get the results of the query
adoRecordset.LockType = adLockOptimistic
adoRecordset.CursorLocation = adUseClient
adoRecordset.Open strSQL, adoConnection

With shtData
.Select
.Cells(2, 1).CopyFromRecordset adoRecordset ' after
this, shtData
is still blank!
End With

adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
----------------------------------------------------------
--


Can anyone please tell me what I am missing?

I do not want to go back to the .GetRows/two-dimensional
variant
array/TRANPOSE approach I was previously using!

Thanks in advance,

--emil
.