I'm not sure if I found a bug or a feature in A2K7 :-). I have code
that exports a query to Excel. The code looks something like:

With ExcelSheet.Application
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
.Cells(1, 1) = "CNC Machine"
.Cells(1, 2) = "Avg. Run-Time Percentage"
strRange = "A2"
strSQL = "qryRunningPercentYTD"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
'MyRS.MoveLast
.Range(strRange.CopyFromRecordset MyRS
DoEvents
Call MilliSleep(1000)
MyRS.Close
Set MyRS = Nothing
...

That code works well. When I uncomment the MyRS.MoveLast line, only
the last record gets exported to the spreadsheet. The help file
suggests that the entire recordset should get transferred, but it
doesn't. Note: I didn't try the export after moving to a random
record in the recordset.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

I don't have Office 2007 installed on this machine, so I can't check, but in
Excel 2010, the Help file entry on CopyFromRecordset explicitly states
"Copying begins at the current row of the Recordset object."

Of course, the snippet you've pasted is invalid code: you've got an opening
parenthesis after the .Range, but no closing one.


"James A. Fortune" wrote in message
news:320b6fc1-c19f-4d9e-868a-cfdfc485bde0 (AT) o15g2000vbe (DOT) googlegroups.com...

I'm not sure if I found a bug or a feature in A2K7 :-). I have code
that exports a query to Excel. The code looks something like:

With ExcelSheet.Application
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
.Cells(1, 1) = "CNC Machine"
.Cells(1, 2) = "Avg. Run-Time Percentage"
strRange = "A2"
strSQL = "qryRunningPercentYTD"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
'MyRS.MoveLast
.Range(strRange.CopyFromRecordset MyRS
DoEvents
Call MilliSleep(1000)
MyRS.Close
Set MyRS = Nothing
...

That code works well. When I uncomment the MyRS.MoveLast line, only
the last record gets exported to the spreadsheet. The help file
suggests that the entire recordset should get transferred, but it
doesn't. Note: I didn't try the export after moving to a random
record in the recordset.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On Sep 12, 5:15*pm, "Douglas J Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
It might be in the A2K7 help file also :-). I'll check the next time
I open A2K7.

Well parsed. I think it should be:

..Range(strRange)
..CopyFromRecordset MyRS

BTW, I see that you and Arvin wrote an Access book.

Thanks,

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com