如何从Excel宏传递参数到批处理文件？ [英] How to Pass argument to batch file from Excel Macros?

问题描述

从excel宏我试图传递参数到批处理文件。我的方法如下：

From excel macros I'm trying to pass arguments to batch file. My approach is given below

在Excel宏中：

passArgument()
Dim var, path As String
Dim wsh As Object
var = "#"
Set wsh = VBA.CreateObject("WScript.Shell")
path = "C:\batchFile" + "\" + "run.bat"
Shell(path + " " + var, vbNormalFocus)

我想在批处理文件中检索这个var，我遵循如下：

And I want to retrieve this var in batch file foe that I have followed like:

set "n=%~1"
echo %n%

但是它显示n本身而不是＃。我是新的这个帮助我，谢谢提前

But it's displaying n itself instead of #. I am new to this kindly help me out, Thanks in advance

推荐答案

1 - 您需要一个空格之间的批处理文件名和其参数

1 - You need a space between the batch file name and its parameters

2 - 如果批处理文件的路径中有空格，则需要引用完整路径和文件名

2 - If there is spaces inside the path to the batch file, full path and file name needs to be quoted

3 - 要使用 WScript.Shell 实例执行命令，您必须调用其运行方法。在你的代码 wsh.Run（.... 。如果你使用VBA的 Shell 方法，那么没有需要 WScript.Shell 。

3 - To execute a command using WScript.Shell instance, you must call its Run method. In your code wsh.Run( .... . If you are using the Shell method of VBA, then no need for WScript.Shell.

4 - 您正在使用 strBatchName 您尚未声明或初始化

4 - You are using strBatchName that you have not declared nor initialized

5 - 检索批处理文件中第一个参数的值的sintax为％1 ，没有关闭百分比符号

5 - The sintax to retrieve the value of the first parameter in a batch file is %1, with no closing percent sign

6 - 您没有设置名为 n 。后面有一个空格，应该写成 setn =％1或 setn =％〜 1删除第一个参数中的引号（如果存在），此行中包含的引号只是阻止在参数中添加空格或特殊字符的问题。

6 - You are not setting the value of a variable called n. There is a space after it. This should be written as set "n=%1" or set "n=%~1" to remove quotes in the first parameter if present. The included quotes in this line just prevent problems with added spaces in line or special characters inside argument.

EDITED - 记录讨论的配置

EDITED - Document the discussed configuration

在excel中

Public Sub TestRun()

    Shell quote("d:\test.cmd") + " " + quote("&<>,;!!"), vbNormalFocus

End Sub

Public Function quote(Text As String) As String

    quote = Chr(34) + Text + Chr(34)

End Function

批处理文件

@echo off

    setlocal enableextensions disabledelayedexpansion
    set "n=%~1"

        setlocal enabledelayedexpansion
        echo(!n!
        endlocal

    endlocal

    pause

这篇关于如何从Excel宏传递参数到批处理文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！