Excel formulas not calculating when copied from recordset

1
votes

I am building formulas in a SQL query then using the EXCEL VBA function CopyFromRecordSet to insert the formulas in the spreadsheet. The text of the formula enters correctly but only displays as the text and does not calculate. The calculation mode is set to automatic, and the cell is formatted as "General" and not "Text".

In order to get the formulas to calculate, I have to enter each cell as if I were going to edit the formula and then leave the cell without actually having to edit anything. Just the action of entering and exiting causes the formula to calculate but must be done manually on each cell.

Sub Test1()
  Application.ReferenceStyle = xlR1C1

  Dim qry As String
  qry = "SELECT mYEAR, '=RC[-1]*12' AS xFormula FROM tblYears"

  Dim Datacmd As ADODB.Command
  Set Datacmd = New ADODB.Command

  Datacmd.CommandText = qry

  Dim Datars As ADODB.Recordset
  Set Datars = DB.RunSelect(GV.ConStr_Config, Datacmd)

  ActiveSheet.Range("A1").CopyFromRecordset Datars

  Application.ReferenceStyle = xlA1
End Sub

This results in this...

    1       2
1 2012    =RC[-1]*12
2 2013    =RC[-1]*12
3 2014    =RC[-1]*12

Then I have to double click in each formula cell to start the edit, then I can just click on another cell to exit the formula and it will calculate.

    1       2
1 2012    24144
2 2013    24156
3 2014    24168

I've tried adding the Calculate Now button; and adding the VBA command APPLICATION.CALCULATION=xlCalculationManual at the beginning with APPLICATION.CALCULATE (and CalculateFULL)at the end.

I can't do the calculations in the query, the formulas will need to be interactive with the Excel data in the final product.

(The DB.RunSelect function is my method for executing the query and returning a disconnected recordset.)

Thanks

2
formularecordsetcalculated-columns
You might try flagging the range as in need of recalculation with Range.Dirty. - dlh

2 Answers

0
votes

You will have to iterate the resultset and set formulas like this:

Range("A1").Formula = myFormulaString

Or

Range("A1").FormulaR1C1 = myFormulaString
0
votes

I got into a similar situation. After trying out multiple options, I resorted to the following and it works:

  1. Allow copyFromRecordset to complete
  2. In the next line compute the range of the column where vlookup is applied
  3. Assign the value property of this range to formula property like so:
Worksheets("sheet").range("A1:A10").formula = Worksheets("sheet").range("A1:A10").Value

I guess this process triggers recalculation.