View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ron de Bruin Ron de Bruin is offline
external usenet poster
 
Posts: 11,123
Default 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