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