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
– Shai Rado, Commented Dec 28, 2016 at 17:55

PhilC · Accepted Answer · 2016-12-28 22:07:30Z

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, 2016 at 22:07

answered Dec 28, 2016 at 19:03

PhilC

7873 silver badges8 bronze badges

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

4 Comments

PhilC Over a year ago

Change the r.exe to rscript.exe for batch arguments.

PhilC Over a year ago

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 Over a year ago

One more try? c:/mypath note change in slash direction

reggie86 Over a year ago

To anyone reading, use this code that PhilC posted-- It works perfectly! Note for 32-bit R users: use RScript.exe within folder i386

KySoto · Accepted Answer · 2016-12-28 20:03:14Z

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, 2016 at 20:03

answered Dec 28, 2016 at 19:24

KySoto

4283 silver badges10 bronze badges

5 Comments

reggie86 Over a year ago

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

KySoto Over a year ago

ah, i'll fix my answer then

reggie86 Over a year ago

Now I have received an unexpected token error for PowerShell. It says:

KySoto Over a year ago

thinking about it, it might be that it is not expecting there to be two strings. i'll edit again

KySoto Over a year ago

actually i found the reason you may have a problem stackoverflow.com/questions/25246735/…

Collectives™ on Stack Overflow

Running R script in VBA Wrong Directory

2 Answers 2

4 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related