VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "FileBatcher"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
' Loops over the spreadsheet, applying a macro to each file,
' passing the value of columns 1 and 2 as arguments.
' Records the job status into column 3, and the date of the job in column 4
' When run, it will skip over any jobs that are newer than the file.

' The status column can be edited, and putting the word
' "Stop" in there will cause the loop to stop.  Putting the
' word "Skip" will cause the loop to skip that row.

' If the time is later than quittingTime, the loop will stop.
' This feature needs to be modified to check if it's in the
' hours past quitting time.

Dim macroName As String

Public Function applyMacro(mName As String)
    Dim fs, f
    Dim quittingTime
    
    Set fs = CreateObject("Scripting.FileSystemObject")

    quittingTime = #11:00:00 PM#

    macroName = mName
    row = 1
    Do While (row <> -1)
        Set fName = Worksheets("Sheet1").Cells(row, 1)
        Set newName = Worksheets("Sheet1").Cells(row, 2)
        Set Status = Worksheets("Sheet1").Cells(row, 3)
        Set lastVisit = Worksheets("Sheet1").Cells(row, 4)
        
        If (fName = "") Then Exit Do
        Debug.Print fName
        
        If (Status = "Stop") Then Exit Do
        
        ' If the time passes the quitting time, stop.
        ' This part needs work.
        timeDelta = quittingTime - Time
        ' If (timeDelta < 0) Then Exit Do
        
        If (lastVisit = "") Then
            lastVisit = #2/12/1985#
        End If
        
        Set f = fs.GetFile(fName)
        
        If (Status = "Skip") Then
            Worksheets("Sheet1").Cells(row, 3) = "Skip"
        ElseIf (lastVist >= f.DateLastModified) Then
            Worksheets("Sheet1").Cells(row, 3) = "Ignored"
        Else
            Worksheets("Sheet1").Cells(row, 3) = "Processing..."
            ' call the macro here
            
            ' Debug.Print ("calling macro on " & fName)
            result = Application.Run(mName, fName, newName)
            
            If (result = "Processed") Then
                ' Worksheets("Sheet1").Cells(row, 3) = "Processed"
                Worksheets("Sheet1").Cells(row, 3) = "Skip"
                Worksheets("Sheet1").Cells(row, 4) = Now
            ElseIf (result = "Interrupted") Then
                Stop
            Else
                Worksheets("Sheet1").Cells(row, 3) = "Skip"
                Worksheets("Sheet1").Cells(row, 4) = ""
                Worksheets("Sheet1").Cells(row, 5) = "Error"
            End If
                
        End If
        
        row = row + 1
    Loop
    executeMacro = 1
End Function