Calling Command Prompt and executing a command through Shell Exec in VBA

Question

I'm trying to execute a command on command prompt using Shell Script in VBA Excel. When I execute this command on command prompt it works by printing a number as output. But when I call the same command in the script it doesn't show this number in the message box.

Sub Button6_Click()
    Dim oShell As Object
    Dim objExecObject As Object
    Set oShell = CreateObject("WScript.Shell")
    Set objExecObject = oShell.Exec("cmd /c convert test2.jpg -fill black +opaque white -fill white -opaque white -print %[fx:w*h*mean]")
    MsgBox (objExecObject.StdOut.ReadAll)
End Sub

I think the problem is with the arguments in the Exec function as there are multiple words. So I tried the following by concatenating a string as shown. But this doesn't work either.

    Dim command As String
    command = "convert test2.jpg -fill black +opaque white -fill white -opaque white -print %[fx:w*h*mean] null:"
    Set objExecObject = oShell.Exec("cmd /c " & command)

There seems to be no runtime error and the message box always returns empty without printing the number. To clarify: the convert command is a function provided by ImageMagick.

Please tell me how I can run the convert command in command prompt and print the output of command prompt in the message box.

This worked. Set objExecObject = oShell.Exec("cmd /c convert test2.jpg -fill black +opaque white -fill white -opaque white -print %[fx:whmean] null:") The null at the end of the command was the problem. Also make sure that the path of the file is absolute which in my case was not the problem. — user2048724
– user2048724, Commented Sep 29, 2014 at 15:04

Ekkehard.Horner · Accepted Answer · 2014-09-29 15:10:12Z

1

When a command 'works' from a console but 'fails' when .Exec'ed, the usual suspects are

pathes -> use quoted full/absolute file specs for executables and data files
permissions -> check user(s)/rights
console runs a shell vs .Exec starts a process

In your case the meaning of 'works/fails' isn't entirely clear. The symptom is "MsgBox does not show a number". Perhaps convert writes to StdErr. So try:

MsgBox objExecObject.StdErr.ReadAll()

or - better -

If Not objExecObject.StdErr.AtEndOfStream Then MsgBox objExecObject.StdErr.ReadAll()

If that does not help, use .Run() with "%comspec% /k".

Update wrt comment:

Start convert from a console and re-direct its output to a file for further checking.

edited Sep 29, 2014 at 15:10

answered Sep 29, 2014 at 14:21

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

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

1 Comment

user2048724 Over a year ago

1,2,3 are not the problem. Both your codes haven't worked. When I use Run then the command works on cmd but I need the output(number in this case) so that I can write this on excel cell. With run I won't be able to do this.

Collectives™ on Stack Overflow

Calling Command Prompt and executing a command through Shell Exec in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related