|
Copy from recordset errors in ADO
I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77
columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Hi Spike
From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Thank you very much for that, it explains a lot.
Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Hi Spike
I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Probably being a bit dim but do not see what you mean by filter it. I have
to say i thought all the data was the same in each column but obviously not. i am trying to avoid opening it for speed, but if i have to so be it. Yes code would be gratefully received. The file is a daily dump so it changes every day. I can get the files as .csv which they were before and i was not aware of this issue then. If csv will that help or will i have the same issue. I may not be able to get back promptly so please excuse delay if there is one! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
I am extracting approx 600 rows of data
Which rows? With the same name in a column or with ???? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Probably being a bit dim but do not see what you mean by filter it. I have to say i thought all the data was the same in each column but obviously not. i am trying to avoid opening it for speed, but if i have to so be it. Yes code would be gratefully received. The file is a daily dump so it changes every day. I can get the files as .csv which they were before and i was not aware of this issue then. If csv will that help or will i have the same issue. I may not be able to get back promptly so please excuse delay if there is one! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Column A has a fund name in it the rest of the columns hold data relevant to
that fund. There are 35 funds and the number of rows varies per fund. So my SQL statement is pulling in all the columns of data where the fund = say "XYZ" .Hope that makes sense. So i end up with a spreadsheet of 77 columns of data for the fund that the macro has been run for. It may be 500 rows ormore or less as the case may be. -- with kind regards Spike "Ron de Bruin" wrote: I am extracting approx 600 rows of data Which rows? With the same name in a column or with ???? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Probably being a bit dim but do not see what you mean by filter it. I have to say i thought all the data was the same in each column but obviously not. i am trying to avoid opening it for speed, but if i have to so be it. Yes code would be gratefully received. The file is a daily dump so it changes every day. I can get the files as .csv which they were before and i was not aware of this issue then. If csv will that help or will i have the same issue. I may not be able to get back promptly so please excuse delay if there is one! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Hi Spike
OK, see the macros on this page http://www.rondebruin.nl/copy5.htm For workbooks try this macro http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Column A has a fund name in it the rest of the columns hold data relevant to that fund. There are 35 funds and the number of rows varies per fund. So my SQL statement is pulling in all the columns of data where the fund = say "XYZ" .Hope that makes sense. So i end up with a spreadsheet of 77 columns of data for the fund that the macro has been run for. It may be 500 rows ormore or less as the case may be. -- with kind regards Spike "Ron de Bruin" wrote: I am extracting approx 600 rows of data Which rows? With the same name in a column or with ???? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Probably being a bit dim but do not see what you mean by filter it. I have to say i thought all the data was the same in each column but obviously not. i am trying to avoid opening it for speed, but if i have to so be it. Yes code would be gratefully received. The file is a daily dump so it changes every day. I can get the files as .csv which they were before and i was not aware of this issue then. If csv will that help or will i have the same issue. I may not be able to get back promptly so please excuse delay if there is one! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
Copy from recordset errors in ADO
Thank you very much for that it looks what i need. will have a proper go at
it in the morning, i really appreciate your help and patience!!! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike OK, see the macros on this page http://www.rondebruin.nl/copy5.htm For workbooks try this macro http://www.rondebruin.nl/copy5.htm#workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Column A has a fund name in it the rest of the columns hold data relevant to that fund. There are 35 funds and the number of rows varies per fund. So my SQL statement is pulling in all the columns of data where the fund = say "XYZ" .Hope that makes sense. So i end up with a spreadsheet of 77 columns of data for the fund that the macro has been run for. It may be 500 rows ormore or less as the case may be. -- with kind regards Spike "Ron de Bruin" wrote: I am extracting approx 600 rows of data Which rows? With the same name in a column or with ???? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Probably being a bit dim but do not see what you mean by filter it. I have to say i thought all the data was the same in each column but obviously not. i am trying to avoid opening it for speed, but if i have to so be it. Yes code would be gratefully received. The file is a daily dump so it changes every day. I can get the files as .csv which they were before and i was not aware of this issue then. If csv will that help or will i have the same issue. I may not be able to get back promptly so please excuse delay if there is one! -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike I think this have the same problem but I am not sure because I never use it. Why not open and filter the file and copy the data to a new workbook? With code this is not diffecult We can help you with this -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... Thank you very much for that, it explains a lot. Originally I was getting these files as excel.csv files and it was i am sure extracting the data correctly is this right, if so i will get the files in that format. If not does your reply also hold with DAO as well?? -- with kind regards Spike "Ron de Bruin" wrote: Hi Spike From my site: In a Database you cannot mix data types, a column must be all numbers or all text. If there are different data types in the column ADO will copy only the Data type that have the majority. See also http://www.rondebruin.nl/ado.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Spike" wrote in message ... I am using ADO to pull in data from another Excel workbook of 12,600 rows, 77 columns and 2.68 Mb in size. I am extracting approx 600 rows of data and have an issue in that not all the data is coming across, a lot of numbers are missing particularly on the right side and towards the end, whereas the text seems to be fine. I really would appreciate some advice as to what could be causing this, i detail the relevant code below Many thanks With cn '*** use this with bit if .xls file and not cn.open above .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Properties("extended properties").Value = "Excel 8.0" .Open "G:\CLIENTS\XYZ\NAV Workbook\" & XY_Name End With If cn.State < adStateOpen Then Exit Sub Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText If rs.State < adStateOpen Then cn.Close Set cn = Nothing Exit Sub End If ' the field headings For f = 0 To rs.Fields.Count - 1 rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name Next f ' gets data rngTargetCell.Offset(1, 0).CopyFromRecordset rs rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- with kind regards Spike |
| All times are GMT +1. The time now is 01:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com