Re: 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" <JVLin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BD2E096-2B83-467F-921B-64EAFA6180C6@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Help - Updating a document
    ... right to the specific data in Access through ADO. ... In fact, using the same technology, you can even do that to Excel ... >to a degree, but it insert a single cell table, not the contents of the cell ... I saved my master as an excel file. ...
    (microsoft.public.word.docmanagement)
  • Re: Excell
    ... Sounds to me that you'd be better off connecting to the file using the Excel ... VBA object model if you need informaiton on the cell presentation properties ... >> ยค I assume this can be done using ADO? ...
    (microsoft.public.vb.database.ado)
  • populate Access numeric field with nothing
    ... i'm having an issue with pushing into Access DB numeric field empty ... value from Excel cell using ADO ...
    (microsoft.public.excel.programming)
  • Re: Remove apostrophe from text (export to Excel)
    ... Why exactly do you need to get rid of it? ... For Excel it defines the cell as ... when using ADO to read that data it should not ...
    (microsoft.public.data.ado)
  • Re: Output form to excel with range ????
    ... here is a long post with lots of useful code for exporting to excel. ... Dim varGetFileName As Variant 'File Name with Full Path ... Set objXLws = objXLWkb.ActiveSheet ... For Each cell In objXLws.Range ...
    (microsoft.public.access.formscoding)