How to execute Batch File while passing parameters from Excel

Question

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.

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

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

I'm not too sur how to call Shell functions, but just curious - could you post a sample of what it should look like, if called correctly? What kind of values are sid and user? Would a working file be "CMD.EXE /c C:\Users\shashank.b03\Desktop\test_CMD.bat"SID USER PASSWORD ? (Do you perhaps need a delimiter in there?) For a test, add Dim myCMD as String then myCMD = "CMD.EXE /c C:\Users\shashank.b03\Desktop\test_CMD.bat" & sid &" "& user &" "& password &" " and then debug.print myCMD and see what the command that would be called actually looks like, and see if that's accurate. — BruceWayne
– BruceWayne, Commented Jan 20, 2017 at 4:50
Thanks for replying Bruce, sid, user are string based values for an oracle database server. The batch file does connect to the server and performs tasks. I want the excel to serve as the front end for the application, — Shashank Bhardwaj
– Shashank Bhardwaj, Commented Jan 20, 2017 at 4:55
Forgive us, you did not answer @BruceWayne's very important question. What should command line look like if called correctly given example cell data? — Parfait
– Parfait, Commented Jan 23, 2017 at 14:42
Hi Parfait, The Command line is supposed to start the export of database using the passed parameters. It should connect to the SQL-Plus and start exp. For basic testing of the batch file parameters what I am doing is echoing the parameters out. echo off echo enter user set /p user= echo %user% echo enter password set /p password= echo %password% echo enter sid set /p sid= echo %sid% echo %user% %password% %sid% >> C:\users\shashank.b03\desktop\test.txt — Shashank Bhardwaj
– Shashank Bhardwaj, Commented Jan 24, 2017 at 5:02

Old Nick · Accepted Answer · 2017-01-23 14:05:00Z

3

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.

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.

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

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.

Save the following as test.bat

set arg1=%1
echo HELLO %1!
pause

Put this code inside a button or some other component in 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.

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.

Hope this helps

edited Jan 23, 2017 at 14:05

answered Jan 20, 2017 at 13:27

Old Nick

1,14510 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shashank Bhardwaj Over a year ago

Hi Nick, I tried this code..the problem that is happening it is calling the batch files but not passing the arguments. After opening the batch file we have to again feed the parameters

Old Nick Over a year ago

Hi Shashank, sorry to ask an obvious question but when you pass the arguments are you prefixing them with the "-" character?

Shashank Bhardwaj Over a year ago

No Nick, I am just a beginner in VBA scripting as of now.

Old Nick Over a year ago

I will edit my answer to show you how to get the parameter to work. :)

Shashank Bhardwaj Over a year ago

Thanks a Lot Nick, Actually the issue was I had " set /p " command used for parameters. It was not allowing me to pass the parameters as it expected the parameter from the user itself. It is working now.

|

Collectives™ on Stack Overflow

How to execute Batch File while passing parameters from Excel

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related