file exists or not

[naveencn023]

Hi,

I am looking at building a macro for the below condition.. please do the needful.

I have the folder keywords in column A, folder paths in column B and i want to know if the folder with the keyword is present in that folder path and if present it should throws message as PASS or FAIL in column C and also i wanted to know the last modified date of that folder in column D.

Thanks in advance for your help...

[pike]

this will get you started

Sub My_Newest_Files()
    Dim objFSO As Object
    Dim objFile As Object
    Dim mfile  As String
    Dim mpath  As String
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    mpath = "C:\Documents and Settings\"
    mfile = Dir(mpath & "*.*")
    Do While mfile <> ""
        mfile = Dir
        If objFSO.FileExists(mpath & mfile) Then
            Set objFile = objFSO.GetFile(mpath & mfile)
            If objFile.DateLastModified > Cells(1, 1).Value Then
                Cells(1, 1).Value = objFile.DateLastModified
                Cells(1, 2).Value = mfile
            End If
        End If
    Loop
End Sub

[naveencn023]

Thanks a lot for your response.. but i am new to macro and i am not getting what you explained.

So kindly provide us the code for the example mentioned below, so that it will be helpful for us..

Path File keyword file present/ file nor present last modified date
G:\Park Infra IND
F:\SEEMA Data Dec

Thanks a lot in advance..

[pike]

I think i understand

Option Explicit
Sub My_Newest_Files()
    Dim objFSO As Object
    Dim objFile As Object
    Dim mfile  As String
    Dim rngFolder  As Range
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each rngFolder In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
        If objFSO.FolderExists(rngFolder.Value) Then
            mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
            Do While mfile <> ""
                mfile = Dir
                If objFSO.FileExists(rngFolder.Value & mfile) Then
                    Set objFile = objFSO.GetFile(rngFolder & mfile)
                    If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
                        rngFolder.Offset(0, 2).Value = objFile.DateLastModified
                        rngFolder.Offset(0, 3).Value = mfile
                    End If
                End If
            Loop
        End If
   Next
    Set objFSO = Nothing
    Set objFile = Nothing
    
End Sub

[naveencn023]

Hi,

I am unable to run the below mentioned macro.. I copied and pasted in macro and clicked run but no result...

[pike]

hi naveencn023,

do the folders and files exist?
can you attach a small sample workbook of you layout?

[pike]

try this it will indicate the results if folder or files dont exist

Option Explicit
Sub My_Newest_Files()
    Dim objFSO As Object
    Dim objFile As Object
    Dim mfile  As String
    Dim rngFolder  As Range
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each rngFolder In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
        If objFSO.FolderExists(rngFolder.Value) Then
            mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
            Do While mfile <> ""
               If mfile = "" Then
               rngFolder.Offset(0, 3).Value = "No File Found"
                End If
                mfile = Dir
                If objFSO.FileExists(rngFolder.Value & mfile) Then
                    Set objFile = objFSO.GetFile(rngFolder & mfile)
                    If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
                        rngFolder.Offset(0, 2).Value = objFile.DateLastModified
                        rngFolder.Offset(0, 3).Value = mfile
                    End If
                End If
            Loop
         Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
        End If
       
   Next
    Set objFSO = Nothing
    Set objFile = Nothing
    
End Sub

[naveencn023]

Hi,

Please find attached workbook for your kind reference.. please provide me the macro..

[pike]

try

[naveencn023]

Hi,

I run the macro, but if file present it is showing as blank and its not showing the last modified date of the file..

Please have a look at the attached file and advise the correct macro.

Thanks in advance..

[pike]

try..

[pike]

no need to down load a workbook just copy the code and paste over the existing code in the workbook module

Option Explicit
Sub My_Newest_Files()
    Dim objFSO As Object
    Dim objFile As Object
    Dim mfile  As String
    Dim rngFolder As Range
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each rngFolder In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
        If objFSO.FolderExists(rngFolder.Value) Then
            mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
            If mfile = "" Then
                rngFolder.Offset(0, 2).Value = "No File Found"
            End If
            Do While mfile <> ""
                mfile = Dir
                If objFSO.FileExists(rngFolder.Value & mfile) Then
                    Set objFile = objFSO.GetFile(rngFolder & mfile)
                    If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
                        rngFolder.Offset(0, 3).Value = objFile.DateLastModified
                        rngFolder.Offset(0, 2).Value = mfile
                    End If
                End If
            Loop
        Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
        End If
    Next
    Set objFSO = Nothing
   Set objFile = Nothing
End Sub

[naveencn023]

I tried again but no use.. file present is not showing and modified date also not reflecting..

[pike]

after you run the routine/sub what results do you get?
I test an my sample data and it work for me population if the file path did not exist or if the file name could not be found

check the full paths and file names

[pike]

Is folder key name a file? or is it a sub folder?
are you just look for folder details?
I dont follow

[pike]

think this is it
any file in the column a path and sub folder in column B look for any file latest saved data

Option Explicit
Sub My_Newest_Files()
    Dim objFSO As Object
    Dim objFile As Object
    Dim mfile  As String
    Dim rngFolder As Range
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each rngFolder In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
        If Right(rngFolder.Offset(0, 1).Value, 1) <> "\" Then rngFolder.Offset(0, 1).Value = rngFolder.Value & "\"
        If objFSO.FolderExists(rngFolder.Value & rngFolder.Offset(0, 1).Value) Then
            mfile = Dir(rngFolder & rngFolder.Offset(, 1).Value & "*.*")
            If mfile = "" Then
                rngFolder.Offset(0, 2).Value = "No File Found"
            End If
            Do While mfile <> ""
                mfile = Dir
                If objFSO.FileExists(rngFolder & rngFolder.Offset(, 1).Value & mfile) Then
                    Set objFile = objFSO.GetFile(rngFolder & rngFolder.Offset(, 1).Value & mfile)
                    If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
                        rngFolder.Offset(0, 3).Value = objFile.DateLastModified
                        rngFolder.Offset(0, 2).Value = mfile
                    End If
                End If
            Loop
        Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
        End If
    Next
    Set objFSO = Nothing
   Set objFile = Nothing
End Sub