Jise7 Jise7 - 3 months ago 47
R Question

VBA code to run R script: output image issue in 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.

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

source("Output.R")


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

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

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.

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

Thank you

EDIT

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

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:

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.

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

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

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

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.

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.

Answer

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

    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.

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.

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

I use the following VBA script in excel to call 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