troubles not bringing in the first row of a ado query to another spreadsheet...

[mjschukas]

...troubles not bringing in the first row of a ado query...

this code works well, BUT I have found that this code doesn’t copy i
the first row of the recordset

???


Code:


Do Until filenameI = ""

strSourceFile = PATH & filenameI

mytab = Mid(filenameI, 1, InStr(1, filenameI, ".") - 1)

strsql = "SELECT * FROM [" & mytab & "$];"

'connection...

Set cn = New ADODB.Connection

'open connection...

cn.Open "DRIVER={Microsoft Excel Drive
(*.xls)};DriverId=790;ReadOnly=True;" & _

"DBQ=" & strSourceFile & ";"

'recordset

Set rs = New ADODB.Recordset

'open rs

rs.Open strsql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

'copy in rs

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.CopyFromRecordset rs

‘clean-up

rs.Delete

rs.Close

filenameI = Dir()

Loop

thank you..

 

[Guest]

I have never tried a CopyFromRecordSet with adOpenForwardOnly; perhaps that
is the issue (Open command reads first record; so the CopyFromRecordset can
only move forward from there??? Just speculating). Try adOpenDynamic and
see if that helps.

 

[mjschukas]

thank you...!

i will try...

thanks!

I have never tried a CopyFromRecordSet with adOpenForwardOnly; perhaps
that
is the issue (Open command reads first record; so the CopyFromRecordset
can
only move forward from there??? Just speculating). Try adOpenDynamic
and
see if that helps.
--
- K Dales

...troubles not bringing in the first row of a ado query...

this code works well, BUT I have found that this code doesn’t copy in
the first row of the recordset

???


Code:


Do Until filenameI = ""

strSourceFile = PATH & filenameI

mytab = Mid(filenameI, 1, InStr(1, filenameI, ".") - 1)

strsql = "SELECT * FROM [" & mytab & "$];"

'connection...

Set cn = New ADODB.Connection

'open connection...

cn.Open "DRIVER={Microsoft Excel Driver
(*.xls)};DriverId=790;ReadOnly=True;" & _

"DBQ=" & strSourceFile & ";"

'recordset

Set rs = New ADODB.Recordset

'open rs

rs.Open strsql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

'copy in rs

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.CopyFromRecordset rs

‘clean-up

rs.Delete

rs.Close

filenameI = Dir()

Loop

thank you...


--
mjschukas
------------------------------------------------------------------------
mjschukas's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9695
View this thread: http://www.excelforum.com/showthread.php?threadid=394584