How to Pass argument to batch file from Excel Macr

Question

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

In Excel Macros:

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)

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

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

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

Answer 1

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

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

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 - You are using strBatchName that you have not declared nor initialized

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

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 - Document the discussed configuration

In 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

In batch file

@echo off

    setlocal enableextensions disabledelayedexpansion
    set "n=%~1"

        setlocal enabledelayedexpansion
        echo(!n!
        endlocal

    endlocal

    pause