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 " & CurrentProject.Path & "\Python\scripts\convert.py")

When the Python script runs the csv output is being placed in my Windows home directory. Here is the source Python code for convert.py:

#!/usr/bin/python
import sys, os
from openpyxl.reader.excel import load_workbook

def main():
    wb=load_workbook(filename='list.xlsx')  
    for sheet in wb.worksheets:
        csv_file='%s.csv' % sheet.title
        print 'Creating %s' % csv_file
        fd=open(csv_file, 'wt')
        for row in sheet.rows:
            values=[]
            for cell in row:
                value=cell.value
                if value is None:
                    value=''
                if not isinstance(value, unicode):
                    value=unicode(value)
                value=value.encode('utf8')
                values.append(value)
            fd.write('\t'.join(values))
            fd.write('\n')
        fd.close()

if __name__=='__main__':
    main()

I would like the CSV file to be placed into "CurrentProject.Path & "\Python\scripts\". Any suggestions on why Access VBA is placing my output into my Windows home directory?

Thanks

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")