Using Excel VBA to run a CMD command

Question

I would like to use Excel VBA to run a CMD command.

The basic cmd command is:

"C:\Program Files\example program.exe" -w C:\Program files\outputfile.file "dir1" "dir2" "dir n+1"

The first part is the location of the program that will merge the files together. The second part is the file location of the outputted merged files. And the "dir1".... is the files that will be merged together.

I have code that lists the files to be merged but struggling to get the CMD code to get it so it does what I want as mentioned above. I have tried the following:

   Sub RunCMD()

        Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")
        Dim waitOnReturn As Boolean: waitOnReturn = True
        Dim windowStyle As Integer: windowStyle = 1
        Dim locationofprogram as string
       'dir of program that will do the merge
        Dim outputfolder as string
       'dir of where the merged file will go
        Dim listoffiles as string
       'list of files to be merged

        wsh.Run "cmd.exe /S /C locationofprogram & " " & "-w" & " " & outputfolder & " " & listoffiles, windowStyle, waitOnReturn

    End Sub

Thanks for the help!

Andreas · Accepted Answer · 2017-05-24 16:34:14Z

3

You can always create a .bat file, run the file, then delete it.

MyFile = "C:\Bat-File\Bat-File.bat"

fnum = FreeFile()
Open MyFile For Output As #fnum
Print #fnum, "C:\Program Files\example program.exe -w C:\Program files\outputfile.file dir1 dir2 dir n+1"    
Close #fnum

Shell MyFile, vbNormalFocus

' wait 10 seconds to let bat file finnish
newHour = Hour(Now()) 
newMinute = Minute(Now()) 
newSecond = Second(Now()) + 10 
waitTime = TimeSerial(newHour, newMinute, newSecond) 
Application.Wait waitTime

' delete bat-file
kill MyFile

I can't test that example program runs without the " around the dir´s so you have to see what happens.
But if it does not work you need to double enclose the " or use & CHR(34) &

answered May 24, 2017 at 16:34

Andreas

24k7 gold badges34 silver badges75 bronze badges

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

Comments

CLR · Accepted Answer · 2017-05-24 16:02:21Z

0

If in doubt, send the text to the Immediate Window to see if it still matches the syntax using:

 Debug.Print "cmd.exe /S /C " & locationofprogram & " " & "-w" & " " & outputfolder & " " & listoffiles

If that works, use:

 wsh.Run "cmd.exe /S /C " & locationofprogram & " " & "-w" & " " & outputfolder & " " & listoffiles, windowStyle, waitOnReturn

answered May 24, 2017 at 16:02

CLR

12.3k1 gold badge13 silver badges31 bronze badges

3 Comments

BAW331 Over a year ago

Thanks CLR for the help. I found that whenever I ran that code, it wouldn't do anything. If I put "ipconfig", it would work but with my long string, it wouldn't parse into the cmd command line. But thanks for the help!

CLR Over a year ago

It should work. The only thing you have to watch out for is spaces in the filename or folder names. If there are spaces, you have to wrap paths in double-quotes. Never mind though, it looks like you found a workaround but FYI there is nothing wrong with using the shell in the way you originally described if you get the syntax right.

BAW331 Over a year ago

hhhmm I guess I should have spend a bit more time with the code above as its simpler than what I used in the end. Hopefully anyone who stumbles on this thread will find it useful tho! Cheers CLR!

Collectives™ on Stack Overflow

Using Excel VBA to run a CMD command

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related