VBA code to run R script: output image issue in Excel

Question

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.

You don't need to escape backslash in VBA or on the command line — Tim Williams
– Tim Williams, Commented Sep 1, 2016 at 16:22
You keep referencing image, charts and output but we see no code running anything only a command line call. Can you post R code so we can see your full process? Does R script produce a chart image that Excel is to render? — Parfait
– Parfait, Commented Sep 1, 2016 at 23:28
Charts are drawn in separate window which can't be captured by shell in excel. Try to save charts to png/jpg from within R script on disk and use vba script to load these image files in Excel. — M.Hassan
– M.Hassan, Commented Sep 1, 2016 at 23:43
Thank you all for your help. I added a few lines of code so that you can have a full understanding of the issue. Please let me know if you find a solution! — Jise7
– Jise7, Commented Sep 2, 2016 at 7:40

M.Hassan · Accepted Answer · 2016-09-04 06:08:17Z

2

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

answered Sep 4, 2016 at 6:08

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jise7 Over a year ago

Thank you very much for your help, the code is now working correctly and I can see the images in the Excel output!

Jise7 Over a year ago

I have now another small issue, described in the EDIT 2 part of my question. If you have a bit of time, it would be great if you can have a look at it. Sorry but as I said I am new with VBA and I still miss some basic concepts. Thanks!

M.Hassan Over a year ago

To resolve it: 1)Create Environment Variable {RSTUDIO_PANDOC} that point to the path of "pandoc" which is needed for rendering. It's in my machine "G:\Program Files\RStudio\bin\pandoc" find it in your machine. 2) Add this line in your r script: library(rmarkdown) . It's working for me. Let me know if that is working for you.

Jise7 Over a year ago

Now it works! I was missing the line Sys.setenv(RSTUDIO_PANDOC="C:\\Program Files\\RStudio\\bin\\pandoc") in my script. Again, thank you very much for your time, your advices were extremely helpful!

user7075507 · Accepted Answer · 2016-11-24 09:23:57Z

0

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

answered Nov 24, 2016 at 9:23

user7075507

Collectives™ on Stack Overflow

VBA code to run R script: output image issue in Excel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related