Reading In Excel Sheet Using ADO

[James Shingleton]

I'm currently using an ADO connection to an Excel sheet to read in the entire
sheet (a large sheet) and put it into an internal array so I can perform
operations on it.
The program is currently reading in most of the cells but for some reason
seems to be randomly leaving some blank in the internal array when there
is actually something in that Excel cell. If I copy a cell which has been
read succesfully into the place of one which has not been read in successfully,
it will then be copied across into the array fine. As far as I can see there
is no difference in the formatting of the cells that can and can't be read
in.
Is there something which I am missing out here and does anyone have any suggestions
on how to solve this?
Thanks in advance.
James Shingleton

[James Shingleton]

Hi,
Included below is the code I am using to read in from the sheet and put it
into the array. I've found that it seems to be reading in numbers in certain
columns but not in others. If I put in the numeric values as '20, '21 etc.
will it stop the problem or am I missing the point entirely?

' Open the Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & File & _
";Extended Properties=""Excel 8.0;HDR=No"""
rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
'Copy recordset to an array
recArray = rs.GetRows
'Transpose the array using a function as it is currently wrongly orientated
tempArray = TransposeDim(recArray)

Many thanks
James Shingleton




Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
>
>¤
>¤ I'm currently using an ADO connection to an Excel sheet to read in the
entire
>¤ sheet (a large sheet) and put it into an internal array so I can perform
>¤ operations on it.
>¤ The program is currently reading in most of the cells but for some reason
>¤ seems to be randomly leaving some blank in the internal array when there
>¤ is actually something in that Excel cell. If I copy a cell which has been
>¤ read succesfully into the place of one which has not been read in successfully,
>¤ it will then be copied across into the array fine. As far as I can see
there
>¤ is no difference in the formatting of the cells that can and can't be
read
>¤ in.
>¤ Is there something which I am missing out here and does anyone have any
suggestions
>¤ on how to solve this?
>
>How are you populating the array? You might want to post some code so we
can take a
>look.
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)

[Paul Clement]

On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:

¤
¤ I'm currently using an ADO connection to an Excel sheet to read in the entire
¤ sheet (a large sheet) and put it into an internal array so I can perform
¤ operations on it.
¤ The program is currently reading in most of the cells but for some reason
¤ seems to be randomly leaving some blank in the internal array when there
¤ is actually something in that Excel cell. If I copy a cell which has been
¤ read succesfully into the place of one which has not been read in successfully,
¤ it will then be copied across into the array fine. As far as I can see there
¤ is no difference in the formatting of the cells that can and can't be read
¤ in.
¤ Is there something which I am missing out here and does anyone have any suggestions
¤ on how to solve this?

How are you populating the array? You might want to post some code so we can take a look.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Sue Harsevoort]

Are you checking the recArray before you transpose it? Is the data missing
already then? Have you checked the recordset?

Sue

"James Shingleton" <vb.@127.0.0.1> wrote in message
news:3d6500ce$1@10.1.10.29...
>
> Hi,
> Included below is the code I am using to read in from the sheet and put it
> into the array. I've found that it seems to be reading in numbers in
certain
> columns but not in others. If I put in the numeric values as '20, '21 etc.
> will it stop the problem or am I missing the point entirely?
>
> ' Open the Connection
> cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & File & _
> ";Extended Properties=""Excel 8.0;HDR=No"""
> rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic,
adLockReadOnly
> 'Copy recordset to an array
> recArray = rs.GetRows
> 'Transpose the array using a function as it is currently wrongly
orientated
> tempArray = TransposeDim(recArray)
>
> Many thanks
> James Shingleton
>
>
>
>
> Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
> >On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
> >
> >¤
> >¤ I'm currently using an ADO connection to an Excel sheet to read in the
> entire
> >¤ sheet (a large sheet) and put it into an internal array so I can
perform
> >¤ operations on it.
> >¤ The program is currently reading in most of the cells but for some
reason
> >¤ seems to be randomly leaving some blank in the internal array when
there
> >¤ is actually something in that Excel cell. If I copy a cell which has
been
> >¤ read succesfully into the place of one which has not been read in
successfully,
> >¤ it will then be copied across into the array fine. As far as I can see
> there
> >¤ is no difference in the formatting of the cells that can and can't be
> read
> >¤ in.
> >¤ Is there something which I am missing out here and does anyone have any
> suggestions
> >¤ on how to solve this?
> >
> >How are you populating the array? You might want to post some code so we
> can take a
> >look.
> >
> >
> >Paul ~~~ pclement@ameritech.net
> >Microsoft MVP (Visual Basic)
>

[Chris Hylton]

Add the following to your extended properties in the connection string and
it should fix the problem. ADO has a problem with Excel when the datatypes
within a column are 'mixed', ADO reads the first cell, determines the datatype
and then if subsequent rows have a different datatype in the same column,
ADO gets confused...the following extended property causes ADO to read everything
in as text/string data.

";IMEX=1"

I recently had the same problem working on a project for a guy who didn't
end up paying me (I think he found me through this forum)...I guess someone
should benefit from what I found...hope you aren't working for the same guy
:-)

Good luck,
Chris

"James Shingleton" <vb.@127.0.0.1> wrote:
>
>Hi,
>Included below is the code I am using to read in from the sheet and put
it
>into the array. I've found that it seems to be reading in numbers in certain
>columns but not in others. If I put in the numeric values as '20, '21 etc.
>will it stop the problem or am I missing the point entirely?
>
>' Open the Connection
> cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & File & _
> ";Extended Properties=""Excel 8.0;HDR=No"""
> rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
>'Copy recordset to an array
> recArray = rs.GetRows
>'Transpose the array using a function as it is currently wrongly orientated
> tempArray = TransposeDim(recArray)
>
>Many thanks
>James Shingleton
>
>
>
>
>Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>>On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
>>
>>¤
>>¤ I'm currently using an ADO connection to an Excel sheet to read in the
>entire
>>¤ sheet (a large sheet) and put it into an internal array so I can perform
>>¤ operations on it.
>>¤ The program is currently reading in most of the cells but for some reason
>>¤ seems to be randomly leaving some blank in the internal array when there
>>¤ is actually something in that Excel cell. If I copy a cell which has
been
>>¤ read succesfully into the place of one which has not been read in successfully,
>>¤ it will then be copied across into the array fine. As far as I can see
>there
>>¤ is no difference in the formatting of the cells that can and can't be
>read
>>¤ in.
>>¤ Is there something which I am missing out here and does anyone have any
>suggestions
>>¤ on how to solve this?
>>
>>How are you populating the array? You might want to post some code so we
>can take a
>>look.
>>
>>
>>Paul ~~~ pclement@ameritech.net
>>Microsoft MVP (Visual Basic)
>

[James Shingleton]

Chris,
Many thanks for the tip. It has solved my problem.
Pretty sure I'm not working for the same guy.
Once again many thanks.
James




"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>Add the following to your extended properties in the connection string and
>it should fix the problem. ADO has a problem with Excel when the datatypes
>within a column are 'mixed', ADO reads the first cell, determines the datatype
>and then if subsequent rows have a different datatype in the same column,
>ADO gets confused...the following extended property causes ADO to read everything
>in as text/string data.
>
>";IMEX=1"
>
>I recently had the same problem working on a project for a guy who didn't
>end up paying me (I think he found me through this forum)...I guess someone
>should benefit from what I found...hope you aren't working for the same
guy
>:-)
>
>Good luck,
>Chris
>
>"James Shingleton" <vb.@127.0.0.1> wrote:
>>
>>Hi,
>>Included below is the code I am using to read in from the sheet and put
>it
>>into the array. I've found that it seems to be reading in numbers in certain
>>columns but not in others. If I put in the numeric values as '20, '21 etc.
>>will it stop the problem or am I missing the point entirely?
>>
>>' Open the Connection
>> cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source=" & File & _
>> ";Extended Properties=""Excel 8.0;HDR=No"""
>> rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
>>'Copy recordset to an array
>> recArray = rs.GetRows
>>'Transpose the array using a function as it is currently wrongly orientated
>> tempArray = TransposeDim(recArray)
>>
>>Many thanks
>>James Shingleton
>>
>>
>>
>>
>>Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>>>On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
>>>
>>>¤
>>>¤ I'm currently using an ADO connection to an Excel sheet to read in the
>>entire
>>>¤ sheet (a large sheet) and put it into an internal array so I can perform
>>>¤ operations on it.
>>>¤ The program is currently reading in most of the cells but for some reason
>>>¤ seems to be randomly leaving some blank in the internal array when there
>>>¤ is actually something in that Excel cell. If I copy a cell which has
>been
>>>¤ read succesfully into the place of one which has not been read in successfully,
>>>¤ it will then be copied across into the array fine. As far as I can see
>>there
>>>¤ is no difference in the formatting of the cells that can and can't be
>>read
>>>¤ in.
>>>¤ Is there something which I am missing out here and does anyone have
any
>>suggestions
>>>¤ on how to solve this?
>>>
>>>How are you populating the array? You might want to post some code so
we
>>can take a
>>>look.
>>>
>>>
>>>Paul ~~~ pclement@ameritech.net
>>>Microsoft MVP (Visual Basic)
>>
>