VBA to run shell commands

[JeenLeen]

I'm trying to use Visual Basic to run a Python script via shell commands.

If I open the DOS Command Prompt, this line of code does what I want (filepath is redacted a bit, but does contain spaces like shown)

python "O:\Accountability\zzz projects\2020\This Project\testPython.py"

That works fine and runs the testPython program.

From looking online, I think this VBA should work, but it isn't.

Option Explicit

Sub runpython()
    Dim Ret_Val
    Dim args As String

    args = "'O:\Accountability\zzz projects\2020\This Project\testPython.py'"
    Ret_Val = Shell("python " + args, vbNormalFocus) 'or vbHide
    If Ret_Val = 0 Then
       MsgBox "Couldn't run python script!", vbOKOnly
    End If
End Sub

No errors messages occur, and the Command Prompt flashes for a second then closes, so I think VBA is correctly running and activating the DOS Command Prompt, but it just doesn't do anything.

So I think the syntax for a Shell command must differ somehow from the syntax when using the actual DOS Command Prompt.
Any help? Maybe something with spacing or single/double quotes?

[Jasdoif]

First thing I see....

python "O:\Accountability\zzz projects\2020\This Project\testPython.py"

    args = "'O:\Accountability\zzz projects\2020\This Project'"

Is the filename missing from your arguments, or is that an accidental omission from the path redacting you mentioned?

[Manga Shoggoth]

Several suggestions:

Try putting the full path name in:

for example: Shell ("C:\Windows\System32\cmd.exe")

Also check to see if your anti-virus software is blocking it (possibly not if you can see the command box appearing)>

As to quotes, use double quotes around filenames with spaces, not single quotes. Confusingly you will have to double them up as well as they are inside a string.

Also use the concatenate operator "&" rather than "+":

args = """O:\Accountability\zzz projects\2020\This Project"""
Ret_Val = Shell("python " & args, vbNormalFocus) 'or vbHide

[JeenLeen]

First thing I see....Is the filename missing from your arguments, or is that an accidental omission from the path redacting you mentioned?

Omission from redacting.
Assume both are O:\Accountability\zzz projects\2020\This Project\testPython.py

I've fixed the opening post.

As to quotes, use double quotes around filenames with spaces, not single quotes. Confusingly you will have to double them up as well as they are inside a string.

Also use the concatenate operator "&" rather than "+":

args = """O:\Accountability\zzz projects\2020\This Project"""
Ret_Val = Shell("python " & args, vbNormalFocus) 'or vbHide

That worked!
Thank you!

Also, I realize it should be if Ret_Val NE 0 (or however VBA does 'not equal').

[Manga Shoggoth]

That worked!
Thank you!

You're welcome. I like the ones I can answer!

[JeenLeen]

...and I just realized that my question wasn't exactly the right question

I think I'm actually needing to run this in VBScript. Or whatever a .vbs file is.
The real context is that I'm having SAS run the script via a %sysexec command on CScript. I thought VBA and VBScript were the same, but I'm seeing the differences as I look at guides on translating from one language to another.

Any recommendation on translating this to VBScript?

I've tried a few things based on guides, but all do nothing when I call it from SAS and give one of a handful of errors if I open the .vbs script.

OR if you know the VBScript to run a VBA subroutine, that could work... and I find it kinda funny as it adds another layer of convulution to my process. Currently it's SAS telling Windows to run VBA/VBScript to get it run Python. Trying to workaround some technology limitations that make running Python straight from SAS difficult.

---------------------------------edit: got solution----------------------------------
from https://stackoverflow.com/questions/...mmand-from-vbs

Set oShell = CreateObject ("WScript.Shell")
oShell.run "python ""O:\my file path\testPython.py"""
Set oShell = Nothing

But, alas, while the Script runs independently, it doesn't do anything when I call it from SAS. So I think I'm running into the same issue as before. Time to ask IT if we can install Python on our SAS server, I guess