File exists or not with created time

[NAVEEN C N]

Team,

I have written a macro which will find out the if the folder exists in the specified directory or not based on the keyword of the folder.

folder keyword is 20141105
directory is
\\nasmsb1\ap\ASBPRO\Alexandria\EXPORT\HISTORY\DMV_VA_Add_Hold_Request

I am able to check if the folder exists or not with bellow mentioned macro but i want modify the macro which is able to retrieve the date and time of creation of folder. if the folder is not exists, it should return blank value.

Sub IsItThere()
    Dim KeyWd As String
    Dim Pathh As String, fName As String
    Dim N As Long, J As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For J = 1 To N
        KeyWd = Cells(J, 1).Value
        Pathh = Cells(J, 2).Value
        If Right(Pathh, 1) = "\" Then
            Pathh = Mid(Pathh, 1, Len(Pathh) - 1)
        End If
        Set objShell = CreateObject("Shell.Application")
        Set objFolder = objShell.Namespace((Pathh))

        For Each strFileName In objFolder.Items
            fName = objFolder.GetDetailsOf(strFileName, 0)
            If InStr(1, fName, KeyWd) > 0 Then
                Cells(J, 3).Value = "PASS"
                GoTo NextRecord
            End If
        Next
        Cells(J, 3).Value = "FAIL"
NextRecord:
        Set objFolder = Nothing
        Set objShell = Nothing
    Next J
End Sub

Please do the needful..

 

[lohithsriram]

objFolder.self.modifydate should fetch you the modified datetime of the folder. Check if objfolder is nothing after you set the folder. If its nothing then exit the sub

 

[NAVEEN C N]

Lohith..

Realy sorry.. i am not that good at macro.. could you please edit the macro which i have sent to retrieve the modified date and time of the folder...

Please do the needful..

 

[NARAYANK991]

Hi Naveen ,

See if this is OK.

Sub IsItThere()
    Const SUCCESS = "PASS"
    Const FAILURE = "FAIL"
    Dim RetVal As String
    Dim KeyWd As String
    Dim Pathh As String, fName As String
    Dim N As Long, J As Long
   
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For J = 1 To N
        KeyWd = Cells(J, 1).Value
        Pathh = Cells(J, 2).Value
        If Right(Pathh, 1) = "\" Then
            Pathh = Mid(Pathh, 1, Len(Pathh) - 1)
        End If
        Set objShell = CreateObject("Shell.Application")
        Set objFolder = objShell.Namespace((Pathh))

        For Each strFileName In objFolder.Items
            fName = objFolder.GetDetailsOf(strFileName, 0)
            If InStr(1, fName, KeyWd) > 0 Then
                RetVal = "PASS"
                GoTo NextRecord
            End If
        Next
        RetVal = "FAIL"
NextRecord:
        Cells(J, 3).Value = RetVal
        If RetVal = SUCCESS Then Cells(J, 4).Value = objFolder.Self.ModifyDate
        Set objFolder = Nothing
        Set objShell = Nothing
    Next J
End Sub

Narayan

 

[NAVEEN C N]

Thanks a lot Narayan..

Its working great...