在目录中的Python / VBA输出文件 [英] Python/VBA outputting file in directory

问题描述

我成功运行从Microsoft Access VBA我的Python脚本使用下面的命令：，

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

在Python脚本运行CSV输出被摆在我的Windows的主目录。这里是源码Python $ C $下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()

我想CSV文件被放置到CurrentProject.Path和放大器;？\ Python的\脚本\为什么访问VBA是把我的输出到我的Windows Home目录下的任何建议

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?

感谢

推荐答案

的的 壳牌 功能不能保证它的程序中的任何工作目录下运行，事实上，我相信它使用的预设是作为默认的shell，这往往意味着对新的Windows和地方你的home目录上的老版本的Windows。

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.

当您指定一个光秃秃的文件名如mysheet.csv（或类似 r'foo \ mysheet.csv<相对路径名/ code>），Python将使用当前的工作目录来决定把它放在哪里。

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.

所以，无论是你的VBA脚本将不得不明确光盘运行程序前，或者更简单地说，你的Python脚本将不得不明确将文件放在正确的位置

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

---

或者，你可能希望把它使VB脚本可以通过输出目录作为参数：

---

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

然后：

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

这篇关于在目录中的Python / VBA输出文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！