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

M

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

K Dales

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

mjschukas

thank you...!

i will try...

thanks!


K said:
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 said:
...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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top