Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete ADODB Recordset | Excel Programming | |||
ADODB Recordset Function | Excel Programming | |||
0 with ADODB Recordset | Excel Programming | |||
adodb.recordset with excel | Excel Programming | |||
ADODB Recordset | Excel Programming |