Run Vba Script from R

Run VBA script from R

1. Write a VBscript wrapper that calls your VBA. See Way to run Excel macros from command line or batch file?

2. Run your VBscript via R's system or shell functions.

Launch VBA macro from R

I found the solution. If some of you are trying to call personnal library from a vbscript, you need to add :

Dim objWorkbook
Set objWorkbook=xlApp.Workbooks.Open("D:\users\...\PERSONAL.XLSB")

before launching the macro.

Running R scripts from VBA

Public Sub RunRTest()
  Shell ("Rscript test.r")
End Sub

Run VBA code for multiple Excel files in R

R code that executes this particular task is as follows:

shell(shQuote(normalizePath("C:/Documents/VBS.vbs")), "cscript", flag = "//nologo")

And VB script can be found in this topic.

Run R script from VBA

Finally, here is a solution working well:

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 Demo()
Dim iEerrorCode As Integer
iEerrorCode = Run_R_Script("C:\Program Files\R\R-3.6.1\bin\x64\Rscript", 
"C:\Users\myname\Desktop\Code.R")
End Sub

Run R script in Excel

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.

How to pass an argument from Excel/VBA to a script in Rstudio

EDIT:

The problem is RStudio and not your code. RStudio doesn't accept command line arguments:

https://support.rstudio.com/hc/en-us/community/posts/200659066-Accessing-command-line-options-in-RStudio

OLD ANSWER:

The problem I am seeing is that you are putting the text "var1" in to the path instead of the contents of the variable called var1. I replaced your Chr(34) with stacked quotes because it's easier for me to keep track of. I apologize if this looks less readable to you. I tested the string and it does feed the contents of Var1 as a command line argument.

Try this:

Option Explicit

Public Sub RunRscript()
    ActiveWorkbook.Save
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Long: style = 1
    Dim errorcode As Long
    Dim path As String
    Dim var1 As String
    var1 = ActiveSheet.Range("F3").Value
    path = """C:\Program Files\RStudio\bin\rstudio.exe"" ""C:\Users\LI\Downloads\starting_code_v3.R"" """ & var1 & """"

    errorcode = shell.Run(path, style, waitTillComplete)
End Sub

---

Related Topics