Automation Process of Schedule Report Output and Report Check Score Card !

[OZMAN86]

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.

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

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:

if (fso.fileexists(filename)) then

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

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

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

in my code

 

[SkipVought]

Hi,

So is what you want is the file in your folder that was last modified today?

 

[PHV]

What about this ?

Private Sub TestFileExistence()
Const cPathName = "\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker - Exec.mhtml"
If FileFolderExists(cPathName) And Weekday(Date) = 2 Then
  If DateDiff("d", FileDateTime(cPathName), Now) = 0 Then
    Range("P4").Value = "X"
  End If
End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886