从 Excel VBA 宏执行 .bat 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13530371/
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
Execute .bat file from Excel VBA Macro
提问by Matteo Cuellar Vega
Im having a problem with my excel vba macro. I need it to execute a batch file which is in the same folder as the excel workbook. The code works well sometimes. I don't know whats causing the error. Here's the code:
我的 excel vba 宏有问题。我需要它来执行与 Excel 工作簿位于同一文件夹中的批处理文件。代码有时运行良好。我不知道是什么导致了错误。这是代码：
Sub writebatch()
Sheets("code").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.path & "\code.bat",
FileFormat:=xlTextPrinter, CreateBackup:=False
Application.DisplayAlerts = True
ThisWorkbook.Saved = True
Shell "cmd.exe /k cd " & ThisWorkbook.path & "&&code.bat"
Application.Quit
End Sub
It writes the batch file, but then doesn't execute it. Only once I got the command window to not close and it said the code.bat file could not be found. So the changedir command worked. Is it possible to run cmd.exe and run the code.bat with the relative path without having to changedir?
它写入批处理文件，但不执行它。只有当我让命令窗口没有关闭时，它才说找不到 code.bat 文件。所以changedir 命令起作用了。是否可以运行cmd.exe并使用相对路径运行code.bat而无需更改？
回答by ElektroStudios
First of all, when you launch a CMD instance you need to enclose the entire CMD argument if you use any type of operators (&):
首先，当您启动 CMD 实例时，如果您使用任何类型的运算符 (&)，则需要将整个 CMD 参数括起来：
CMD /K "command1 & command2"
And then enclose the sub-internal arguments, like this:
然后附上子内部参数，如下所示：
CMD /K "command "path with spaces" & command"
So you need to do something like this:
所以你需要做这样的事情：
Shell "cmd.exe /k ""cd " & """ & ThisWorkbook.path & """ & " && code.bat"""
Notice I've used """ to escape a quote, but I don't know the way to escape a quote in VBA.
请注意，我使用 """ 来转义引号，但我不知道在 VBA 中转义引号的方法。
PS: remember to also enclose the code.batif you have spaces, but ONLY if you have spaces.
PS：如果您有空格，请记住也要附上code.bat，但前提是您有空格。
回答by Bali C
I'm pretty sure the problem is down to this line
我很确定问题出在这一行
Shell "cmd.exe /k cd " & ThisWorkbook.path & "&&code.bat"
You need a space in front of the &&to separate it from the cdcommand and after it to separate it from the code.bat.
您需要在 前面有一个空格&&将它与cd命令分开，在它之后需要一个空格将它与code.bat.
Shell "cmd.exe /k cd " & ThisWorkbook.path & " && code.bat"
回答by user3350522
Shell "cmd.exe /k cd /d" & ThisWorkbook.path & "&& code.bat"
Shell "cmd.exe /k cd /d" & ThisWorkbook.path & "&& code.bat"
here, without /d cmd will open in document folder. by /d it will open in d drive, you may change this as per your easy.
在这里，没有 /d cmd 将在文档文件夹中打开。通过 /d 它将在 d 驱动器中打开，您可以根据自己的轻松进行更改。
回答by Andrew Slentz
One way to insert a quote(") into a string is by using the character code conversion function Chr(34), 34 being the ASCII value for quotes(")
将quote(")插入字符串的一种方法是使用字符代码转换函数Chr(34)，34是quotes(")的ASCII值
