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

ADODB.Recordset from workbooks : numbers only?
 

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" wrote in message
...
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

ADODB.Recordset from workbooks : numbers only?
 

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" wrote in message
...
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