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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message news:B047573A-34D1-49AE-BCF3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
>> >> >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
>> >>
>>

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" <(E-Mail Removed)> wrote in message news:B047573A-34D1-49AE-BCF3-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
> >> >> >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

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" <(E-Mail Removed)> wrote in message news:323EA418-94E0-4854-9592-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:B047573A-34D1-49AE-BCF3-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
>> >> >> >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 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" <(E-Mail Removed)> wrote in message news:323EA418-94E0-4854-9592-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:B047573A-34D1-49AE-BCF3-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:BD0B324D-BDC7-4C2D-B7B4-(E-Mail Removed)...
> >> >> > 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" <(E-Mail Removed)> wrote in message news0599DC0-F4D7-4D5C-9EF3-(E-Mail Removed)...
> >> >> >> >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
> >> >> >>
> >> >>
> >>
>