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

ozman86 · #1 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel -> PowerPoint multiple presentations - process automation	wstach	Excel Programming	2	03-18-2014 06:20 AM
Excel to Access to Excel for report automation	Nicholaspoe	Excel Programming	10	10-08-2013 08:13 PM
Who Does What, When Report Times	cossie2k	Project	1	06-26-2013 05:49 AM
Example Report	Hinchy	Word	0	10-09-2012 07:00 AM
Report creation.	Igtech	Excel	1	04-02-2010 03:33 PM