从Excel vba shell更改工作目录

For work, I'm trying to run a Python script from an Excel VBA macro.

为了工作，我正在尝试从Excel VBA宏运行Python脚本。

The macro is as follows -

宏如下 -

Sub Plot()

Shell "C:\Users\maheshda\AppData\Local\Continuum\Anaconda3\python.exe C:\Users\maheshda\Tool\Tool\Main.py", vbNormalFocus

End Sub

The script runs just fine when run from the command line, but unfortunately, because I'm using relative filepaths in the Python script (at one point, I call files from '../Input/') the script crashes because the current working directory isn't C:\Users\maheshda\Tool\Tool.

从命令行运行时脚本运行得很好，但不幸的是，因为我在Python脚本中使用相对文件路径（在某一点上，我从'../Input/'调用文件）脚本崩溃，因为当前工作目录不是C：\ Users \ maheshda \ Tool \ Tool。

How can I change the working directory from within the macro? Thank you!

如何在宏中更改工作目录？谢谢！

3 个解决方案

#1

This is a trivial task in VBA, use ChDir:

这在VBA中是一项微不足道的任务，使用ChDir：

ChDir Statement

ChDir声明

Changes the current directory or folder.

更改当前目录或文件夹。

Syntax

句法

ChDir path

ChDir路径

The required path argument is a string expression that identifies which directory or folder becomes the new default directory or folder. The path may include the drive. If no drive is specified, ChDir changes the default directory or folder on the current drive.

必需的path参数是一个字符串表达式，用于标识哪个目录或文件夹成为新的默认目录或文件夹。路径可以包括驱动器。如果未指定驱动器，ChDir将更改当前驱动器上的默认目录或文件夹。

Since your main.py resides in C:\Users\maheshda\Tool\Tool\, use the following right before calling the Python script:

由于main.py位于C：\ Users \ maheshda \ Tool \ Tool \中，因此在调用Python脚本之前请使用以下权限：

ChDir "C:\Users\maheshda\Tool\Tool"

Or (since it is on drive C):

或者（因为它在驱动器C上）：

ChDir "\Users\maheshda\Tool\Tool"

#2

Extending on Wiktor Stribiżew's answer and comments, the sub below can be used to change the Current Directory in any case.

扩展WiktorStribiżew的回答和评论，以下子目录可用于在任何情况下更改当前目录。

Public Sub Change_Current_Directory(NewDirectoryPath as string)
    Dim CurrentDirectoryPath as string
    CurrentDirectoryPath = curdir
    if Strings.StrComp(Strings.Left(CurrentDirectoryPath,2), Strings.Left(NewDirectoryPath,2), vbTextCompare) then
        ChDrive Strings.Left(NewDirectoryPath,1)
    end if
    ChDir NewDirectoryPath
End Function

Happy coding!

快乐的编码！

#3

FCastro, why did you bother with that StrComp line? And, for that matter, why bother with the Strings object?

FCastro，你为什么要打扰那个StrComp系列？而且，就此而言，为什么要烦扰Strings对象呢？

I suppose if the drive were external and hadn't been accessed yet it might take a moment, but as long as the path is not expected to be a USB/CD/DVD/etc..., then:

我想如果驱动器是外部的并且尚未被访问但可能需要一些时间，但只要路径不是USB / CD / DVD /等......，那么：

Public Sub Change_Current_Directory(NewDirectoryPath as string)
    ChDrive Left(NewDirectoryPath,1)
    ChDir NewDirectoryPath
End Function

#1