CODEWITHSUNDEEP
rexcelvba
LiviusI
LiviusI

Reputation: 204

keep Shell / cmd open when launching R script from VBA

I have an R script that is being run from an Excel workbook via a button that is linked to a VBA script.

The problem is every time the R script encounters an error - say it does not find one of the files it was supposed to read - the Shell / cmd window the R script is running in closes down instantly. The problem with that is you cannot see any clues as to why it failed. I then have to debug it manually by modifying the code and running in RStudio to find the errors - usually I have to do this for other people that do not know R.

The VBA code I use was copied and modified from one of the SO posts I found here. I am not very proficient in VBA and do not understand a lot of the code so I'm looking for a simple solution to this. My VBA code is 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 Integer: style = 1

    Dim scriptPath As String
    scriptPath = Range("F5").Value

    Dim argument As String
    argument = Range("F3").Value

    Dim path As String
    path = """C:\Program Files\R\R-3.4.2\bin\Rscript.exe"" """ & scriptPath & """ """ 
& argument & """"

    ActiveWorkbook.Save

    Dim errorcode As Integer
    errorcode = shell.Run(path, style, waitTillComplete)

    ActiveWorkbook.Save

End Sub

scriptPath points to the path of the R script and argument is an argument I pass to the R script.

I have tried passing the usual arguments to keep the cmd window open but I didn't manage to figure it out. This is also complicated by the fact that as mentioned I do not understand VBA syntax very well and the 'path' variable being very convoluted because of those endless double quotes.

Upvotes: 0

Views: 5666

Answers (2)

chgrl
chgrl

Reputation: 116

You might use

errorcode = shell.Run("cmd /k " & path, style, waitTillComplete)

instead of

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

We open a new instance of the command-line tool, thus can use its parameters. The parameter /k keeps the cmd-window open (use /c to close). After the cmd-parameter we add the script to be executed.

But there is one problem: if you close the cmd-window, VBA throws an overflow error. I use a flag in my Excel sheet to switch between debug mode and normal mode:

debugging = Range("N5").Value 'True/False
If debugging Then
    errorCode = shell.Run("cmd /k " & path, style, waitTillComplete)
Else
    errorCode = shell.Run(path, style, waitTillComplete)
End If

If the script fails I rerun it setting debug mode to True in a cell of the Excel sheet.

Upvotes: 6

FreeMan
FreeMan

Reputation: 5687

Based on this comment:

@FreeMan, the output of the R script is actually a series of Excel workbooks that it opens, prints data in and then saves. it reads a list with the paths of all the files it needs to read as an input to generate the output data required to print in excel. Sometimes, one of the paths in that list is wrong, so the program stops halfway and there is no clue as to why for the user. (the reason could be something else as well). It would be good if I could pass the text that is generated in shell / cmd automatically to a notepad / textpad file at least, so the user could then open and check

It sounds like the best bet may be to have your R script validate the paths/files prior to attempting to do its processing. I'm barely familiar with R, but what I know of it there should be a library somewhere that will allow you to test for the existence of a path and/or a file on that path. If it doesn't exist, the script would simply put an error message in the file it was supposed to create/update indicating that it couldn't find the requested path/file, or it could write that message to an error log that could be opened by your VBA code at the completion of its processing.

Upvotes: 0

Related Questions