Tutorial :Running an external script from Access



Question:

I want my Access application to run an external program (in this case a R script) after the user clicks a button. I use this code:

Dim RetVal  RetVal = Shell("""C:\Program Files\R\R-2.10.1\bin\R.exe"" CMD BATCH --no-environ --silent --no-restore --no-save ""c:\test.R"" ""c:\test-result.txt""", vbHide)  MsgBox RetVal  

This works fine, but the VBA code keeps on running while my script is executed. How can I make Access waiting for the script to be finished? Has anybody suggestions about how to give an error message of the script back to Access?


Solution:1

The OpenProcess and WaitForSingleObject combo that @Remou links to, is probably your best bet for doing this. You should take a look at this, it's a nice drop in module for shell and wait.

For returning a message back from the script, you could mess around with redirecting the scripts input and output. This is not for the faint of heart. As an alternative I would redirect the output of the script to a text file, then read in that file after it exits.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »