从 R 和 VBA & 在 Excel 中插入函数/公式从 R 应用 VBA 宏 [英] Insert a function / formula in Excel from R and VBA & Apply a VBA macro from R

问题描述

我正在寻找一种将公式/函数从 R 插入 Excel 的方法.我浏览了所有软件包，我能做的最好的是:

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")

这给了我一个文件，公式写在单元格 SUM(A1:A2) 中，就好像它是 Excel 中的字符串一样.如果单击单元格并按 Enter，则公式会计算结果.我想一种方法是使用可以刷新的 VBA 代码，但是通常的 select、refreshall 等不起作用.

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.

推荐答案

我能够使用@n8 的建议找到合适的解决方案.这个想法是使用一个 VBA 代码，该代码将对所有工作簿和所有列上的列执行文本.然后使用 VBscript 从 R 调用此 VBA 代码.

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.

这是R代码:

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")

文件 template.xlsm 是一个空的 .xlsm 工作簿，只有一张 CAN.FI，这本书有一个名为text_to_column()"的嵌入式宏/模块，宏如下:

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

您会注意到在 R 代码的末尾

You will notice at the end of the R code the

path_to_vbs_file = "./text_to_column.vbs"

这指向 vbscript，它只是一个 .vbs 文件.可以从文本文件创建它并更改扩展名.我跟着线程:如何创建 vbscript Stackoverflow.我的 .vbs 文件名为: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(".	est.xlsm", 0, True) 
  xlApp.Application.Visible = False
  xlApp.DisplayAlerts = False
  xlApp.Run "text_to_column"
  xlApp.ActiveWorkbook.SaveAs ".	est filled.xlsx", 51

  xlApp.ActiveWorkbook.Close
  xlApp.Quit

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub 

vb 脚本将打开文件 test.xlsm 运行 VBA 宏text_to_column"，然后将文件以 xlsx 格式保存在名称testfilled.xlsx"下

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"

vbscript 从 R 的最后一行开始运行.

The vbscript is run from the last line in R.

长话短说，代码 R 将打开模板，以字符串格式填充公式，调用将运行宏转换公式的 vbscript.然后将保存具有正确格式的公式的最终文件.

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.

顺便说一句，如果你想写公式，你可以考虑使用 ADDRESS 和 INDIRECT，它允许你使用行号和列号，这比写 A1 B2 等更容易.一个例子可能是:

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)))

A1:A5 的总和.

再次感谢您的帮助，@n8.希望能帮到大家.

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

罗曼.

这篇关于从 R 和 VBA & 在 Excel 中插入函数/公式从 R 应用 VBA 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！