在Excel中插入函数/公式从R和VBA&从R应用VBA宏 [英] Insert a function / formula in Excel from R and VBA & Apply a VBA macro from R
问题描述
我正在寻找一种从R中插入公式/函数到Excel的方法。我经历了所有的包，最好的办法是：
<$ p $
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中的一个字符串。如果单击单元格并按回车键，则公式计算结果。我想一个做这个伎俩的方法是使用将刷新的VBA代码，但通常的选择，refreshall等不起作用。
我可以使用@ n8的建议找到正确的解决方案。这个想法是使用一个VBA代码，它将对所有的工作簿和所有列做一个文本列。然后使用VBscript从R调用VBA代码。
这是R代码：
库（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（）宏如下：
Sub text_to_column（）
应用程序ication.ScreenUpdating = False
在错误恢复下一个
对于每个wksht在ActiveWorkbook.Worksheets
wksht.activate
对于每个col在wksht.Columns
列（col。列）.TextToColumns _
目标：=单元格（1，col.Column），_
DataType：= xlDelimited，_
TextQualifier：= xlDoubleQuote，_
ConsecutiveDelimiter：= False ，_
Tab：= True，_
分号：= False，_
逗号：= False，_
空格：= False，_
其他：= False ，_
FieldInfo：= Array（1，1），_
TrailingMinusNumbers：= True
下一个col
下一个wksht
End Sub
您会注意到R代码的末尾
path_to_vbs_file =./text_to_column.vbs
这指向vbscript这只是一个.vbs文件。可以从文本文件创建它并更改扩展名。我跟着线程：如何创建一个vbscript Stackoverflow 。我的.vbs文件命名为：text_to_column.vbs，如下所示：
Option Explicit
ExcelMacroExample
Sub ExcelMacroExample（）
Dim xlApp
Dim xlBook
设置xlApp = CreateObject（Excel.Application）
设置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
设置xlBook =没有
设置xlApp =没有
End Sub
vb脚本将打开文件test.xlsm运行VBA宏text_to_column，然后以xlsx格式保存文件，名称为test filled.xlsx
vbscript从最后一行运行。
长篇小说，代码R将打开模板，填入f ormula以字符串格式，调用将运行宏的vbscript转换公式。然后将保存具有正确格式的公式的最终文件。
在旁注中，如果要编写公式，可以考虑使用ADDRESS和INDIRECT，这样可以使用行号和列号，写A1 B2等一个例子可以是：
SUM（INDIRECT（ADDRESS（1,1）&：& ; ADDRESS（5,1）））
哪些将执行A1：A5的总和。 p>
再次感谢帮忙，@ n8。
希望这将有助于人们。
Romain。
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.
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.
这篇关于在Excel中插入函数/公式从R和VBA&从R应用VBA宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
