check for file, if available run macro?

You would need to use the On Time method to check periodically for the file.
This UDF checks for a file

Option Explicit

Function wbExists(FullFileName As String) As Boolean
' returns TRUE if the file exists
    wbExists = Len(Dir(FullFileName)) > 0
End Function

Check for the file with

Sub mainCode()
    Dim sFile  As String
    'you need the path where the file is
    sFile = "C:\Documents and Settings\your name\ReportsFolder\report" & Format(Date - 1, "mmddyy") & ".xls"
    If Not wbExists("sfile") Then
'keep checking
        checkLater
        Exit Sub
    Else
        'your code
    End If
End Sub
Sub checkLater()
    'check each 5 minutes
    Application.OnTime Now + TimeValue("00:05:00"), "mainCode"
End Sub

Start the process when the workbook is opened

Option Explicit

Private Sub Workbook_Open()
'start the Ontime & run the main macro after 5 minute
Application.OnTime Now + TimeValue("00:05:00"), "mainCode"
End Sub