无法在VBA中从WScript Shell运行DIR？ [英] Can't run DIR from WScript Shell in VBA?

问题描述

我在很多VBA项目中使用了以下功能。我最初添加了对Windows脚本宿主对象模型的引用，以利用Intellisense，但是后来切换到后期绑定，所以我不必引用一堆东西。

 私有函数RunCMD（ByVal strCMD As String）As String 
'运行提供的命令
 Dim oShell As Object'New WshShell 
 Dim cmd As Object' WshExec 
 Dim x As Integer 
 Const WshRunning = 0 
 
错误GoTo wshError 
 
x = 0 
 RunCMD =Error
设置oShell = CreateObject（Wscript.Shell）
设置cmd = oShell.Exec（strCMD）
'Debug.Print strCMD 
'停止
尽管cmd.Status = WshRunning 
睡眠100'为1/10秒
x = x + 1 
如果x> 1200然后'我们等了2分钟杀死
 cmd.Terminate 
 MsgBoxError：Timed Out，vbCritical，Timed Out
 End If 
 Loop 
 
 RunCMD = cmd.StdOut.ReadAll& cmd.StdErr.ReadAll 
设置oShell = Nothing 
设置cmd = Nothing 
退出函数
 
 wshError：
错误恢复下一步
 RunCMD = cmd.StdErr.ReadAll 
简历Next 
结束函数
  

它的工作很好当您执行
RunCMD（ping www.bing.com）或
RunCMD（winrs -r ：& strHost&reg query hklm\system\currentcontrolset\services\cdrom / v start）

然而 RunCMD（Dir c：\config * / a：-d / b / d / s）失败，而 cmd.StdErr。 ReadAll 给出一个Object Variable或With Block not set错误。即使一个简单的 RunCMD（Dir）失败。

为什么DIR使WScript shell崩溃？更重要的是，如何使用CMD的DIR函数（而不是VBA的DIR函数！）来获取匹配搜索模式的文件列表？

解决方案

如果您使用cmd / c前导您的dir命令并将您的DOS命令包含在双引号中

  RunCmd（cmd / cDIR）
  pre> 
 
 
或
  RunCmd（cmd / c c：\config * / a：-d / b / d / s）
  
 
I use the following function in a lot of my VBA projects. I initially added the reference to Windows Script Host Object model to take advantage of Intellisense, but then switched to late binding so I didn't have to reference a bunch of stuff.
Private Function RunCMD(ByVal strCMD As String) As String
    'Runs the provided command
    Dim oShell As Object 'New WshShell
    Dim cmd As Object 'WshExec
    Dim x As Integer
    Const WshRunning = 0

    On Error GoTo wshError

    x = 0
    RunCMD = "Error"
    Set oShell = CreateObject("Wscript.Shell")
    Set cmd = oShell.Exec(strCMD)
    'Debug.Print strCMD
    'Stop
    Do While cmd.Status = WshRunning
        Sleep 100 'for 1/10th of a second
        x = x + 1
        If x > 1200 Then 'We've waited 2 minutes so kill it
            cmd.Terminate
            MsgBox "Error: Timed Out", vbCritical, "Timed Out"
        End If
    Loop

    RunCMD = cmd.StdOut.ReadAll & cmd.StdErr.ReadAll
    Set oShell = Nothing
    Set cmd = Nothing
    Exit Function

wshError:
    On Error Resume Next
    RunCMD = cmd.StdErr.ReadAll
    Resume Next
End Function
It works great when you do something like 
RunCMD("ping www.bing.com") or 
RunCMD("winrs -r:" & strHost & " reg query hklm\system\currentcontrolset\services\cdrom /v start")

However RunCMD("Dir c:\config* /a:-d /b /d /s") fails, and cmd.StdErr.ReadAll gives an Object Variable or With Block not set error. Even a simple RunCMD("Dir") fails.

Why does DIR make the WScript shell crap out? More importantly, how can I use CMD's DIR function (not VBA's DIR function!) to get a list of files that match a search pattern? 
解决方案
Does it work if you preface your dir command with "cmd /c " and wrap your DOS command in double quotes,  like 
RunCmd("cmd /c ""DIR""")
or 
RunCmd("cmd /c ""Dir c:\config* /a:-d /b /d /s""")


                        
这篇关于无法在VBA中从WScript Shell运行DIR？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！