运行R脚本的VBA代码：在Excel中输出图像问题

I am working on a project with R and Excel, using the package "xlsx" to print the output and I have the following problem.

我正在使用R和Excel进行项目,使用包“xlsx”打印输出,我有以下问题。

The analysis is performed by a script called "Model.R".A second script, called "Output.R" produces the output (which include some charts) of my analysis. I call it from the first script with the command

分析由名为“Model.R”的脚本执行。第二个脚本名为“Output.R”,产生我的分析输出(包括一些图表)。我用命令从第一个脚本调用它

source("Output.R")

If I run both the script from R Studio, no problem and the output is correct.

如果我从R Studio运行这两个脚本,没问题,输出是正确的。

Now, I built an Excel "interface" to run the script, using the following VBA code for a Macro:

现在,我使用以下VBA代码构建了一个Excel“界面”来运行脚本:

Sub Run_script()
    Dim cmdLine As String
    cmdLine="C:\\Program Files\\R\\R-3.3.1\\bin\\Rscript C:\\...\\Model.R"
    shell cmdLine
End Sub

Now everything works fine and the output is correctly printed in a new Excel file, a part from the fact that now all the charts are blank boxes with nothing inside.

现在一切正常,输出正确打印在一个新的Excel文件中,这是因为现在所有的图表都是空白框,里面没有任何内容。

Is there anyone that knows how to solve this issue? For example could be another way to run the script from Excel.

有没有人知道如何解决这个问题?例如,可能是从Excel运行脚本的另一种方法。

Thank you

EDIT

Here is the R code that produces and saves one of the charts, as an example:

以下是生成并保存其中一个图表的R代码,例如:

hist(P1,probability=FALSE,main = "",breaks = 20)
title("CE Anno -1")
dev.copy(png,filename="CE1.png");
dev.off ()

so the image is saved as png file in the same working directory. Then, to print it in Excel I use the xlsx package and the following code:

因此图像将保存为同一工作目录中的png文件。然后,要在Excel中打印它我使用xlsx包和以下代码:

outwb <- createWorkbook()
SummaryCE <- createSheet(outwb, sheetName = "Summary CE")
addPicture("CE1.png", SummaryCE, scale = 0.5, startRow = 11, startColumn = 1)
saveWorkbook(outwb, "Risultati RO.xlsx")

As I said, this code works fine if I run it from R Studio. Using the previous VBA code to call it from the command line, the Excel is correctly created but the image is a blank box.

正如我所说,如果我从R Studio运行它,这段代码工作正常。使用以前的VBA代码从命令行调用它,正确创建Excel但图像是一个空白框。

EDIT 2

Thanks to your help, I successfully solved the problem. What I am trying to do now is to automatically produce a report (Word format, no LaTex available here) based on the same script Model.R

感谢您的帮助,我成功地解决了这个问题。我现在要做的是基于相同的脚本Model.R自动生成报告(Word格式,此处没有LaTex)

To do this, I use the Markdown feature and I write a R Markdown file "Report.Rmd". Here I basically load the workspace resulting from the Model.R execution

为此,我使用Markdown功能,并编写一个R Markdown文件“Report.Rmd”。在这里,我基本上加载了Model.R执行产生的工作空间

load("Mywork.RData")

then add some tables, charts and comments. Then, I produce the Word document using the following line in the "Model.R" script

然后添加一些表格,图表和评论。然后,我使用“Model.R”脚本中的以下行生成Word文档

render("Report.Rmd")

Again, I have a similar issue as before. If I run it from R Studio, everything is fine. If I use the previous Excel interface, the Word output does not appear in the folder.

我再次遇到类似的问题。如果我从R Studio运行它,一切都很好。如果我使用以前的Excel界面,Word输出不会出现在该文件夹中。

Anyone knows what might cause this problem?

谁知道什么可能导致这个问题?

Thank you and sorry for the several questions, I am confortable with R but it is the first time I use VBA.

谢谢你,抱歉有几个问题,我很满意R,但这是我第一次使用VBA。

2 个解决方案

#1

dev.copy is not working well in non-interactive mode (shell command). Modify the code as in in the following script:

dev.copy在非交互模式下运行不佳(shell命令)。修改代码,如下面的脚本中所示:

    require(xlsx)

    fname <- "CE1.png"

    #add this device. you can use other device,e.g. jpg /bmp/tiff devices
    png(fname)  # you can define width /height as needed

    hist(P1,probability=FALSE,main = "",breaks = 20)
    title("CE Anno -1")

    #dev.copy(png,filename="CE1.png");  #remove this line

    dev.off ()

     # the following code used  as is 
    outwb <- createWorkbook()
    SummaryCE <- createSheet(outwb, sheetName = "Summary CE")
    addPicture("CE1.png", SummaryCE, scale = 0.5, startRow = 11, startColumn = 1)
    saveWorkbook(outwb, "Risultati RO.xlsx")

To be sure that the code is working with the expected result, create a batch file e.g do.bat and execute it from the console window.

要确保代码正在使用预期结果,请创建一个批处理文件,例如do.bat,并从控制台窗口执行它。

The batch file may be:

批处理文件可能是:

   "I:\Program Files\R\R-3.2.1\bin\i386\rscript" --verbose    model.r

It should be working and excel is filled with the image.

它应该工作,excel充满了图像。

If that is done successfully, you can call the batch file in excel

如果成功完成,您可以在excel中调用批处理文件

I use the following VBA script in excel to call do.bat:

我在excel中使用以下VBA脚本来调用do.bat:

 Sub RunShell()
 Dim currentPath As String
 currentPath = ActiveWorkbook.Path
'Debug.Print currentPath 
 Call Shell(currentPath & "\do.bat ", vbNormalFocus)
 End Sub

I have tested the code and it's working and fill excel with the image

我已经测试了代码并且它正在工作并且用图像填充excel

#2

This works fine for me.

这对我来说很好。

Sub RunRscript1()
    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

    ' http://shashiasrblog.blogspot.com/2013/10/vba-front-end-for-r.html
    path = "C:\Users\your_path\Documents\R\R-3.2.5\bin\x64\R.exe CMD BATCH --vanilla --slave C:\Users\your_path\Documents\R\Download.r"
    'path = """C:\Users\your_path\Documents\R\R-3.2.5\bin\i386"" C:\Users\your_path\Documents\R\Download.R"
    errorCode = shell.Run(path, style, waitTillComplete)
End Sub

#1