[SOLVED] Writing a macro that passes commands to Windows Shell

Issue

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.

Solution

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)

Answered By - Tim Williams