pythonexcelvbaparameterscall

How to find whether a Python program runs after is has been called by an Excel VBA program


I'm able to run a Python program from Excel VBA. As this Pyhton program can be run either from Excel VBA or in stand-alone mode, I need something in the Pyhton program to find out which way it is running (as I have to apply small changes in the execution).

What kind of parameter (or similar stuff) can I query/identify within the Python program to find out whether it has been called by Excel VBA or not?

I would rather prefer not to pass parameters from Excel VBA to Python (it does not seem obvious).


Solution

  • If the behavior of your Python script needs to change based on how it's called, it would be much more reliable to build a command-line switch into your script. This would also make it explicit when the behavior should change. Given that your are also writing the VBA macro that calls your Python script, you can control when the switch is used.

    I know you mentioned you would prefer to avoid passing parameters to your Python script, but your parenthetical remark suggests your reason for avoiding parameters is that it's not clear how to do it with WScript.Shell.Run.

    Here's how you can pass a command-line switch to a Python script from VBA using WScript.Shell.Run.

    Sub RunCommand()
        Dim shellObj As Object, result As Integer, strCommand As String
        Dim pythonPath As String, pythonScript As String
        
        Set shellObj = CreateObject("WScript.Shell")
        
        pythonPath = "C:\Path\to\python.exe"
        pythonScript = "C:\Path\to\do_something.py"
        strCommand = pythonPath & " " & pythonScript & " --excel"
        result = shellObj.Run(strCommand, 1, True)
    End Sub
    

    do_something.py:

    import argparse
    
    def parse_args():
        parser = argparse.ArgumentParser(
            prog="do_something",
            description="Do some function"
        )
        parser.add_argument("-x", "--excel", action="store_true")
        args, unknown_args = parser.parse_known_args()
        return args, unknown_args
    
    def main():
        args, unknown = parse_args()
        if args.excel:
            do_some_excel_thingy()
        print("Hello")
    
    if __name__ == "__main__":
        main()