Running R script in VBA Wrong Directory

Question

I have a macro that edits an R script. That R script is then supposed to be called by the following VBA:

 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 = """" & "C:\Program Files\R\R-3.3.2\bin\i386\R.exe" & """ """ & "R RAM Cluster Script.R" & """"

 errorCode = shell.Run(path, style, waitTillComplete)

The above code was from this question.

When I execute the macro, however, the R Command Line gives me an error stating:

 '\\dm\home\myaccount\*Path of my original Excel File*'
 "CMD.EXE was started with the above path as the current directory. 
  UNC Paths are not supported. Defaulting to Windows directory. 
  Argument 'R  RAM Cluster Script.R' Ignored"

The script is stored in the folder that my Excel workbook is in.

Can anyone help me out with finding my problem?

what happens if you try to run the **R script** by replacing the last line with `shell.Run path, 1, True` ? does it work ? — Shai Rado, Dec 28 '16 at 17:55

PhilC · Accepted Answer · 2016-12-28T22:07:30.290

1

Try this:

Sub test()
 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 = """C:\Program Files\R\R-3.3.1\bin\RScript.exe""" & """c:/temp/R RAM Cluster Script.R"""

 errorCode = shell.Run(path, style, waitTillComplete)

End Sub

edited Dec 28 '16 at 22:07

answered Dec 28 '16 at 19:03

PhilC

767
3
8

Change the r.exe to rscript.exe for batch arguments. – PhilC Dec 28 '16 at 19:12
Try adding the path to the R file 'c:\YOUR DIR\R RAM ...' in your argument. The error is due to rscript not finding the script. – PhilC Dec 28 '16 at 20:17
One more try? c:/mypath note change in slash direction – PhilC Dec 28 '16 at 20:30
To anyone reading, use this code that PhilC posted-- It works perfectly! Note for 32-bit R users: use RScript.exe within folder i386 – reggie86 Dec 28 '16 at 22:27

KySoto · Answer 2 · 2016-12-28T20:03:14.850

0

I would suggest trying to use powershell as a wrapper for your script because it DOES support UNC paths. So, something like this should work:

path = "Powershell.exe -executionpolicy bypass -Command &{'C:\Program Files\R\R-3.3.2\bin\i386\R.exe " &  ThisWorkbook.Path & "\R RAM Cluster Script.R'}"

edited Dec 28 '16 at 20:03

answered Dec 28 '16 at 19:24

KySoto

399
2
7

This did help get around the directory problem, but because of the space between 'Program' and 'Files', it is looking for an executable called `C:\Program` – reggie86 Dec 28 '16 at 19:42
ah, i'll fix my answer then – KySoto Dec 28 '16 at 19:48
Now I have received an unexpected token error for PowerShell. It says: – reggie86 Dec 28 '16 at 20:00
thinking about it, it might be that it is not expecting there to be two strings. i'll edit again – KySoto Dec 28 '16 at 20:02
actually i found the reason you may have a problem http://stackoverflow.com/questions/25246735/unexpected-token-errors-in-powershell – KySoto Dec 28 '16 at 20:12

Running R script in VBA Wrong Directory

2 Answers2