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

Try the examples for more workbooks on this page
http://www.rondebruin.nl/ado.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
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

Thank you for the quick response but that's a lot of code to go through
immediately and establish if it answers my question which is what happens if
the first column of source data is empty? Multiple workbooks comes after
I've settled this matter.

Geoff

"Ron de Bruin" wrote:

Try the examples for more workbooks on this page
http://www.rondebruin.nl/ado.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
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

Use the code in the links on top of the page
You have much more control then and it is always working correct.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
Thank you for the quick response but that's a lot of code to go through
immediately and establish if it answers my question which is what happens if
the first column of source data is empty? Multiple workbooks comes after
I've settled this matter.

Geoff

"Ron de Bruin" wrote:

Try the examples for more workbooks on this page
http://www.rondebruin.nl/ado.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
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

Ok, I'll start testing. Thank you.

Geoff

"Ron de Bruin" wrote:

Use the code in the links on top of the page
You have much more control then and it is always working correct.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
Thank you for the quick response but that's a lot of code to go through
immediately and establish if it answers my question which is what happens if
the first column of source data is empty? Multiple workbooks comes after
I've settled this matter.

Geoff

"Ron de Bruin" wrote:

Try the examples for more workbooks on this page
http://www.rondebruin.nl/ado.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
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

Sorry but it doesn't answer my question. After testing the great examples I
find that any blanks in the first row of the source are copied to the ADO
Tester file with the letter F and the column number. Blanks elsewhere in the
data are left blank.
The data files I have to deal with can have column headers at the bottom of
the data table, part way through or even no headers on some columns. This
means there can be blanks on the first row and I would need to ensure they
remain blank as I tidy the data up.
Which is where I started - how do I make sure if cell A1 is blank in the
source that it is replicated in the Log file?

Geoff

"Geoff" wrote:

Ok, I'll start testing. Thank you.

Geoff

"Ron de Bruin" wrote:

Use the code in the links on top of the page
You have much more control then and it is always working correct.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
Thank you for the quick response but that's a lot of code to go through
immediately and establish if it answers my question which is what happens if
the first column of source data is empty? Multiple workbooks comes after
I've settled this matter.

Geoff

"Ron de Bruin" wrote:

Try the examples for more workbooks on this page
http://www.rondebruin.nl/ado.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Geoff" wrote in message ...
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