5

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

Source

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?

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

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Bunty
  • 51
  • 2
  • 3
  • 8
  • What happens when you run "RScript C:\R_code\hello.R" through cmd.exe? – Tom Dee Mar 23 '18 at 12:45
  • The same thing,I described above-Just opens up in RStudio – Bunty Mar 23 '18 at 12:47
  • Possible duplicate of [Running R scripts from VBA](https://stackoverflow.com/questions/11597626/running-r-scripts-from-vba) So this code looks correct. – Pᴇʜ Mar 23 '18 at 12:50
  • Tried That already. – Bunty Mar 23 '18 at 12:52
  • I guess on Windows you cannot simply call a executable like that. What about something like that with the path to the executable C:\Program Files\R\R-3.4.0\bin\x64\Rscript.exe C:\R_code\hello.R – JRR Mar 23 '18 at 12:55
  • @JRR the OP said "*just opens the R script in Rstudio*" but the script does not run. Therefore it cannot be a missing path (it did start). Also you can run it without path as long as it is in the PATH environment variable of Windows. – Pᴇʜ Mar 23 '18 at 12:57
  • @Bunty ... that is very strange as Rscript.exe is an executable file and entirely separate from RStudio. In cmd.exe run `echo %path%` and check if the installed R's bin folder appears. R.exe and Rscript.exe is located in bin folder. Maybe your Anaconda or other distribution is affecting env variable paths by overwriting Rscript path. Try uninstall/reinstall of R and then RStudio. – Parfait Mar 23 '18 at 14:54

3 Answers3

3

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

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

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.

Tom Dee
  • 2,516
  • 4
  • 17
  • 25
  • Hello, Thanks for your answer.I am quite new to programming. Can you please tell me where do I write this- CMD/VBA code or you just want me to edit it somewhere in the code I mentioned in the question above? – Bunty Mar 23 '18 at 13:08
  • The one without output works(which means that R Studio doesn't open)- I suppose that's how it has to be.But the second one ,with output as mentioned in your answer gives error-Compile Error:Expected End of statement – Bunty Mar 23 '18 at 13:48
  • Try again, I was trying to compress it all on one line but apparently it didn't like that! I haven't tested it but should be fine – Tom Dee Mar 23 '18 at 14:03
  • Follow up Question ,if possible (https://stackoverflow.com/questions/49452078/r-script-excel-output-while-running-in-vba) – Bunty Mar 23 '18 at 14:47
0

I had the same issue as you, but the "R CMD BATCH" solution didn't work for me. Here's what worked for me.

First, I tested whether I could run my R script via the command line to weed out any issues.

Open your command prompt and try typing after the ">" symbol, "path Rscript.exe" "path to R script you want to run". In my case I typed "C:\Program Files\R\R-3.6.0\bin\Rscript.exe" "C:\Users\phung\Documents\Angela\DB_Import\TransformData.R" then hit enter to run the code. See image below for example. You will need to navigate to your C program files>R>version>bin to find Rscript.exe (might be different path on your computer).

enter image description here

Once I got this to work, I used adapted the code provided by Ibo here: Running R scripts from VBA

    Function Run_R_Script(sRApplicationPath As String, _
                    sRFilePath As String, _
                    Optional iStyle As Integer = 1, _
                    Optional bWaitTillComplete As Boolean = True) As Integer

        Dim sPath As String
        Dim shell As Object

        'Define shell object
        Set shell = VBA.CreateObject("WScript.Shell")

        'Wrap the R path with double quotations
        sPath = """" & sRApplicationPath & """"
        sPath = sPath & " "
        sPath = sPath & sRFilePath

        Run_R_Script = shell.Run(sPath, iStyle, bWaitTillComplete)
    End Function


   Sub Run_R 
        Dim iEerrorCode As Integer

        iEerrorCode = Run_R_Script("C:\Program Files\R\R-3.6.0\bin\Rscript.exe", """C:\Users\phung\Documents\Angela\1_MR-100 project\DB ready VBA code\TransformData.R""")
   End Sub

I was careful to use double quotations in VBA because I had a space in my folder name.

Angela P
  • 111
  • 5
0

The previous answers are correct, but here is why. The path you use for the WScript.Shell is what is executed. This is similar to double-clicking on the file in file explorer.

The Operating System sees the file extention (.R) in the path and says "I know what to do with that! The default application for .R is RStudio. So it opens RStudio and and specified file.

Richard Binnington
  • 54
  • 1
  • 9