通过 Excel VBA 运行 Shell 对象/命令时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21267283/
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
Error running Shell object / commands through Excel VBA
提问by brentf
I am running a C++ program through my VB code, and I am having trouble getting my code to run on a shared drive vs. on a local computer. My program generates a set of assumptions, then runs those assumptions through a C++ model, then picks up the model output and prepares it for viewing in the VB workbook.
我正在通过我的 VB 代码运行 C++ 程序，但我无法让我的代码在共享驱动器上与在本地计算机上运行。我的程序生成一组假设，然后通过 C++ 模型运行这些假设，然后获取模型输出并准备在 VB 工作簿中查看。
The code below works fine when I have the workbook saved in a local directory on my C drive, but when I upload it to my company's shared drive, I get the following error:
当我将工作簿保存在 C 驱动器的本地目录中时，下面的代码工作正常，但是当我将其上传到我公司的共享驱动器时，出现以下错误：
"Run-time error '-2147024894 (80070002)': Method 'Run' of object 'IWshShell3' failed"
“运行时错误‘-2147024894 (80070002)’：对象‘IWshShell3’的方法‘Run’失败”
Code:
代码：
'---------------------------------------------------------
' SECTION III - RUN THE MODEL AS C++ EXECUTABLE
'---------------------------------------------------------
Dim ModelDirectoryPath As String
Dim ModelExecutableName As String
Dim ModelFullString As String
' First build the command string
Application.StatusBar = "Running C++ Model..."
ModelDirectoryPath = Range("ModelFilePath").value
ModelExecutableName = Range("ModelFileName").value
ModelFullString = ModelDirectoryPath & ModelExecutableName
ModelFullString = ModelFullString & " " & ScenarioCounter & " " & NumDeals _
& " " & ModelRunTimeStamp & " " & Settle_YYMMDD
' Run the program
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Integer
errorCode = wsh.Run(ModelFullString, windowStyle, waitOnReturn)
If errorCode = 0 Then
' MsgBox "C++ Model Completed without Errors."
Else
MsgBox "Program exited with error code " & errorCode & "."
End If
Application.StatusBar = "C++ Model Complete"
Any thoughts?
有什么想法吗？
回答by sobersoup
The error doescome from the directory having a space in it:
错误确实来自其中有空格的目录：
C:\Users\myname\this is my folder\myexe.exe
A simple workaround does the trick:
一个简单的解决方法可以解决问题：
wsh.Run(Chr(34) & YourFullPathDirectoryWithSpaces & "\myexe.exe" & Chr(34))
Chr(34)is a double quote.
Chr(34)是双引号。
There was an issue with .Runtaking a two line property.
有有一个问题，.Run采取两行属性。
Tested it on Excel 2010/Win32.
在 Excel 2010/Win32 上对其进行了测试。
回答by Jezz81
Had the same problem, alternative solution:
有同样的问题，替代解决方案：
wsh.CurrentDirectory = exePath
wsh.Run(exeName & " " & cmdArgs, windowStyle, waitOnReturn)
Key point being to set the CurrentDirectory property of the shell
关键点是设置 shell 的 CurrentDirectory 属性
回答by Vrun
found this great referenceexplaining triple quotes when there is a space in the path. Here is a portion of it in case it gets taken down or moves:
找到了这个很好的参考，解释了路径中有空格时的三重引号。这是它的一部分，以防它被取下或移动：
if you had wanted to open a file
c:\My Files\Text File.txtand your shell required that this be wrapped in"", then you'd write the string like this
PID = Shell("notepad ""c:\My Files\Text File.txt""", vbNormalFocus)The same goes for any path you need to specify for the actual command/script name. In this example I'm calling a batch file (Text Parser.bat) to process the text file (Text File.txt):
PID = Shell("""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""", vbNormalFocus)All of those
"""look a bit strange but let me explain. The first and last"mark the beginning and end of the string that specifies the program being called, including any parameters, switches and file(s) it will use:
"""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt"""If we remove those"we are left with the Program string itself, which is composed of two separate strings, one for the path\batch file, and the other for the path\file the batch file will use .
""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""When this is passed to the Shell one of the double"is removed so what is actually seen in the Shell is
"c:\My Scripts\Text Parser.bat" "c:\My Files\Text File.txt"Which looks like two normally delimited strings.
如果你想打开一个文件
c:\My Files\Text File.txt并且您的外壳要求将其包裹在 中""，然后您可以像这样编写字符串
PID = Shell("notepad ""c:\My Files\Text File.txt""", vbNormalFocus)这同样适用于您需要为实际命令/脚本名称指定的任何路径。在这个例子中，我调用一个批处理文件 (Text Parser.bat) 来处理文本文件 (Text File.txt)：
PID = Shell("""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""", vbNormalFocus)所有这些
"""看起来都有些奇怪，但让我解释一下。第一个和最后一个"标记指定被调用程序的字符串的开头和结尾，包括它将使用的任何参数、开关和文件：
"""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt"""如果我们删除那些，"我们将留下 Program 字符串本身，它由两个单独的字符串组成，一个用于路径\批处理文件，另一个用于批处理文件将使用的路径\文件。
""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""当这被传递给壳牌时，其中一个双倍"被删除，所以在壳牌中实际看到的是
"c:\My Scripts\Text Parser.bat" "c:\My Files\Text File.txt"这看起来像两个通常分隔的字符串。
回答by brentf
I have determined that the problem appears entirely due to the directory structure including a space. There does not appear to be any issue relating to the local vs. shared directory.
我已经确定问题的出现完全是由于包含空格的目录结构造成的。似乎没有任何与本地与共享目录相关的问题。
If someone knows an easy fix to get around the directory having a space in it, please let me know!
如果有人知道绕过包含空格的目录的简单方法，请告诉我！
回答by bnqprv
I have noticed that the problem only seems to occur when more than one argument is passed on to the program/script that is run.
我注意到这个问题似乎只有在将多个参数传递给运行的程序/脚本时才会发生。
If the second argument contains a filepath with a space, even when the necessary """" are present, the error occurs.
如果第二个参数包含一个带空格的文件路径，即使存在必要的 """"，也会发生错误。
Can't find any solution at this point...
目前找不到任何解决方案......
