|
troubles not bringing in the first row of a ado query to another spreadsheet...
...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 |
troubles not bringing in the first row of a ado query to another s
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 |
troubles not bringing in the first row of a ado query to another spreadsheet...
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 |
| All times are GMT +1. The time now is 07:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com