VBA: Multiple commands from one cmd.exe

Question

I am trying to run VBA script that will execute particular shell program with arguments retrieved from Excel Spreadsheet.

I'd like to exeute the command n times for n rows in spredsheet, yet have it all in one cmd.exe window. Is that achievable?

I am doing such steps:

1.Creating shell object:

Set wsh = VBA.CreateObject("WScript.Shell")

2.Iterating trough all rows in Spreadsheet and calling for each:

wsh.Run("Command with row specific args", 1, true)

As the result I have n console windows opened for n processed rows. Is there any way to avoid it?

Help appreciated.

Mathieu Guindon · Accepted Answer · 2013-10-03 22:55:13Z

3

You don't give much details, but a solution could be to build a batch (.bat) file (no different from writing to any other text file) with all the parameters you need, save it somewhere and then run it in a single call to cmd.exe. And then you can just delete that file.

Start with opening the file for output:

Dim fn As Long
fn = FreeFile
Open "filename.bat" For Output As #fn

Then iterate your range, and instead of launching a cmd.exe for each, write to your open file:

Print #fn "command with row specific args"

Then close your file and run it:

Close #fn
Set wsh = VBA.CreateObject("WScript.Shell")
wsh.Run("filename.bat", 1, true)

Then you can discard the file:

Kill "filename.bat"

edited Oct 3, 2013 at 22:55

answered Oct 3, 2013 at 22:16

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

VBA: Multiple commands from one cmd.exe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related