CODEWITHSUNDEEP
rexcel
statespace
statespace

Reputation: 1664

Exporting data from R to Excel: formulas do not recalculate

R(3.0.2) / MSOffice 2013 / Win7

Assume we have an existing data.xlsx file with two sheets - "data" and "calc".

"data" sheet is filled through R with:

require(XLConnect)
df <- data.frame(c(1,2,3,4,5), c(2,3,4,5,6))
wb <- loadWorkbook("data.xlsx", create=F)
setStyleAction(wb, type=XLC$"STYLE_ACTION.NONE")
writeWorksheet(wb, data=df, sheet="data", startRow=1, startCol=1, header=F)
saveWorkbook(wb)

So here comes the problem - I have "calc" sheet that refers to "data" sheet with formulas. For some reason updating the data doesn't get recalculated even though formulas refer to cells that were just filled in.

Calculation option in Excel is turned to automatic, even turning it to manual and pressing F9 to force for recalculation doesn't work.

I've found some odd ways to make this work: 1. select particular cell which has proper formula in it and press enter (for each and every cell) 2. drag formulas over malfunctioning cells over again (overwrite formula with the exact same formula..) 3. Refering to data sheet from other file (say, data2.xlsx) does work but this for me is a last resort option. I don't want to bloat file structure with separate data files. Plus, consider this horrible requirement of opening both file in order to make it work.

Thanks in advance.

Upvotes: 7

Views: 5102

Answers (4)

Dale Kube
Dale Kube

Reputation: 1460

After you load a workbook and write in data, force a formula recalculation before saving the workbook to a directory.

This should work for you:

writeWorksheet(wb, data=df, sheet="data", startRow=1, startCol=1, header=F)
setForceFormulaRecalculation(wb,"data",TRUE)
saveWorkbook(wb)

Upvotes: 1

statespace
statespace

Reputation: 1664

Looking that this link gets some clicks and solution is hidden in comments section, here's the answer:

wb$setForceFormulaRecalculation(T)

This should work for most of packages (at least ones based on rJava) as this java method is passed within excel, hence is not determined by R language.

Upvotes: 5

T-T
T-T

Reputation: 713

I know this post is 2.5 years old, but I just had exact same issue with you. I suspect there are some issues with XLConnect. I used write.csv and it worked. The problem I have with write.csv is that my dataframe has more than 16384 columns and I can't add another sheet to the file.

Upvotes: 0

Massimo Fuccillo
Massimo Fuccillo

Reputation: 337

You can put this VBA macro in a module of your excel document and run (from excel) it after your data transfer.

    Sub RefreshCalculations()
        Calculate
    End Sub

Even simplest: press F9 in excel, this forces calculation of all formulas.

I'm sorry for my previous answer.

this macro works for me, just tested:

Sub refresh2()
Dim sht As Worksheet

Dim rng0 As Range
Dim rng1 As Range
Dim c As Range

'loop through the sheets
For Each sht In ActiveWorkbook.Worksheets

    'limit the search to the UsedRange
    Set rng0 = sht.UsedRange
    On Error Resume Next ' in case of no formulas

    'reduce looping further
    Set rng1 = rng0.SpecialCells(xlCellTypeFormulas)

    For Each c In rng1 'loop through the SpecialCells only
        c.Formula = c.Formula
    Next

Next
End Sub

Upvotes: -1

Related Questions