Use CopyFromRecordSet to insert named range formulas? (Excel 2000)

[Kel Good]

I am trying to insert a recordset into an Excel worksheet using
CopyFromRecordset. I was hoping for certain cells that my query could simply
insert the text of a named range formula as below:

UPC Calculated Value Of Some Sort
Quantity
Row 00300300303 =GetValueFormula 5

The named range is simply ending up as text in the cell.

Is there any way to do what I'm trying to do?

Thanks.

 

[Wei-Dong XU [MSFT]]

Hi Kel,

Currently I am finding one support professional for you on this issue. If
any update, we will reply here at the first time.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
It is my pleasure to be of assistance.

 

[Kel Good]

Hi Wei-Dong,

Thanks for your reply. I would definitely appreciate an answer for why this
is not working.

I have been able to iterate through all the cells in the range in question,
setting the formula for each cell like so:

cellItem.Formula = cellItem.Formula

When I do this, the named formulas are now recognized. But this is a very
time consuming way to get things working. It should simply work to place the
formula name in the cell via CopyFromRecordset I would think.

Kel

 

[Peter Huang [MSFT]]

Hi

Based on my research, when we use the CopyFromRecordset the load the data
from the database to the cell.
e.g.
=SUM(A1:B1)
it will be recongnized as a text but not a fumular.

Based on my knowledge this is by design, because the cell have never
entered the edit mode.

246335 How To Transfer Data from an ADO Recordset to Excel with Automation
http://support.microsoft.com/?id=246335


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kel Good]

Hi Peter,

Thanks for your reply.

What's odd is the value is still in the Formula property. If after the
CopyFromRecordset statement I iterate over the cells in the range and do the
following, the formula becomes acknowledged:

cellItem.Formula = cellItem.Formula

I find myself why, if the named range statement is already in the Formula
property of the cell like this, it is not acknowledged by the cell.

Kel

 

[Peter Huang [MSFT]]

Hi

Thanks for your knowledge sharing.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Stephen Carter MCP]

I know this is an old post, but I figured someone else may be strugling with this. It is simple:

wbk.wsh.Range("A1:C1").Formula = wbk.wsh.Range("A1:C1").Formula

This will cause excel to recognize the formula contained in the cell. If there is no formula it will ignore it.

Good luck and happy computing.