Insert a function / formula in Excel from R and VB

问题:

I am looking for a way to insert a formula/function into Excel from R. I went through all the packages and the best I could do was:

library(xlsx)
wb = createWorkbook(type = "xlsx")
sh = createSheet(wb, "CANFI")
cell = CellBlock(sheet = sh, startRow = 2, startColumn = 2, noRows = 1, noColumns = 1)
CB.setMatrixData(cellBlock = cell, x = as.matrix('=SUM(A1:A2)'), startRow = 1, startColumn = 1)
saveWorkbook(wb, file =  "./test.xlsx")

This gave me a file, with the formula written in the cell SUM(A1:A2), as if it was a string in Excel. If you click on the cell and press enter, then the formula computes the result. I suppose a way of doing the trick would be to use VBA code that would refresh, but the usual select, refreshall etc. did not work.

回答1:

I was able to find a proper solution using the suggestion from @n8. The idea was to use a VBA code that will do a text to column on all the workbook and all the columns. Then call this VBA code from R, using a VBscript.

Here is the R code :

library(xlsx)
wb = loadWorkbook("./source/template.xlsm")
sh   = wb$getSheet("CAN.FI")
cell = CellBlock(sheet = sh, startRow = 2, startColumn = 2, noRows = 1, noColumns = 1)
CB.setMatrixData(cellBlock = cell, x = as.matrix('=SUM(A1:A2)'), startRow = 1, startColumn = 1)

cell = CellBlock(sheet = sh, startRow = 4, startColumn = 4, noRows = 1, noColumns = 1)
CB.setMatrixData(cellBlock = cell, x = as.matrix('=SUM(A1:A2)'), startRow = 1, startColumn = 1)
saveWorkbook(wb, file =  "./test.xlsm")

path_to_vbs_file = "./text_to_column.vbs"
shell(shQuote(normalizePath(path_to_vbs_file)), "cscript", flag = "//nologo")

The file template.xlsm is a .xlsm workbook empty with one sheet CAN.FI, the book has an embedded macro/module called "text_to_column()" The macro is as followed :

Sub text_to_column()
Application.ScreenUpdating = False
On Error Resume Next
For Each wksht In ActiveWorkbook.Worksheets
 wksht.activate
     For Each col In wksht.Columns
         Columns(col.Column).TextToColumns _
         Destination:=Cells(1, col.Column), _
         DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, _
         ConsecutiveDelimiter:=False, _
         Tab:=True, _
         Semicolon:=False, _
         Comma:=False, _
         Space:=False, _
         Other:=False, _
         FieldInfo:=Array(1, 1), _
         TrailingMinusNumbers:=True
     Next col
Next wksht
End Sub

You will notice at the end of the R code the

path_to_vbs_file = "./text_to_column.vbs"

This points to the vbscript which is just a .vbs file. One can create it from a text file and change the extension. I followed the thread : How To create a vbscript Stackoverflow. My .vbs file is named : text_to_column.vbs and looks like this :

Option Explicit

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open(".\test.xlsm", 0, True) 
  xlApp.Application.Visible = False
  xlApp.DisplayAlerts = False
  xlApp.Run "text_to_column"
  xlApp.ActiveWorkbook.SaveAs ".\test filled.xlsx", 51

  xlApp.ActiveWorkbook.Close
  xlApp.Quit

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 

The vb script will open the file test.xlsm run the VBA macro "text_to_column" and then save the file in an xlsx format under the name "test filled.xlsx"

The vbscript is run from the last line in R.

Long story short, the code R will open the template, fill with the formula in string format, call the vbscript that will run the macro transforming the formula. The final file with the formulas in proper format will be then saved.

On a side note, if you want to write formula, you might consider the usage of ADDRESS and INDIRECT which allows you to use row and column numbers, which is easier than writing A1 B2 etc. one example could be :

SUM(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(5,1)))

Which will do the sum of A1:A5.

Thanks again for the help, @n8. Hope that will help people.

Romain.

回答2:

XLConnect and openxlsx packages seem better suited for dealing with formulas. Not sure about VBA script though.

Quoting an example from openxlsx document.

> library(openxlsx)
> wb <- createWorkbook()
> addWorksheet(wb, "Sheet 1")
> writeData(wb, "Sheet 1", x = iris)
> v <- c("SUM(A2:A151)", "AVERAGE(B2:B151)") ## skip header row
> writeFormula(wb, sheet = 1, x = v, startCol = 10, startRow = 2)
> writeFormula(wb, 1, x = "A2 + B2", startCol = 10, startRow = 10)
> saveWorkbook(wb, "writeFormulaExample.xlsx", overwrite = TRUE)

Similarly for XLConnect,

> library(XLConnect)
> wb <- loadWorkbook("cellFormula.xlsx", create = TRUE)
> createSheet(wb, "Formula")
> setCellFormula(wb, "Formula", 1, 1, "SUM($B$1:$B$29)")
> getCellFormula(wb, "Formula", 1, 1)
          Formula 
"SUM($B$1:$B$29)" 
> getCellFormula(wb, 1, 1, 1)
[1] "SUM($B$1:$B$29)"

Alternatively, one can create an Excel with required formulas and VB scripts. And, selectively modify data in the excel. Since rest of the formulas/scrips are not modified, Excel will update as per new input.

For example, suppose Excel "Sheet1" contains data in 3 columns with formula in C column as =SUM(A2:B2). So, we can just modify data in columns A and B and Excel will be updated for column C due to existing formulas.

> library(openxlsx)
> wb <- loadWorkbook("formula-demo.xlsx")
> # update data in A column
> writeData(wb, "Sheet1", 21:40, startCol = 1, startRow = 2, colNames = FALSE) 
Warning message:
Overwriting existing cell data. 
> saveWorkbook(wb, "formula-demo.xlsx", overwrite = T)

I hope this helps!

回答3:

I find that using the Text To Columns functionality applies formatting changes more thoroughly than the "formatting" utility does. For instance, if you change a column from general to text, none of the cells change from "Number" to "Number saved as text" (as denoted by the green triangle in the upper-left corner) unless you click into the cell and hit enter. Whereas if you use the "text-to-columns" utility, the whole column gets the little green triangles. It might well be the case for you, too, that you can use the "text-to-columns" utility, specifying your column as "general". However, there is a limitation in that you can only do one column at a time.

Another solution may be to have Excel track changes to the workbook, and re-apply the value of the cell to the cell formula, such as:

Private Sub Worksheet_Change(ByVal Target As Range)

  Target.FormulaR1C1 = Target.Value

End Sub

Putting that in the Worksheet code will cause the cell to be re-calculated when it is changed, I suspect. But I have not tested it.