Shell ExifTool from Excel VBA

[Betj]

Dear Sirs
Using ExifTool command line, I do something wrong, but I do not see where.

Using a fresh new image without any tag from the cmd window:

C:\> ExifTool -comment="gamma 0.4" "C:\Tmp\377.jpg"

I get the answer:    1 image files updated
And I can see with ExifTool Edit Properties menu that the right comment is there, and the original file name is changed to 377.jpg_original.


Same command from Excel VBA does not work: No new file nor change in name, comment tag is not created:

Code Select Expand


strCommand="ExifTool -Comment=""gamma 0.4"" ""C:\Tmp\377.jpg"""
retValue= Shell(strCommand, vbHide)

The result does not depend on the windowstyle (hide or normal)

In the past with Windows XP and 32 bits VBA versions the same code was working fine. Something changed, but apparently not from ExifTool side.
I see also in the forum that it was suggested to use some ShellWait ms-access code, but my VBA knowledge is a little light to understand!
So, Is there a simple way to control ExifTool from Excel ? I hope someone can help me.

[Phil Harvey]

There can be two things wrong:

1. ExifTool isn't in the path for the VBA shell.  In this case, specify the full path in the command instead of just "exiftool".

2. The quotes aren't escaped properly in your VBA string.  I can't help with this because I don't know the VBA quoting rules.

- Phil

[l.willms]

There can be two things wrong:

2. The quotes aren't escaped properly in your VBA string.  I can't help with this because I don't know the VBA quoting rules.

The latter

Code Select Expand

strCommand="ExifTool -Comment=""gamma 0.4"" ""C:\Tmp\377.jpg"""
retValue= Shell(strCommand, vbHide)


It might be that the two quotes following the "-Comment=" are needed by the Exiftool, but the ones between "0.4" and "C:\Tmp" seem superfluous to me.

I'd do it this way:

Code Select Expand

strCommand="ExifTool -Comment=" & CHR(34) & CHR(34) & "gamma 0.4 C:\Tmp\377.jpg"

CHR(34) returns the quote character, "&" is the concatenation operator.

[Phil Harvey]

ExifTool doesn't need any quotes.  The quotes are entirely for the shell command and VBA string.

From what I.Willms says, I think this my work:

Code Select Expand

strCommand="ExifTool -Comment=" & CHR(34) & "gamma 0.4" & CHR(34) & " " & CHR(34) & "C:\Tmp\377.jpg" & CHR(34)

- Phil

[Betj]

Dear Both
I thank you very much for the time you took to look at the case.
I am sorry none of the suggestions solve the problem.
It is true that the ExifTool.exe was not in the VBA directory. It was in the Windows directory in order to be accessed from anywhere. I moved it to the VBA directory, but the result is the same: when the shell is sent, I see a very fast flash corresponding to the console window, which closes immediately, but the image is not updated.

VBA interprets 2 double quotes inside a string as a single double quote. Same as using & CHR(34)
So the string produced is
ExifTool -Comment="gamma 0.4" "C:\Tmp\377.jpg"

I understand that the quotes are necessary only if there is a space. I have sometimes images names like 
C:\Tmp\1 377.jpg

Anyway, In order to eliminate quotes questions I tried

Code Select Expand

strCommand="ExifTool  -Comment=gamma C:\Tmp\377.jpg"

Again no success, but a copy and paste in a console window works perfectly !

[Phil Harvey]

I didn't say to move exiftool to the VBA directory.  I said to specify the path in the command:

c:\path\to\exiftool\exiftool -Comment="gamma 0.4" "C:\Tmp\377.jpg"

If ExifTool gets launched at all, adding -k to the command will keep the window from popping away so quickly, and may give you a chance to read any messages it may be trying to give.

- Phil

[Betj]

Thank you Phil
I did many tries including the -k option and I found:

This code does not work:

Code Select Expand

strCommand = "C:\Tmp\ExifTool.exe  -k -Comment=abcde C:\Tmp\378.jpg"
retVal = Shell(strCommand)
I get the error message: Incorrect procedure call.

Instead I tried to pass ExifTool as an argument to Cmd, and this works !

Code Select Expand

strCommand = "Cmd /c C:\Tmp\ExifTool.exe -Comment= abcde C:\Tmp\378.jpg"
ret = Shell(strCommand)
strCommand = "Cmd /c C:\Tmp\ExifTool.exe –k -Comment C:\Tmp\378.jpg"
ret = Shell(strCommand)
As expected, ExifTool displays the tag value and waits for a Return key, then the Cmd window closes.

But in spite of many tries I did not manage to write the tags in a file
This code does not work

Code Select Expand

strCommand = "Cmd /c C:\Tmp\ExifTool.exe -Comment C:\Tmp\378.jpg  >  C:\Tmp\ExifToolTmp.txt"
ret = Shell(strCommand)
This code creates the ExifToolTmp.txt file, but the txt file remains always empty.
I suspect Admin rights problems, even if I checked them.

Do you know if there is another way to get the tag value ?

[ryerman]

To keep the command window open and see any error messages, try this:
strCommand = "Cmd /k C:\Tmp\ExifTool.exe -Comment C:\Tmp\378.jpg  >  C:\Tmp\ExifToolTmp.txt"

[Betj]

Dear ryerman
Thanks. I used both the Cmd /k option, and the Exiftool –k option

Code Select Expand

strCommand = "Cmd /k C:\Tmp\ExifTool.exe -k -Comment C:\Tmp\378.jpg > C:\Tmp\ExifToolTmp.txt"

The Cmd window opens and immediately after the ExifTool window opens.
The ExifTool window shows the correct tag, but does not redirect it to the text file:

Comment:                          xyxyx
-- press RETURN --
see image 1

The text file is created but is completely empty.
Typing return closes the ExifTool window and the Cmd window shows the prompt without any error message.

The title of the Cmd window shows that the end of the full string starting after ">" is truncated, so I tried to pass all the ExifTool command line as a single argument of Cmd.

Code Select Expand

strCommand = "Cmd /k ""C:\Tmp\ExifTool.exe -k -Comment C:\Tmp\378.jpg > C:\Tmp\ExifToolTmp.txt"""

This corresponds to the string
Cmd /k "C:\Tmp\ExifTool.exe -k -Comment C:\Tmp\378.jpg  >  C:\Tmp\ExifToolTmp.txt"

I get exactly the same result. see image2


And if I quote also the files in the ExifTool arguments list:

Code Select Expand

strCommand = "Cmd /k """"C:\Tmp\ExifTool.exe"" -k -Comment ""C:\Tmp\378.jpg"">""C:\Tmp\ExifToolTmp.txt"""""
corresponding to the string:
Cmd /k ""C:\Tmp\ExifTool.exe" -k -Comment "C:\Tmp\378.jpg">"C:\Tmp\ExifToolTmp.txt""
see image3

Always the same result.
The text file is created empty and locked by the cmd window, so I cannot delete it before closing the cmd window.
So I think it is the redirection with > which is not understood. The screen still receives the data.

This is the point where I am !

[Phil Harvey]

OK, so getting the redirection to work is problematic.

Try this instead:

strCommand = "Cmd /c C:\Tmp\ExifTool.exe -Comment C:\Tmp\378.jpg -W+! C:\Tmp\ExifToolTmp.txt"

- Phil

[StarGeek]

Maybe this Stackoverflow answer can help?

[Betj]

Thank you very much to all.

Phil, your suggestion works perfect.
I get the right comment in the text file.

[l.willms]

Maybe this Stackoverflow answer can help?

This is one step forward by using the WScriptShell object, but instead of using the Run method, it would be better to use the WshScriptExec object, because with the Exec method one has also access to the SYSIN, SYSOUT, and SYSERR streams.

An example from the help file for VBScript:

Code Select Expand

Dim WshShell, oExec, input
Set WshShell = CreateObject("WScript.Shell")
Set oExec    = WshShell.Exec("test.bat")
input = ""

Do While True

     If Not oExec.StdOut.AtEndOfStream Then
          input = input & oExec.StdOut.Read(1)
          If InStr(input, "Press any key") <> 0 Then Exit Do
     End If
     WScript.Sleep 100
Loop

oExec.StdIn.Write VbCrLf

Do While oExec.Status <> 1
     WScript.Sleep 100
Loop


Remarks
The Status property is used when a program is run asynchronously.

Return Values
The Status property returns a value from an enumerated type.

WshRunning ( = 0)
The job is still running.
WshFinished ( = 1)
The job has completed.

Sorry for being late with this, I do not look at this forum every day

[l.willms]

Maybe this Stackoverflow answer can help?

This is one step forward by using the WScriptShell object, but instead of using the Run method, it would be better to use the WshScriptExec object, because with the Exec method one has also access to the SYSIN, SYSOUT, and SYSERR streams.

The code example from the VBSCript help file for access to the STDERR stream:

Code Select Expand

Dim WshShell, oExec
Set WshShell = CreateObject("WScript.Shell")
Set oExec    = WshShell.Exec("%comspec% /c dire")

Function ReadAllFromAny(oExec)

     If Not oExec.StdOut.AtEndOfStream Then
          ReadAllFromAny = oExec.StdOut.ReadAll
          Exit Function
     End If

     If Not oExec.StdErr.AtEndOfStream Then
          ReadAllFromAny = "STDERR: " + oExec.StdErr.ReadAll
          Exit Function
     End If
     
     ReadAllFromAny = -1
End Function

Dim allInput, tryCount

allInput = ""
tryCount = 0

Do While True

     Dim input
     input = ReadAllFromAny(oExec)

     If -1 = input Then
          If tryCount > 10 And oExec.Status = 1 Then
               Exit Do
          End If
          tryCount = tryCount + 1
          WScript.Sleep 100
     Else
          allInput = allInput & input
          tryCount = 0
     End If
Loop

WScript.Echo allInput


[Betj]

Thanks. I tried to use WScript.Shell and also PowerShell.

I fall very quickly into problems or subtleties that I am not able solve. For example I can see that "my"  WScript.Shell  object does not support the Exec method, but the Run method instead, showing that it is not the same as yours!
My programming level is not good enough to adapt the code!
So I think I will have to stay with the use of CMD.

I have a number of images in a directory and its subdirectories to write with the same tag. But not all images are to be written.
I have a list. In order not to load the CMD for every image, Is there a way to specify a list in a text file with the relevant names (and path) ? Something like @ARGLIST option, but for source images, not arguments.