Microsoft Office Forums - View Single Post - Automation Process of Schedule Report Output and Report Check Score Card !

ozman86 · 11-19-2014, 07:57 AM

i have some files that are coming daily to a folder and are replaced my new ones daily . i am using the followingVBA for catching the file and putting the X mark in the excel. But what i really want is to get the today file , sometime the today file is not delivered and the yesterday file is still sitting , so just by catching the name the code put X in the excel sheet , which i do not want.

Code:

Private Sub TestFileExistence()


 If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker - Exec.mhtml") And _
       Weekday(Date) = 2 Then
 Range("P4").Value = "X"
End If
End Sub

the module code of the function is following

Code:

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Usman Tariq
'Macro Purpose: Check if a file or folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
    EarlyExit:
    On Error GoTo 0
End Function

Doing some research some one asked me to do following thing

your FileFolderExists() function probably utilizes the Filesytem object
and has something like:

Code:

if (fso.fileexists(filename)) then

you need to EITHER modify your FileFolderExists function
to also use:

Code:

set f = fso.getfile(filename)
if (datediff("d",f.datelastmodified,now()) = 0) then

or add a variation of this to your parent macro.
(which runs if the filefolderexists function returns "true")

Dont know where to put In my code , please help

Code:

set f = fso.getfile(filename)
if (datediff("d",f.datelastmodified,now()) = 0) then

in my code