ADODB.Recordset from workbooks : numbers only?

Hi,

I've only just started playing about with ADODB.Recordsets, specifically to
import data from other workbooks.

I notice though that queries don't return data if the cells contain formulae
or cell references. Similarly, none of the 'PasteSpecial' functionality seems
available. Is this to do with any of the settings I'm using (see last two
lines of the sample code below in particular) or is this a restriction of the
ADODB.Recordset object?

' Create the connection string
stConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stFullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"

'Get data
stSQL = "SELECT * FROM & [DataSheet$A1:E20]"
Set rsData = New ADODB.Recordset
rsData.Open stSQ, stConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

'Paste data
rgRange.CopyFromRecordset rsData

Regards,
JvL

ADO is basically treating you Excel sheet as a table in a database. As such
, formula and cell references have no meaning, only the result in each cell.
..PasteSpecial is member of the Excel library, not ADO. To use it, you have
to get your out of ADO and into Excel first.

NickHK

"JVLin" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I've only just started playing about with ADODB.Recordsets, specifically
to
> import data from other workbooks.
>
> I notice though that queries don't return data if the cells contain
formulae
> or cell references. Similarly, none of the 'PasteSpecial' functionality
seems
> available. Is this to do with any of the settings I'm using (see last two
> lines of the sample code below in particular) or is this a restriction of
the
> ADODB.Recordset object?
>
> ' Create the connection string
> stConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & stFullName & ";" & _
> "Extended Properties=""Excel 8.0;HDR=No"";"
>
> 'Get data
> stSQL = "SELECT * FROM & [DataSheet$A1:E20]"
> Set rsData = New ADODB.Recordset
> rsData.Open stSQ, stConnect, adOpenForwardOnly, adLockReadOnly,
adCmdText
>
> 'Paste data
> rgRange.CopyFromRecordset rsData
>
> Regards,
> JvL
>

Thanks NickHK.
I feared as much.
Oh well, more work...

jvl



"NickHK" wrote:

> ADO is basically treating you Excel sheet as a table in a database. As such
> , formula and cell references have no meaning, only the result in each cell.
> ..PasteSpecial is member of the Excel library, not ADO. To use it, you have
> to get your out of ADO and into Excel first.
>
> NickHK
>
> "JVLin" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I've only just started playing about with ADODB.Recordsets, specifically
> to
> > import data from other workbooks.
> >
> > I notice though that queries don't return data if the cells contain
> formulae
> > or cell references. Similarly, none of the 'PasteSpecial' functionality
> seems
> > available. Is this to do with any of the settings I'm using (see last two
> > lines of the sample code below in particular) or is this a restriction of
> the
> > ADODB.Recordset object?
> >
> > ' Create the connection string
> > stConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & stFullName & ";" & _
> > "Extended Properties=""Excel 8.0;HDR=No"";"
> >
> > 'Get data
> > stSQL = "SELECT * FROM & [DataSheet$A1:E20]"
> > Set rsData = New ADODB.Recordset
> > rsData.Open stSQ, stConnect, adOpenForwardOnly, adLockReadOnly,
> adCmdText
> >
> > 'Paste data
> > rgRange.CopyFromRecordset rsData
> >
> > Regards,
> > JvL
> >
>
>
>