...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..

--
mjschuka
-----------------------------------------------------------------------
mjschukas's Profile: http://www.excelforum.com/member.php...nfo&userid=969
View this thread: http://www.excelforum.com/showthread.php?threadid=39458

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


"mjschukas" wrote:


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

this code works well, BUT I have found that this code doesnt 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...fo&userid=9695
View this thread: http://www.excelforum.com/showthread...hreadid=394584

thank you...!

i will try...

thanks!


K Dales Wrote:
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


"mjschukas" wrote:


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

this code works well, BUT I have found that this code doesnt 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...fo&userid=9695
View this thread:
http://www.excelforum.com/showthread...hreadid=394584




--
mjschukas
------------------------------------------------------------------------
mjschukas's Profile: http://www.excelforum.com/member.php...fo&userid=9695
View this thread: http://www.excelforum.com/showthread...hreadid=394584