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

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

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

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

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

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