Writing an Excel macro that passes commands to Windows Shell

Question

I have an Excel file containing metadata information for 20k+ images. I'm trying to write a macro that executes commands with exiftool.exe (a tool used to batch edit metadata) on Windows Shell with variables relative to each row.

For instance, I want to iterate through exiftool commands that take information from column B ("Author") such as:

C:\exiftool\exiftool.exe -Author="CELL B1 CONTENT"
C:\exiftool\exiftool.exe -Author="CELL B2 CONTENT"

...repeats ad infinitum.

This is what I've tried so far:

    Sub EnterMetadata()
    For Each Cell In Range("C1:C20000")
    Shell("c:\Exiftool\exiftool.exe -o I:/Photos/ & ActiveCell.Offset(0, -2).Value) & " -Author=" & ActiveCell.Offset(0, -1).Value) 
    Next
    End Sub

Notice that column A contains the path for the original files. And column B contains author names. I'm trying to retrieve information from columns A and B to use in the macro.

Tim Williams · Accepted Answer · 2014-07-16T17:57:50.487

Untested:

Sub EnterMetadata()
    Const CMD As String = "c:\Exiftool\exiftool.exe -o ""I:/Photos/{fn}"" -Author=""{auth}"""
    Dim Cell as Range, s as String

    For Each Cell In Range("C1:C20000")
        s = Replace(CMD, "{fn}", Cell.Offset(0, -2).Value)
        s = Replace(s, "{auth}", Cell.Offset(0, -1).Value)

        Debug.Print s


        Shell s
    Next
End Sub

If any of your command line parameters might contain spaces then you should quote them (quotes are escaped in VBA strings by doubling them up)

score 1 · Answer 2 · answered Jul 16 '14 at 00:51

What about using ShellExecute?

This is what you need to declare in your macro so you can use it:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
    ByVal hWnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

You can pass parameters to it as String (lpParameters), get a return value tohelp understand what happened if something went wrong and is generally more versatile than using Shell.

You can do something similar to this (this is not tested, as I don't have exiftool.):

ShellExecute 0, vbNullString, "C:\exiftool\exiftool.exe", "-Author=""CELL B1 CONTENT""", vbNullString, 10 ' 10=SW_SHOWDEFAULT

I'll let you populate the author according to your requirements.

For full information about ShellExecute, click here to have a look on MSDN.

Writing an Excel macro that passes commands to Windows Shell

2 Answers2