Support the GITP forums on Patreon
Help support GITP's forums (and ongoing server maintenance) via Patreon
Results 1 to 6 of 6
  1. - Top - End - #1
    Troll in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default VBA to run shell commands

    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)
    Code:
    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.
    Code:
    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?
    Last edited by JeenLeen; 2020-09-21 at 12:23 PM.

  2. - Top - End - #2
    Firbolg in the Playground
     
    Jasdoif's Avatar

    Join Date
    Mar 2007
    Location
    Oregon, USA

    Default Re: VBA to run shell commands

    First thing I see....
    Quote Originally Posted by JeenLeen View Post
    Code:
    python "O:\Accountability\zzz projects\2020\This Project\testPython.py"
    Quote Originally Posted by JeenLeen View Post
    Code:
        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?
    Feytouched Banana eldritch disciple avatar by...me!

    The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas

  3. - Top - End - #3
    Ogre in the Playground
     
    Flumph

    Join Date
    Nov 2006
    Location
    Essex, England
    Gender
    Male

    Default Re: VBA to run shell commands

    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
    Last edited by Manga Shoggoth; 2020-09-21 at 12:14 PM.
    Warning: This posting may contain wit, wisdom, pathos, irony, satire, sarcasm and puns. And traces of nut.

    "The main skill of a good ruler seems to be not preventing the conflagrations but rather keeping them contained enough they rate more as campfires." Rogar Demonblud

    "Hold on just a d*** second. UK has spam callers that try to get you to buy conservatories?!? Even y'alls spammers are higher class than ours!" Peelee

  4. - Top - End - #4
    Troll in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: VBA to run shell commands

    Quote Originally Posted by Jasdoif View Post
    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.

    Quote Originally Posted by Manga Shoggoth View 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').
    Last edited by JeenLeen; 2020-09-21 at 12:27 PM.

  5. - Top - End - #5
    Ogre in the Playground
     
    Flumph

    Join Date
    Nov 2006
    Location
    Essex, England
    Gender
    Male

    Default Re: VBA to run shell commands

    Quote Originally Posted by JeenLeen View Post
    That worked!
    Thank you!
    You're welcome. I like the ones I can answer!
    Warning: This posting may contain wit, wisdom, pathos, irony, satire, sarcasm and puns. And traces of nut.

    "The main skill of a good ruler seems to be not preventing the conflagrations but rather keeping them contained enough they rate more as campfires." Rogar Demonblud

    "Hold on just a d*** second. UK has spam callers that try to get you to buy conservatories?!? Even y'alls spammers are higher class than ours!" Peelee

  6. - Top - End - #6
    Troll in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: VBA to run shell commands

    ...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

    Code:
    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
    Last edited by JeenLeen; 2020-09-21 at 03:09 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •