Copy from recordset errors in ADO

[Spike]

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

 

[Ron de Bruin]

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

 

[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

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

 

[Ron de Bruin]

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

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??

 

[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!

 

[Ron de Bruin]

I am extracting approx 600 rows of data

Which rows?

With the same name in a column or with ????

 

[Spike]

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.

 

[Ron de Bruin]

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

 

[Spike]

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!!!