locked
How properly to run a command script using VBA RRS feed

  • Question

  • Hello,

    I have been learning so much here lately, thanks to the folks who have been responding to my questions!

    I am currently trying to run a command line script using vba. I was going to try and use vba to simply run a BAT file but I think it would be cleaner to simply run the command.

    Anyway, I have tried this...

    <BEGIN CODE>

    Sub Test()
    Call Shell("cmd.exe /S/K" & "Copy "Z:\Reports\2017-03-22\Converted\*.csv" "Z:\Reports\2017-03-22\Mail Shipment Log 2017-03-22.xls, vbNormalFocus)

    End Sub

    <END CODE>

    And get an error stating it was expecting a separator and it highlights the "Z"

    What am I missing?

    Thanks

    Thursday, March 23, 2017 3:41 PM

Answers

  • It's not clear why you want the command processor to remain in memory by using the /K switch.  If you just want to run a command using cmd.exe then you can use the /C switch so that it doesn't remain running after the command has been executed.

    Try this

    Dim ret
    ret = Shell("cmd.exe /C copy ""SourceFile"" ""TargetFile""", vbNormalFocus)
    

    Thursday, March 23, 2017 7:12 PM

All replies

  • It's not clear why you want the command processor to remain in memory by using the /K switch.  If you just want to run a command using cmd.exe then you can use the /C switch so that it doesn't remain running after the command has been executed.

    Try this

    Dim ret
    ret = Shell("cmd.exe /C copy ""SourceFile"" ""TargetFile""", vbNormalFocus)
    

    Thursday, March 23, 2017 7:12 PM
  • Perfect!

    I was using the K switch at a suggestion from a friend but you are correct, I did not need it.

    Will the normal VBA code work on that line such as if I needed to insert specific dates I could use 

    Z:\Reports\Opex Metadata\2017-03-22\Mail Shipment Log " & CurrentDate & ".xlsx

    After "Dimming" the variables of course...

    Thursday, March 23, 2017 7:44 PM
  • You can work it out, but remember that the / character used in dates returned by the VBA Date function  is invalid in file names.
    Thursday, March 23, 2017 9:04 PM