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]

I've also looked at creating the batch file dynamically, but am again getting into grief with the quadruple quotes:

Sub CreateTheBatchFile()


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

 

[NewOrderFac33]

...and even creating the batch file the non-FSO way:

Sub CreateTheBatchFile()
    
    LineString = """"C:\Program Files (x86)\7-Zip\7z.exe"""" & "a" & """"c:\PetesStuff\MyZip"""" &  """"C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*""""
    BatchFileFullName = "C:\PeteStuff\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

But still to no avail!

 

[DeusXv]

I wish I could help, however all I can offer you is a simple

"Bump"

Hopefully someone else will see it and know what to do.

 

[NewOrderFac33]

Bonjour, mon ami - I have seen "Bump" before, but not quite sure what it does in the context of this forum.
Care to enlighten me?

 

[DeusXv]

Your thread is now at the top of the "Search New Posts" so you've been "Bumped" to the top, and when you're at the top everyone views the thread right ?....right ?

 

[apo]

Hey..

I am not experienced in this.. but i did create a solution for some guy/girl recently that involved shell to invoke commands..

For example.. this code pings google.. then creates a folder on my desktop called "tester"..

So.. basically.. it is an example of string together multiple commands that get invoked directly from excel..

Private Sub CommandButton1_Click()
Shell "cmd.exe /c ping google.com" & " && cd\Users\apo\Desktop" & " &&mkdir tester"
End Sub

Does that help at all?

 

[John_w]

getting into grief with the quadruple quotes

Define this function which adds the quotes to a text string:

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

and call it where needed to surround a string with quotes.

 

[NewOrderFac33]

Lo and behold - it works!

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 = Chr(34) & "C:\Program Files (x86)\7-Zip\7z.exe" & Chr(34) & " a " & Chr(34) & "c:\PetesStuff\MyZip" & Chr(34) & " " & Chr(34) & "C:\PetesStuff\01 Backup\01 Petes Useful Stuff\*.*" & Chr(34)
    ts.WriteLine (LineString)
    ts.Close
End Sub

Thanks very much, John - and Apo for your suggestion, which I can think of a use for elsewhere and Deus for bumping me (oo-er!)

Now to make the calculations for the jump to light speed, Chewie!

 

[DeusXv]

Thanks very much, John - and Apo for your suggestion, which I can think of a use for elsewhere and Deus for bumping me (oo-er!)

Now to make the calculations for the jump to light speed, Chewie!

Never underestimate the power of the "BUMP" ... and I suppose the insanely talented people on this forum... but more importantly the power of the "BUMP"