I can't get the CopyFromRecordset method to start at a specific row
Details
Using Access and Excel 2000, rst is an adodb.recordset in an mdb, range is an excel.range object
"range.CopyFromRecordset rst" works as expected
"range.CopyFromRecordset rst, 10" copies the first 10 records as expected
But after moving to some record, e.g. using "rst.move 5", "range.CopyFromRecordset rst, 10" will still copy the first 10 records of the rst, instead of starting with the 5th record as I want it to
According to http://msdn.microsoft.com/library/de...mRecordset.asp copying should begin at the current row of the recordset. (In fact, the first row becomes the current row when I call the CopyFromRecordset method, don't know why...
Please help! Thanks.

Adi,

this generic routine works for me to dump a recordset to a new workbook
starting at an optional start position, including the field headers, using
ADO 2.7 on Office XP, Win XP.

Sub DumpRecordset(rsName As adodb.Recordset, Optional lStartPos As Long)
Dim W As Workbook
Dim nField As Integer
Set W = ActiveWorkbook
Workbooks.Add
With rsName
For nField = 1 To .Fields.Count
Cells(1, nField).Value = .Fields(nField - 1).Name
Next nField
.MoveFirst
If Not IsEmpty(lStartPos) Then .Move lStartPos
End With
Cells(2, 1).CopyFromRecordset rsName
End Sub

Robin Hammond
www.enhanceddatasystems.com

"adi" wrote in message
...
I can't get the CopyFromRecordset method to start at a specific row.
Details:
Using Access and Excel 2000, rst is an adodb.recordset in an mdb, range is
an excel.range object.
"range.CopyFromRecordset rst" works as expected.
"range.CopyFromRecordset rst, 10" copies the first 10 records as expected.
But after moving to some record, e.g. using "rst.move 5",
"range.CopyFromRecordset rst, 10" will still copy the first 10 records of
the rst, instead of starting with the 5th record as I want it to.
According to
http://msdn.microsoft.com/library/de...mRecordset.asp
copying should begin at the current row of the recordset. (In fact, the
first row becomes the current row when I call the CopyFromRecordset method,
don't know why...)
Please help! Thanks.

hello Robi
I know that it works with DAO, but actually I wanted to use ADO just like in the rest of my app

thanks a lot anywa
adi

Adi,

what I sent is ado. it works, starting at a specific row if done this way.
At least I hope it is because I've been working on related stuff for about
ten months now.

You haven't posted your exact code, so I provided an example that works. My
best guess is that you have an errant movefirst command somewhere in your
code.

Robin Hammond
www.enhanceddatasystems.com

"adi" wrote in message
...
hello Robin
I know that it works with DAO, but actually I wanted to use ADO just like
in the rest of my app.

thanks a lot anyway
adi

Robin,

I can see only 2 differences between our situations:
(1) I'm using Office 2000 (and ado 2.7, winXP just as you are)
(2) My code runs under Access, and an Excel.Application is created.

I have tried an adaptation of your code in my app an it didn't work (i.e., I got all the rows).

Here's a reduced version of my code:
-----------------------------------------------------------
Sub CopyFromRecordsetsetTest()
Dim rst As New ADODB.Recordset
Dim xlWb As Excel.Workbook
Dim xlApp As New Excel.Application
Const fileSpec As String = "C:\test.xls"

With rst
.source = "SELECT * FROM myTable ORDER BY myField"
.CursorLocation = adUseClient
.Open , CurrentProject.Connection, adOpenDynamic, adLockReadOnly
End With
Set xlWb = xlApp.Workbooks.Add
With xlWb
rst.MoveFirst
rst.Move 5
' the 6th row is indeed the current row now, I've checked that
.Sheets(1).Cells(2, 1).CopyFromRecordset rst
' the 1st row is the current row now, and the entire recordset (40 rows) has been copied
.SaveAs fileSpec
End With
Set xlWb = Nothing
rst.Close
Set rst = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
--------------------------------------------------


Here's a DAO version that works as desired (the 'real' version, maybe a bit harder to read):
---------------------------------------------------
Sub ExportRecSetToExcelFileDAO(fileSpec As String, ByRef rst As DAO.Recordset, ByRef bezeichnerArr As Variant, _
xlApp As Excel.Application)
'DAO is used since CopyFromRecordset Method doesn't start at current row in ADO recordsets
'xlMaxRows is the maximum number of rows that fits on an Excel sheet

Dim xlWb As Excel.Workbook
Dim i As Integer
Dim sheetNr As Integer
Dim sheetsNeeded As Integer
Dim recCount As Long

rst.MoveLast 'to get right recordcount
recCount = rst.RecordCount
rst.MoveFirst
If (recCount - 1) Mod xlMaxRows = 0 Then 'first row is for fieldnames
sheetsNeeded = (recCount - 1) / xlMaxRows
Else
sheetsNeeded = (recCount - 1) \ xlMaxRows + 1
End If

Set xlWb = xlApp.Workbooks.Add
For i = xlWb.Sheets.count + 1 To sheetsNeeded
xlWb.Sheets.Add
Next

With xlWb
For sheetNr = 1 To sheetsNeeded
For i = 0 To UBound(bezeichnerArr) 'write fieldnames
With .Sheets(sheetNr)
.Cells(1, i + 1) = bezeichnerArr(i)
.Cells(1, i + 1).Font.Bold = True
.Cells(1, i + 1).Interior.ColorIndex = 15
.Cells(1, i + 1).BorderAround xlContinuous, xlMedium, 56
End With
Next
.Sheets(sheetNr).name = "Daten" & sheetNr
.Sheets(sheetNr).Cells(2, 1).CopyFromRecordset rst, xlMaxRows - 1
.Sheets(sheetNr).Cells.Columns.AutoFit
Next
.SaveAs fileSpec
End With
Set xlWb = Nothing
End Sub
----------------------------------------------------

If you have any idea how to get the ADO version to work, please let me know.
Thank you very much for your help

P.S. I'm a newby in this stuff, I hope I havn't broken to many conventions in my coding...

Hi adi,

You are correct that your code will work from within Excel but not via
Automation. I'm not sure exactly why, but I would guess it has something to
do with marshalling, which occurs when you pass data/references across
processes/threads. From my testing, using a server-side cursor will solve
the problem you're encountering. It seems that the record position gets
lost somewhere in the process when you try to use a client-side cursor.
Since the server-side cursor resides on the server, the record location is
not lost.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


adi wrote:
Robin,

I can see only 2 differences between our situations:
(1) I'm using Office 2000 (and ado 2.7, winXP just as you are)
(2) My code runs under Access, and an Excel.Application is created.

I have tried an adaptation of your code in my app an it didn't work
(i.e., I got all the rows).

Here's a reduced version of my code:
-----------------------------------------------------------
Sub CopyFromRecordsetsetTest()
Dim rst As New ADODB.Recordset
Dim xlWb As Excel.Workbook
Dim xlApp As New Excel.Application
Const fileSpec As String = "C:\test.xls"

With rst
.source = "SELECT * FROM myTable ORDER BY myField"
.CursorLocation = adUseClient
.Open , CurrentProject.Connection, adOpenDynamic, adLockReadOnly
End With
Set xlWb = xlApp.Workbooks.Add
With xlWb
rst.MoveFirst
rst.Move 5
' the 6th row is indeed the current row now, I've checked that
.Sheets(1).Cells(2, 1).CopyFromRecordset rst
' the 1st row is the current row now, and the entire recordset
(40 rows) has been copied .SaveAs fileSpec
End With
Set xlWb = Nothing
rst.Close
Set rst = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
--------------------------------------------------


Here's a DAO version that works as desired (the 'real' version, maybe
a bit harder to read):
---------------------------------------------------
Sub ExportRecSetToExcelFileDAO(fileSpec As String, ByRef rst As
DAO.Recordset, ByRef bezeichnerArr As
Variant, _ xlApp As Excel.Application) 'DAO is used since
CopyFromRecordset Method doesn't start at current row in ADO
recordsets 'xlMaxRows is the maximum number of rows that fits on an
Excel sheet

Dim xlWb As Excel.Workbook
Dim i As Integer
Dim sheetNr As Integer
Dim sheetsNeeded As Integer
Dim recCount As Long

rst.MoveLast 'to get right recordcount
recCount = rst.RecordCount
rst.MoveFirst
If (recCount - 1) Mod xlMaxRows = 0 Then 'first row is for fieldnames
sheetsNeeded = (recCount - 1) / xlMaxRows
Else
sheetsNeeded = (recCount - 1) \ xlMaxRows + 1
End If

Set xlWb = xlApp.Workbooks.Add
For i = xlWb.Sheets.count + 1 To sheetsNeeded
xlWb.Sheets.Add
Next

With xlWb
For sheetNr = 1 To sheetsNeeded
For i = 0 To UBound(bezeichnerArr) 'write fieldnames
With .Sheets(sheetNr)
.Cells(1, i + 1) = bezeichnerArr(i)
.Cells(1, i + 1).Font.Bold = True
.Cells(1, i + 1).Interior.ColorIndex = 15
.Cells(1, i + 1).BorderAround xlContinuous, xlMedium,
56 End With
Next
.Sheets(sheetNr).name = "Daten" & sheetNr
.Sheets(sheetNr).Cells(2, 1).CopyFromRecordset rst, xlMaxRows
- 1 .Sheets(sheetNr).Cells.Columns.AutoFit
Next
.SaveAs fileSpec
End With
Set xlWb = Nothing
End Sub
----------------------------------------------------

If you have any idea how to get the ADO version to work, please let
me know.
Thank you very much for your help

P.S. I'm a newby in this stuff, I hope I havn't broken to many
conventions in my coding...

Hi Jake,
Thanks for your valuable post.
I'm using the recordset.recordcount property in another sub. That wouldn't work with a server-side cursor. How do I get the recordcount with a server-side cursor? Is there any other possibility than using a separate "SELECT Count(*) ... " statement (sounds inefficient)?
Thanks in advance,
adi

Hi adi,

Yes, you can MoveLast, get the RecordCount, then MoveFirst (or wherever you
want) again. Unfortunately, with server-side cursors, the client only knows
the RecordCount after you have reached the end of the Recordset. I don't
know if this would be more efficient in your case than executing another
query; I imagine it would be.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


adi wrote:
Hi Jake,
Thanks for your valuable post.
I'm using the recordset.recordcount property in another sub. That
wouldn't work with a server-side cursor. How do I get the recordcount
with a server-side cursor? Is there any other possibility than using
a separate "SELECT Count(*) ... " statement (sounds inefficient)?
Thanks in advance,
adi

thank you so much for your help

----- Jake Marx wrote: ----

Hi adi

Yes, you can MoveLast, get the RecordCount, then MoveFirst (or wherever yo
want) again. Unfortunately, with server-side cursors, the client only know
the RecordCount after you have reached the end of the Recordset. I don'
know if this would be more efficient in your case than executing anothe
query; I imagine it would be

--
Regards

Jake Mar
MS MVP - Exce
www.longhead.co

[please keep replies in the newsgroup - email address unmonitored


adi wrote
Hi Jake
Thanks for your valuable post
I'm using the recordset.recordcount property in another sub. Tha
wouldn't work with a server-side cursor. How do I get the recordcoun
with a server-side cursor? Is there any other possibility than usin
a separate "SELECT Count(*) ... " statement (sounds inefficient)
Thanks in advance
ad