ADODB.Recordset from workbooks : numbers only?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Thanks NickHK.
I feared as much.
Oh well, more work...

jvl



NickHK said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top