在Excel中运行R脚本 [英] Run R script in Excel

问题描述

关于如何执行此操作的信息不多.我试图在线学习博客，并在VBA中实现以下代码(带有R文件的路径):-

There isn't a lot of information on how to do this. I tried to study a blog online and implemented the following code in VBA(with the path of the R file):-

Sub RunRscript()
    'runs an external R code through Shell
    'The location of the RScript is 'C:\R_code'
    'The script name is 'hello.R'

    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorCode As Integer
    Dim path As String
    path = "RScript C:\R_code\hello.R"
    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

来源

但是，当我在Excel中运行宏时，它基本上什么也没做-只需在RStudio中打开脚本即可.我没有收到任何错误，但是没有给出任何输出-只需在Rstudio中打开R脚本即可.我在做什么错了?

However, when I run the macro in Excel, it basically does nothing-just opens the script in RStudio. I am not getting any error, but it's not giving any output-just opens the R script in Rstudio. What am I doing wrong?

此外，如果需要在Excel中使用R，此方法是否有效，或者基本上我需要安装软件RExcel?

Also, does this method work or basically I need to install the software RExcel, if I need to use R in Excel?

在Excel中使用R的任何其他链接/信息将不胜感激.谢谢:)

Any other link/information to use R in Excel would be appreciated. Thanks:)

推荐答案

它在RStudio中打开似乎很奇怪.我建议直接通过R.exe运行它.看起来PATH是根据您告诉我们的内容正确设置的.因此，如果不需要输出，可以像这样调用R.exe:

It seems quite odd that it is opening in RStudio. I would suggest running it straight through R.exe. It looks like the PATH is setup all correctly from what you have told us. So you can call R.exe like this if don't need the output:

Sub RunRscript()
    Shell ("R CMD BATCH C:\R_code\hello.R")
End Sub

如果需要输出，则需要制作一个WshShell对象，如下所示:

If you need the output then you'll need to make a WshShell object like this:

Sub RunRscript()
    Dim output As String
    output = CreateObject("WScript.Shell").Exec("R CMD BATCH C:\R_code\hello.R").StdOut.ReadAll
End Sub

这是运行R脚本的较旧方法，但暂时应该可以正常工作.您可能需要多研究一下R的安装，以查看是否还有其他问题.

This is the older way to run R scripts but should work fine for the time being. You may want to look into your installation of R a bit more to see if there are any other problems.

这篇关于在Excel中运行R脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！