4

I want to open a cmd.exe and then execute a few lines of code.

I searched the web for some examples.

Code I tried modifying:

strToPrint = "Hello World!"
Shell "cmd.exe /K echo " & strToPrint, vbNormalFocus

I found How to write message to command window from VBA?

I tried multiple lines of coding, but the lines are executed in different command windows:

Sub CMD_VBA_Script()
    Shell "cmd.exe /K echo Hello World!", vbNormalFocus
    Shell "cmd.exe /K color 0a", vbNormalFocus
End Sub

I understand when I call the Shell two times, that it will execute two times.

My goal is to call the following script from VBA:

@echo off
    title Matrix
    color 0a
    mode 1000

    :a
    echo %random%%random%
    goto a

How can I execute multiple lines of code from VBA in command prompt?

Community
  • 1
  • 1
Dubblej
  • 107
  • 2
  • 3
  • 10

4 Answers4

5
MyFile = "C:\cmdcode.bat"
fnum = FreeFile()
Open MyFile For Output As #fnum
Print #fnum, "@echo off"
Print #fnum, "title Matrix"
Print #fnum, "color 0a"
Print #fnum, "mode 1000"
Print #fnum, ""
Print #fnum, ":a"
Print #fnum, "echo %random%%random%"
Print #fnum, "goto a"
Close #fnum


' Run bat-file:
Shell MyFile, vbNormalFocus


' optional, remove bat-file:
Kill "C:\cmdcode.bat"

So in short. You need to create a bat-file that you run. If you don't need the bat-file after it's done you can delete it with Kill

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • Thank you, it works great! I added 'Application.Wait (Now + #12:00:03 AM#)' in the code before the Kill command, now the file got executed for 3 seconds and then it's killed. – Dubblej Jul 23 '16 at 09:41
  • No problem! I usually use the sleep() function when I want to delay the code. `Sleep(3000)` a bit easier to write. – Andreas Jul 23 '16 at 21:57
3

You can write something like this -

Call Shell("cmd.exe /c "cd C:\Users\username\local\temp\" & " && temp.vbs" & " && mkdir newfolder")

This executes 3 lines of command:

  1. Change directory
  2. Execute a vbs file
  3. Make a new folder
girlvsdata
  • 1,596
  • 11
  • 21
ronak
  • 31
  • 1
1

It looks like you want to execute a Command Prompt Batch file - batches are stateful, so simply executing each line separately is not going to have the same effect as executing the batch as a whole.

Two other alternative approaches involve faking batch execution by instructing an interactive instance of cmd.exe to execute commands as-they're-entered by some automated process: either sending window messages, or piping into the cmd process' stdin stream. I do not recommend either of these approaches because of their inherent flakiness (i.e. dependency on undocumented behavior)

So the best approach would be to just execute a batch file as it's intended - you would need to write the batch to a temporary file first, and then execute it:

Using the code from here: https://support.microsoft.com/en-us/kb/195763

Dim tempFileName As String
tempFileName = CreateTempFile("SomeBatch.cmd")

WriteToBatchFile( tempFileName ) ' you will have to write to the temp batch file yourself here

Shell "cmd.exe /c """ & tempFileName & """", vbHide, True ' Run the batch file, then cmd.exe will terminate.  The Shell function will block until cmd is closed

Kill tempFile ' delete the temp batch file
Dai
  • 141,631
  • 28
  • 261
  • 374
0

Use "cmd.exe /K" option, and '&' to connect multiple commands.

In C:\Users\%USERNAME%, create test.bat with two simple commands:

echo %PATH%
dir

Here is the complete sample:

Sub Demo_Multi_Commands()
    Cmd1 = "cd " & Environ("USERPROFILE")
    Cmd2 = "test.bat"
    Connector = " & "
    Commands = "cmd.exe /K " & Cmd1 & Connector & Cmd2 & Connector & "systeminfo"
    Debug.Print Commands
    pid = Shell(Commands, vbNormalFocus)
End Sub

Environment: Windows 10 Enterprise, VBA 7.1

Jonathan L
  • 9,552
  • 4
  • 49
  • 38