CODEWITHSUNDEEP
pythonvbashell
user7437214
user7437214

Reputation: 59

Sending a parameter from VBA to Python via shellscript

I'm currently running a program in VBA which calls Python to execute a script. The script downloads from SQL then runs an algorithm to produce output which is subsequently consumed by VBA (I'm doing it this way since my GUI is in Excel and my final output is an Excel table).

I'm running the Python part via shellscript as below:

Public Const PythonPath = "D:\Anaconda2\Python.exe "

Set ShellScript = VBA.CreateObject("WScript.Shell")
Filename = """I:\SQL Downloader_v4.2.py"""
ShellScript.Run (PythonPath & Filename), windowStyle, waitOnReturn

This then launches a Python application which requires a username and password to access an SQL database. Python is set up to accept username and password via raw_input within the script. How would I send username and password from VBA to Python in this method or is there a better way than doing via raw_input?

Upvotes: 1

Views: 512

Answers (1)

Shmygol
Shmygol

Reputation: 933

You could send username and password using command line arguments.

Your VBA code would looks like so:

Public Const PythonPath = "D:\Anaconda2\Python.exe "

Set ShellScript = VBA.CreateObject("WScript.Shell")
Filename = """I:\SQL Downloader_v4.2.py"""
ShellScript.Run (PythonPath & Filename & username & password), windowStyle, waitOnReturn

In the python script you can parse first the arguments with getopt:

import sys

if sys.argv < 3:
    print('Missing username and/or password')
    print('Usage: SQL_Downloader_v4.2.py USERNAME PASSWORD')
    exit(1)
username = sys.argv[1]
password = sys.argv[2]

...

There are two modules in the Python standard library for more advanced parsing of command line arguments: getopt and argparse.

Upvotes: 2

Related Questions