Run DOS Command from within VBA

[NewOrderFac33]

Good afternoon,

I have a VBA procedure that runs a batch file which in turn creates a zip file:

Sub RunDOSBatch()
    Dim Param As String
    Dim RetVal
    Param = "C:\PetesStuff\ZipMe.bat"
    RetVal = Shell(Param)
End Sub

ZipMe.bat contains the line

"C:\Program Files (x86)\7-Zip\7z.exe" a "c:\PetesStuff\MyZip" "C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*"

Is it possible to run this command from within the procedure without having to invoke the batch file?
I've given it a go, but I'm getting a bit mixed up with where the quadruple quote marks need to go.

As usual, thanks in advance.

Pete

 

[NewOrderFac33]

...or, to use it as you intended:

Sub CreateTheBatchFileFSO()


    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim LineString As String
    Dim BatchFileFullName As String
    
    'fso.DeleteFile ("C:\PetesStuff\PetesInfo.txt")
    Set ts = fso.CreateTextFile("C:\PetesStuff\ZipMe.bat", True) 'True to overwrite existing file
    LineString = QMe("C:\Program Files (x86)\7-Zip\7z.exe") & " a " & QMe("c:\PetesStuff\MyZip") & " " & QMe("C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*")
    ts.WriteLine (LineString)
    ts.Close
End Sub


Private Function QMe(text As String) As String
    QMe = Chr(34) & text & Chr(34)
End Function

Thanks again!

 

[NewOrderFac33]

On a roll now - a non-FSO version, uasing the same function as previous example...

Sub CreateTheBatchFile()
    
    LineString = QMe("C:\Program Files (x86)\7-Zip\7z.exe") & " a " & QMe("c:\PetesStuff\MyZip") & " " & QMe("C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*")
    BatchFileFullName = "C:\PetesStuff\ZipMe.bat"
    Open BatchFileFullName For Output As #1
        Print #1, LineString
    Close #1
    'RetVal = Shell("cmd /k  " & BatchFileFullName, vbNormalFocus) 'Leave command window open
    RetVal = Shell(BatchFileFullName) 'Close command window


End Sub

Private Function QMe(text As String) As String
    QMe = Chr(34) & text & Chr(34)
End Function

My journey to the Dark Side is now complete.

 

[apo]

Yeap... pretty sure the bump brought it to my attention (not that I helped much ,LOL)

Praise the bump

 

[John_w]

And running directly with Shell:

Sub ZipEm()
    Dim command As String
    command = QMe("C:\Program Files (x86)\7-Zip\7z.exe") & " a " & QMe("c:\PetesStuff\MyZip") & " " & QMe("C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*")
    Shell "cmd /k " & Q(command), vbNormalFocus
End Sub

 

[NewOrderFac33]

.

 

[NewOrderFac33]

OMGOMGOMG - and use /c to close the DOS window, as /k leaves it open.

My sad and lonely life is complete...

BTW, third line should read QMe(Command), not Q(Command)

 

[apo]

Hi.. try

Replace /k with /c

 

[Rick Rothstein]

OMGOMGOMG - and use /c to close the DOS window, as /k leaves it open.

If it helps you any, here is everything I know about using the Shell function in VBA (it is from an old answer I once posted which will explain why some of it is written the way it is)...

The following is a compilation of several posts I've given in the past regarding the Shell command. Your question is addressed in there; the remainder is for your consideration.

You can use the Shell command. To execute internal DOS command (Dir, Copy, etc. as well as redirection of screen output), the command processor must be specified (using the Environ$ function and "comspec" as its argument returns the correct command processor path on NT and non-NT systems) . Specifying the command processor is safe & generic and will work with non-internal commands also. That syntax, using an XCopy command as an example is:

Shell Environ$("comspec") & " /c xcopy """ & Source & """ """ & Destination & """ " & Option, vbHide

You set the Source and Destination (string variables) to the appropriate paths and the Option (string variable), if any, which can be found by opening a Command Prompt window and typing xcopy /?. (Note: You can type /? after any DOS command at a DOS prompt to list the available options for that command.) One more example would be to list all the files in a directory including subdirectories and subdirectories of subdirectories and all of their files.

CommandLine = "dir """ & FileSpec & """ /s/b > """ & RedirectTo & """"
Shell Environ$("comspec") & " /c " & CommandLine, vbHide

Here, the output of a Dir command is redirected to a file-path you specify in the RedirectTo (string variable). The /s/b are options to the Dir command that tell it to recurse throught its subdirectories and not to include header or summary information.

I used a variable for the file name so that I could more easily explain the benefit of encasing it in quotemarks. If you redirect to a file that has spaces in its name, or if there are spaces in the path specification itself, then the filename *must* be quoted to protect the spaces from DOS's desire to use them as delimiters. (That's what all those quotemarks in the Shell statement are for.) If the filename doesn't have spaces in it, the quotes aren't necessary BUT they don't hurt either. Hence, the above will work with either.

As for your PING question, something like the following should work:

strIP = "4.17.23.1"
Shell Environ$("comspec") & " /c ping " & strIP & " > """ & RedirectFile & """", vbHide

Although you didn't specify it in your original post, I assume you want to use vbHide for the optional 2nd parameter to Shell. This hides the DOS window so that your user doesn't see it. If you want the DOS window to remain visible, you would use the vbNormalFocus BUT you must use a /k instead of a /c for the command processor argument. Basically, the /c tells the command processor "here comes a command and, when its finished executing, close the DOS shell it is running in" whereas the /k also tells the command processor that a command follows, but it instructs it to leave the DOS session running.

The above assumes you do NOT have to wait for this file to be completely written before your code continues executing. If you have to work with this file right after it is created, consider one of these (which makes your program wait until the DOS process is finished):

MICROSOFT'S OFFICIAL WAY
========================
See this link... How To Use a 32-Bit Application to Determine When a Shelled Process Ends
Note: This method doesn't use Shell -- it uses CreateProcessA.

FAST AND DIRTY METHOD
======================
Paste these lines into a Standard Module:

Private Declare Function OpenProcess Lib "kernel32" _
                        (ByVal dwDesiredAccess As Long, _
                         ByVal bInheritHandle As Long, _
                         ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" _
                        (ByVal hObject As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" _
                        (ByVal hHandle As Long, _
                         ByVal dwMilliseconds As Long) As Long

Call your Shell command with the appropriate Shell arguments placed in the parentheses:

PID = Shell( **Put Shell Arguments Here** )

And finally, paste the following IMMEDIATELY after the PID=Shell statement above (making sure to handle the possible error where indicated; i.e. stop the code from falling through to your other commands if the Shell failed):

If PID = 0 Then
  '
  'Handle Error, Shell Didn't Work
  '
Else
  hProcess = OpenProcess(&H100000, True, PID)
  WaitForSingleObject hProcess, -1
  CloseHandle hProcess
End If

And finally, about your issue with quote marks. The rule is quite simple, really... if you have a text constant (indcated by text inside quote marks), then any quote marks inside the delineated text constant must be doubled up. So, if you wante to assign this text string to a variable...

Look at the "word" that I quoted.

then the assignment would look like this...

SomeVariable = "Look at the ""word"" that I quoted."

The two blue (outer) quote marks are used to delineate that what is between them is a text constant. Any quote marks contained between them (the red ones) must be doubled-up in order for VB to be able to distinguish them from the outer (blue) quote marks delineating the text constant (if you used single quote marks inside the text, VB would think they were delineating a text constant, not that they were actual quote marks). Some confusing comes about when a quote mark starts or ends a text constant... the key is to remember the above rule and keep straight which quote marks are delineating the text constant and which are the quote marks that are real quote marks. Here is an example to help. Remember this from above...

SomeVariable = "Look at the ""word"" that I quoted."

What if I actually wanted this sentence instead...

"word" that I quoted

It is the same as above without the 'Look at the ' part, take this...

SomeVariable = "Look at the ""word"" that I quoted."

and remove those words... what is left? This is...

SomeVariable = """word"" that I quoted."

Note what you have at the beginning... three quote marks... the blue one is the opening delimiter for the text constant and the two red ones are the doubled-up quote mark indicating an internal quote mark. A similar analysis explains why a text constant that ended with a quote mark would also have three quote marks in a row as the end.

 

[apo]

Wow.. fantastic info..

 

[NewOrderFac33]

Rick - some light bedtime reading - thank you very much for taking the time to post this!
Have a good evening/afternoon/whatever it is over there - my geography's not up to much!