Is it possible to use OLEDB to insert a formula?

Question

I have successfully created an Excel file using OLEDB (after first creating a table / sheet). I would like to insert a formula that sums a Cost (Currency) cell but have been unable find any techniques for doing so.

I have tried inserting the formula into the Cost field as follows but receive an invalid data type error, which is understandable:

cmd.Parameters.Add("P0", OleDbType.PropVariant).Value = "=SUM(K2:K5)"
cmd.CommandText = "INSERT INTO  [" & ExcelTableName & "] (Cost) VALUES(P0);"
cmd.ExecuteNonQuery()

Is there any way of doing this using OLEDB or will I need to use automation?

Perhaps this may help: http://www.java-samples.com/showtutorial.php?tutorialid=1051 — Peter L., Feb 23 '13 at 08:02
[Jet only provides data integration](http://stackoverflow.com/questions/2624012/microsft-jet-oledb-connection-to-excel-loses-formulae). So it is not possible to insert formula. — Alexander, Mar 28 '13 at 08:45
It is possible but not the way you're trying to do it, see my answer — Our Man in Bananas, Jun 26 '13 at 12:41
@Alexander, I agree with your comment. If you care to post it as an answer, I will accept it. — ron tornambe, Jun 26 '13 at 15:16
it **IS POSSIBLE TO INSERT A FORMULA** so long as you put the formula as a column in your query, then use Excel's *CopyFromRecordset* method - **I HAVE DONE THIS MANY MANY TIMES IN THE PAST** — Our Man in Bananas, Jun 26 '13 at 18:58

Our Man in Bananas · Answer 1 · 2013-06-26T19:01:34.153

you could use a SQL Query that includes the formula in a column, and in Excel use an ADO DB Recordset to execute the query.

Then you would use the CopyFromRecordset method on the range object to dump your resultset into Excel which would include the formula.

FOR EXAMPLE:

sQry="Select fields, '=IF(A:A>=B:B, ''''Good'''', ''''Bad'''') from table where criteria'
xlApp.Range("A1").CopyFromRecordset oRs

this can also be done from .NET, just differently...

see MSDN Library: Transfer data to a worksheet by using ADO.NET

Is it possible to use OLEDB to insert a formula?

1 Answers1