CODEWITHSUNDEEP
vbaexcelshellwsh
feetwet
feetwet

Reputation: 3446

Excel VBA to start shell script in background with specified environment

I want use an Excel macro to start a python script to run in the background.

I have to be able to specify that it use the system PATH (I don't want to assume where python is installed) as well as a particular working directory, where it will find other files it needs.

Presently I use the following, but this blocks the Excel process until it is complete.

Sub RunPython()
    shellScript = "python Daily.py"
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.CurrentDirectory = Application.ThisWorkbook.Path
    Set wshSystemEnv = wsh.Environment("Process")
    Set WshShellExec = wsh.exec(shellScript)
    Range("C7").Value = WshShellExec.StdErr.ReadAll ' Show error, if any
End Sub

How can I run this on a background process?

Bonus points if there are methods of invoking another VBA subroutine after the process has completed!

Upvotes: 0

Views: 2130

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

Well, the issue here is Excel VBA doesn't support multi-threading, that means all of the code runs linear only! And because the shell should return something that you want to write into a cell it has of course to wait until the python script finished. So the Excel instance running this code will always wait for the py script.

Suggested workaround
Run the script with Shell "python Daily.py" and redirect the output into a file: Shell "python Daily.py >output.txt"

Then run a loop to check if the file exists

Do
    DoEvents 'make Excel responsive
Loop While Not FileExists("output.txt")

eg use this function

Function FileExists(ByVal fName As String) As Boolean
    On Error Resume Next
    FileExists = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

Note that you won't be able to run another macro while that loop runs, but Excel is responsive.

Otherwise you can use How to have vba execute every 10 minutes? to eg. check every 1 minute if the file exists and then run a procedure.

Don't forget to delete the output.txt file before running the py script.

Upvotes: 1

Related Questions