是否可以从批处理文件向VBA返回错误代码? [英] Is it possible to return error code to VBA from batch file?

问题描述

我的批处理文件:

SET username=%1
SET password=%2

net use "\\gessel\toys\name" %password% /user:shops\%username%
ECHO.%ERRORLEVEL%
:copy
Xcopy "\\gessel\toys\name\babytoys" "%appdata%\shops" /S /E
ECHO.%ERRORLEVEL%

IF ERRORLEVEL 0 goto disconnect
goto end
:disconnect 
net use "\\gessel\toys\name\babytoys" /delete
goto end
:end
EXIT /B %ERRORLEVEL%

我已经从VBA调用了上述批处理文件，其代码如下:

I have called above batch file from VBA and the code as follows:

call Shell(Environ$("COMSPEC") & " /c " & path & username & password, vbHide)

上面的代码很好用.但是我需要验证文件是否在VBA中被复制.假设客户输入了错误的用户名和密码，那么他将不会获得玩具信息.然后，我必须显示一个消息框，该消息框将消息显示为输入的信息错误" .为此，我尝试了如下代码:

The above code is works fine. But I need to validate whether the files are copied or not in the VBA. Suppose the customer entered his username and password wrongly then he won't get the toys info. Then I have to display a message box that display message as "entered information wrong". So for that I have tried the code like this:

sub submit_click

    Dim as error as integer
    error = Shell(Environ$("COMSPEC") & " /c " & path & username & password, vbHide)
    if error <> 0
        MsgBox "Provided info wrong", vbOKOnly, "Failure"
    end if
end sub

但是上面的代码不起作用.即使用户名和密码正确，它也始终返回该值.但是，如果我运行该批处理文件，它将正确返回值，例如正确的详细信息为0，错误的数据为2或4.请有人帮助我从批处理文件中捕获错误代码，并将其传递给VBA.

But the above code does not work. It always returns the value even the username and password is correct. But if I run the batch file it correctly returns value such as for correct details 0 and for wrong data is 2 or 4. Please anyone help me to capture error code from batch file and to pass it into VBA.

推荐答案

ERRORLEVEL 变量的值随每次命令执行而变化(或多或少).因此，当批处理文件中的代码正在执行时，每个命令都会生成一个更改.您需要存储该值以供以后处理，或者，在每种情况下，请在每个步骤中以指定的值退出:

The value of ERRORLEVEL variable changes with each command execution (more or less). So as the code in your batch file is executing, each command generates a change. You need to store the value for later processing or, in your case, exit with the adecuated value in each of the steps:

SET "username=%~1"
SET "password=%~2"

rem This will be tested for a errorlevel value of 1 or greater. In this case, 
rem no further processing will be done if errors found, so we return our own 
rem value to the calling process

net use "\\gessel\toys\name" %password% /user:shops\%username% 
if errorlevel 1 exit /b 100

rem We are connected, and will disconnect no matter if xcopy worked or failed
rem So, the result of the command will be stored to later return the adecuate value

Xcopy "\\gessel\toys\name\babytoys" "%appdata%\shops" /S /E
set "exitCode=%errorlevel%"

net use "\\gessel\toys\name\babytoys" /delete

EXIT /B %exitCode%

现在，在vba代码中，可以测试 error 变量的值100(我们从网络使用错误中返回的值)或从xcopy返回的任何值.

Now, in the vba code the error variable can be tested for value 100 (what we returned from errors in net use) or for any of the values returned from xcopy.

现在我们有一个可以正常工作的批处理文件，让我们转到Excel.

Now that we have a working batch file, let's to to Excel.

否， Shell 函数无法执行您所要求的操作. Shell 的返回值是正在运行的进程的ID. Shell 不等待进程结束，因此，它无法返回其退出代码.

NO, Shell function in VBA can not do what you are asking. The return value of Shell is the id of the running process. Shell do not wait for the process to end, so, it can not return its exit code.

但是，WshShell对象可以完成您需要的工作.

BUT, the WshShell object can do what you need.

Dim oSHELL, batchFile, user, password, exitCode
    Set oSHELL = VBA.CreateObject("WScript.Shell")
    batchFile="myBatchFile.cmd"
    user="myUserName"
    password="this is my password"

    ' Quote strings to avoid problems with spaces
    ' The arguments are WhatToRun, WindowStyle (0=hide), WaitForProcessToEnd
    exitCode = oSHELL.Run(""""+batchFile+""" """+user+""" """+password+"""", 0, True)

这篇关于是否可以从批处理文件向VBA返回错误代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！