Python/vba Outputting File In Directory

I am successfully running my Python script from Microsoft Access VBA with the following command:, Shell (CurrentProject.Path & '\Python\Python-Portable.exe ' & CurrentProj

Solution 1:

The Shell function doesn't guarantee any particular working directory for the program that it runs—in fact, I believe it uses whatever the default is for your default shell, which tends to mean your home directory on newer Windows and somewhere completely useless on older Windows.

When you specify a bare filename like 'mysheet.csv' (or a relative pathname like r'foo\mysheet.csv'), Python will use the current working directory to decide where to put it.

So, either your VBA script will have to explicitly cd before running the program or, more simply, your Python script will have to explicitly put the file in the right location.

---

For example, if you want the file to end up right next to the script file itself (which is a weird thing to do, but that's what you seem to be asking for):

import sys, os

scriptdir = os.path.dirname(os.path.abspath(__file__))

# ... later ...

fd=open(os.path.join(scriptdir, csv_file), 'wt')

---

Or, you might want to make it so the VB script can pass an output directory as an argument:

fd = open(os.path.join(sys.argv[1], csv_file), 'wt')

Then:

Shell (CurrentProject.Path & "\Python\Python-Portable.exe " & 
       CurrentProject.Path & "\Python\scripts\convert.py " & 
       CurrentProject.Path & "\Python\scripts")