Insert function / formula in Excel from R and VBA and apply VBA macro from R

I was able to find the right solution using suggestion from @ n8. The idea was to use VBA code that would render text in a column across the entire workbook and across all columns. Then call this VBA code from R using 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 template.xlsm file is a blank .xlsm workbook with one CAN.FI sheet, the workbook has a built-in macro / module called text_to_column (). The macro runs like this:

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

      

        
        
        
      

    

At the end of the R code, you will notice

path_to_vbs_file = "./text_to_column.vbs"

      

        
        
        
      

    

This points to vbscript, which is just a .vbs file. You can create it from a text file and change the extension. I followed the theme: fooobar.com/questions/111293 / ... . My .vbs file is called: 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 

      

        
        
        
      

    

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

vbscript runs from the last line in R.

In short, the R code will open the template, fill in the formula in string format, call vbscript, which will run the macro that transforms the formula. Then the final formulas file will be saved in the correct format.

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

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

      

        
        
        
      

    

What will the sum A1: A5 do.

Thanks again for the help @ n8. Hope this helps people.

Romny.