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

[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.
--
- 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 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...fo&userid=9695
> View this thread: http://www.excelforum.com/showthread...hreadid=394584
>
>

[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


"mjschukas" wrote:

>
> ...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...fo&userid=9695
> View this thread: http://www.excelforum.com/showthread...hreadid=394584
>
>