从 Excel vba shell 更改工作目录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32276705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing working directory from Excel vba shell
提问by Wiktor Stribi?ew
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!
如何从宏中更改工作目录？谢谢！
回答by Wiktor Stribi?ew
This is a trivial task in VBA, use ChDir:
这是 VBA 中的一项微不足道的任务，请使用ChDir：
ChDir Statement
Changes the current directory or folder.
Syntax
ChDirpathThe 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,
ChDirchanges the default directory or folder on the current drive.
ChDir 声明
更改当前目录或文件夹。
句法
ChDir小路必需的路径参数是一个字符串表达式，用于标识哪个目录或文件夹成为新的默认目录或文件夹。路径可能包括驱动器。如果未指定驱动器，则
ChDir更改当前驱动器上的默认目录或文件夹。
Since your main.pyresides in C:\Users\maheshda\Tool\Tool\, use the following right beforecalling 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"
回答by FCastro
Extending on Wiktor Stribi?ew's answer and comments, the sub below can be used to change the Current Directory in any case.
扩展Wiktor Stribi?ew的回答和评论，下面的 sub 可用于在任何情况下更改当前目录。
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!
快乐编码！
回答by Joe Marfice
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
回答by NOTSermsak
If your behavior is to open an excel window and then open your recent file, please note that you should not forget to add change Drive and then change Directory into your VBA code.
如果您的行为是打开一个 excel 窗口然后打开您最近的文件，请注意您不应忘记添加更改驱动器，然后将目录更改为您的 VBA 代码。
Cause the Excel always start with the default Directory even it's just open your recent file !
因为 Excel 总是以默认目录开头，即使它只是打开您最近的文件！
Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts As Variant
ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
Application.PathSeparator)
ChDrive ThisWorkbookPathParts(LBound(ThisWorkbookPathParts))
ChDir ThisWorkbookPath
