|
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
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 |
Copying the first row of wbooks with ADO
Use the code in the links on top of the page
You have much more control then and it is always working correct. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
Copying the first row of wbooks with ADO
Apologies if I have not made myself clear. The issue at the moment is not to
do with multiple workbooks. It is to sort out what happens if there is a gap in the headers. In the examples supplied if there is a blank header then: rsData.Fields(lCount).Name = the letter F plus the column number where the blank occurred. I cannot have F1 or F2 inserted in the log file, it must remain blank. 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. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
Copying the first row of wbooks with ADO
For the purpose of copying the first row of data - including any gaps in the
source file I put this into your function code and it now permits gaps without filling the cell with F and whatever colnum. For lCount = 0 To rsData.Fields.Count - 1 If Not rsData.Fields(lCount).Name = "F" & 1 + lCount Then TargetRange.Cells(1, 1 + lCount).Value = rsData.Fields(lCount).Name Else TargetRange.Cells(1, 1 + lCount).Value = "" End If Next lCount It still does not answer my question which was how to use the CopyFromRecordset method. It seems to me the recordset starts at the first non blank column of source data which may not be column A. But thank you for the suggestions I am sure the multiple wbook solutions you provide will come in very useful. Geoff "Geoff" wrote: Apologies if I have not made myself clear. The issue at the moment is not to do with multiple workbooks. It is to sort out what happens if there is a gap in the headers. In the examples supplied if there is a blank header then: rsData.Fields(lCount).Name = the letter F plus the column number where the blank occurred. I cannot have F1 or F2 inserted in the log file, it must remain blank. 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. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
Copying the first row of wbooks with ADO
Forget ADO
Use the code that open the workbooks in the links I posted You have full control then http://www.rondebruin.nl/copy3.htm Try example 2 to select the workbook you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... For the purpose of copying the first row of data - including any gaps in the source file I put this into your function code and it now permits gaps without filling the cell with F and whatever colnum. For lCount = 0 To rsData.Fields.Count - 1 If Not rsData.Fields(lCount).Name = "F" & 1 + lCount Then TargetRange.Cells(1, 1 + lCount).Value = rsData.Fields(lCount).Name Else TargetRange.Cells(1, 1 + lCount).Value = "" End If Next lCount It still does not answer my question which was how to use the CopyFromRecordset method. It seems to me the recordset starts at the first non blank column of source data which may not be column A. But thank you for the suggestions I am sure the multiple wbook solutions you provide will come in very useful. Geoff "Geoff" wrote: Apologies if I have not made myself clear. The issue at the moment is not to do with multiple workbooks. It is to sort out what happens if there is a gap in the headers. In the examples supplied if there is a blank header then: rsData.Fields(lCount).Name = the letter F plus the column number where the blank occurred. I cannot have F1 or F2 inserted in the log file, it must remain blank. 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. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
Copying the first row of wbooks with ADO
Thanks, that is something which I can adapt very well.
I will test to see which is the faster, that or the adapted ADO solution. There are times when 40 plus wboooks can arrive with dataheaders in varying columns and greatly differing numbers of records. The first assessment to be made is how the wbooks might be grouped with similar data patterns, hence extracting the data headers if available, to a log file. So speed is of the essence before the real work of extracting relevant data begins. Geoff "Ron de Bruin" wrote: Forget ADO Use the code that open the workbooks in the links I posted You have full control then http://www.rondebruin.nl/copy3.htm Try example 2 to select the workbook you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... For the purpose of copying the first row of data - including any gaps in the source file I put this into your function code and it now permits gaps without filling the cell with F and whatever colnum. For lCount = 0 To rsData.Fields.Count - 1 If Not rsData.Fields(lCount).Name = "F" & 1 + lCount Then TargetRange.Cells(1, 1 + lCount).Value = rsData.Fields(lCount).Name Else TargetRange.Cells(1, 1 + lCount).Value = "" End If Next lCount It still does not answer my question which was how to use the CopyFromRecordset method. It seems to me the recordset starts at the first non blank column of source data which may not be column A. But thank you for the suggestions I am sure the multiple wbook solutions you provide will come in very useful. Geoff "Geoff" wrote: Apologies if I have not made myself clear. The issue at the moment is not to do with multiple workbooks. It is to sort out what happens if there is a gap in the headers. In the examples supplied if there is a blank header then: rsData.Fields(lCount).Name = the letter F plus the column number where the blank occurred. I cannot have F1 or F2 inserted in the log file, it must remain blank. 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. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
Copying the first row of wbooks with ADO
After lots of testing and blank cell A1 issues resolved......
mean time to extract first row from 32 assorted size workbooks:- non ADO = 11.29 seconds with ADO = 2.27 seconds No contest. Geoff "Geoff" wrote: Thanks, that is something which I can adapt very well. I will test to see which is the faster, that or the adapted ADO solution. There are times when 40 plus wboooks can arrive with dataheaders in varying columns and greatly differing numbers of records. The first assessment to be made is how the wbooks might be grouped with similar data patterns, hence extracting the data headers if available, to a log file. So speed is of the essence before the real work of extracting relevant data begins. Geoff "Ron de Bruin" wrote: Forget ADO Use the code that open the workbooks in the links I posted You have full control then http://www.rondebruin.nl/copy3.htm Try example 2 to select the workbook you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... For the purpose of copying the first row of data - including any gaps in the source file I put this into your function code and it now permits gaps without filling the cell with F and whatever colnum. For lCount = 0 To rsData.Fields.Count - 1 If Not rsData.Fields(lCount).Name = "F" & 1 + lCount Then TargetRange.Cells(1, 1 + lCount).Value = rsData.Fields(lCount).Name Else TargetRange.Cells(1, 1 + lCount).Value = "" End If Next lCount It still does not answer my question which was how to use the CopyFromRecordset method. It seems to me the recordset starts at the first non blank column of source data which may not be column A. But thank you for the suggestions I am sure the multiple wbook solutions you provide will come in very useful. Geoff "Geoff" wrote: Apologies if I have not made myself clear. The issue at the moment is not to do with multiple workbooks. It is to sort out what happens if there is a gap in the headers. In the examples supplied if there is a blank header then: rsData.Fields(lCount).Name = the letter F plus the column number where the blank occurred. I cannot have F1 or F2 inserted in the log file, it must remain blank. 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. See the pages below if you want to open the files with code and merge the data Merge data from all workbooks in a folder(Dir) http://www.rondebruin.nl/copy3.htm Merge data from all workbooks in a folder(FSO) http://www.rondebruin.nl/fso.htm RDBMerge Add-in (very easy) http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Geoff" wrote in message ... 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 |
| All times are GMT +1. The time now is 11:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com