G
Geoff
Hi
I would like to copy the first row of data from a number of wbooks using ADO
but for now the code below is just for 1 source wbook to the Log file.
It copies row 1 data from Test1.xls in the "C" directory to wherever Log.xls
is installed. The code works fine providing there is data in cell A1. But
if the first cell containing data in the source file (test1) is say C1, then
data is still copied into the Log file starting at A1 whereas it ought to be
C1. Empty columns elsewhere in the source file row1 are copied correctly
How can I rectify this?
Is my approach the best way to go about the process for multiple wbooks?
T.I.A.
Geoff
Option Explicit
Option Private Module
Sub Test2()
GetData "C:\Test1.xls", "Sheet1", "A1:IV1"
End Sub
Function GetData(SourceFile As Variant, SourceSheet As String, sourceRange
As _ String)
Dim rs As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
szSQL = "SELECT * FROM [Sheet1$];"
Set rs = New ADODB.Recordset
rs.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Workbooks("Log.xls").Sheets(1).Cells(1, 1).CopyFromRecordset rs, 1, _
rs.Fields.Count
rs.Close
Set rs = Nothing
End Function
I would like to copy the first row of data from a number of wbooks using ADO
but for now the code below is just for 1 source wbook to the Log file.
It copies row 1 data from Test1.xls in the "C" directory to wherever Log.xls
is installed. The code works fine providing there is data in cell A1. But
if the first cell containing data in the source file (test1) is say C1, then
data is still copied into the Log file starting at A1 whereas it ought to be
C1. Empty columns elsewhere in the source file row1 are copied correctly
How can I rectify this?
Is my approach the best way to go about the process for multiple wbooks?
T.I.A.
Geoff
Option Explicit
Option Private Module
Sub Test2()
GetData "C:\Test1.xls", "Sheet1", "A1:IV1"
End Sub
Function GetData(SourceFile As Variant, SourceSheet As String, sourceRange
As _ String)
Dim rs As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
szSQL = "SELECT * FROM [Sheet1$];"
Set rs = New ADODB.Recordset
rs.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Workbooks("Log.xls").Sheets(1).Cells(1, 1).CopyFromRecordset rs, 1, _
rs.Fields.Count
rs.Close
Set rs = Nothing
End Function