0

I have a report with a front-end in Excel (inherited files). There are multiple buttons calling VBA code which then call either SQL queries, stored procedures or R script files (in the form of rcmd batch/shell, passing some parameters etc.)

R scripts are called from VBA one after another. All need some unspecified time to finish.

There are applications/wait-time in the VBA to 'ensure' that the R script is finished before next step is initiated.

This does not always work (depends on the data size etc.).

Is there a way for / code to be put at the end of the R script that would notify VBA it is time to go on?

Or for VBA to check if the specific R script is still running?

Community
  • 1
  • 1
tomo_111
  • 19
  • 2
  • 1
    Why not call the R script from VBA? – z̫͋ Apr 18 '14 at 14:16
  • Hi, it is called from VBA at the moment. But when you call more scripts one after another (and all of them need some unspecified time to finish), how do you ensure it? Currently waittime in VBA is used, which failes sometimes when the script runs longer for some reason. example: call Rscript 1 --runs maybe 4-5 minutes call Rscript2 The Rscript2 should run only after the first is finished, is there a way to ensure that in an automated way? thank u also sorry for my english, not my mother tongue. – tomo_111 Apr 18 '14 at 14:57
  • 2
    You can wait for each script to finish. See [excel vba wait for shell command to complete](http://stackoverflow.com/questions/15951837/excel-vba-wait-for-shell-command-to-complete) – z̫͋ Apr 18 '14 at 15:21
  • looks very helpful/useful i will test it, thank u! – tomo_111 Apr 18 '14 at 21:20
  • Possible duplicate of [Wait for Shell to finish, then format cells - synchronously execute a command](https://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells-synchronously-execute-a-command) – Artem Jul 19 '19 at 23:02

1 Answers1

0

Based on @z feedback & Wait for Shell to finish, then format cells - synchronously execute a command thread.

You can use approach WScript.Shell object with Run method with waitOnReturn option set:

Please see the example of the code for Excel function:

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run Chr(34) & "C:\Program Files\R\R-3.5.0\bin\rscript" & Chr(34) & " " & Chr(34) & "C:\Users\demo_user\test.R" & Chr(34), windowStyle, waitOnReturn

test.R contents:

rnorm(10000)
Artem
  • 3,304
  • 3
  • 18
  • 41