如何在从Excel中传递参数时执行批处理文件

I am trying run a batch file placed at a particular path. The file requires user inputs for which I want the parameters to be passed from Excel cells. This execution of the batch file within Excel should happen by usage of click command button.

我正在尝试运行位于特定路径的批处理文件。该文件需要用户输入，我希望从Excel单元格中传递参数。在Excel中的批处理文件的执行应该通过单击命令按钮来执行。

I am new to VBA. I tried the following code, but on clicking the button nothing is happening.

我是VBA的新手。我尝试了下面的代码，但是在点击按钮时什么也没有发生。

Private Sub CommandButton2_Click()

   sid = Excel.Worksheets("Sheet1").Range("I8").Value
   user = Excel.Worksheets("Sheet1").Range("I9").Value
   Password = Excel.Worksheets("Sheet1").Range("I10").Value
   msg = "hi"
   Shell ("CMD.EXE /c C:\Users\shashank.b03\Desktop\test_CMD.bat" & sid &" "& user &" "& password &" ")

End Sub

1 个解决方案

#1

Here is an example which I have tested and should work fine for you. It just calls the shell command and passes it a command string.

这是我测试过的一个例子，应该对你有用。它只调用shell命令并传递命令字符串。

You can change the path where your batch file is in the string & if you don't want to show the shell window when you're running this use vbHide instead of vbNormalFocus.

您可以更改您的批处理文件在字符串中的路径&如果您不想在运行时显示shell窗口，请使用vbHide而不是vbNormalFocus。

You'll just have to change this a bit to put the cell values into the sid, user and password variables.

您只需稍作修改，就可以将单元格值放入sid、user和password变量中。

Hope this helps.

希望这个有帮助。

Dim sid As String
Dim user As String
Dim password As String

CommandString = "c:\test.bat" + " " + sid + " " + user + " " + password
Call Shell("cmd.exe /c" & CommandString, vbNormalFocus)

Here is a more basic example of using parameters and a batch file from shell.

下面是一个更基本的示例，它使用了shell中的参数和批处理文件。

Save the following as test.bat

保存如下作为test.bat

set arg1=%1
echo HELLO %1!
pause

Put this code inside a button or some other component in excel;

将此代码放在excel中的按钮或其他组件中;

Private Sub CommandButton1_Click()
Dim sid As String
sid = "Shashank"
CommandString = "c:\test.bat" + " " + sid
Call Shell("cmd.exe /c" & CommandString, vbNormalFocus)
End Sub

Make sure that the path where the batch file is saved is the same as the one in commandstring.

确保保存批处理文件的路径与commandstring中的路径相同。

When this is run, you'll see the string held in the variable sid is passed to the batch file and used. You should be able to get it working from here.

运行时，您将看到变量sid中保存的字符串被传递到批处理文件并使用。你应该能让它在这里工作。

Hope this helps

希望这有助于

#1