• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Last modified date and time of file

NAVEEN C N

New Member
Hi,

I need a macro for the below mentioned condition.

I have the folder keywords column A, folder paths in column B and i need to retrieve PASS/FAIL in column C if the folder with specific keyword is present or not in the folder path. If present it should retrieve last modified date of the folder in column D.

I have attached sample file your reference..

Please do the needful. Thanks in advance for your help.
 

Attachments

  • macro.required.xlsx
    10.2 KB · Views: 6
Hi Naveen,

Welcome to the forum..

Can you please upload a PICTURE of folder(exp.
\\nasmsb1\ap\ISBPRO\Alhambra\EXPORT\History\DMV_LES_RO_Plate_Request
detail view .. (like below), so that we can guess the Keywords are perfect or part of the file / folder or is it in between any file..

22-04-2014 23-59-00.png
 
@NAVEEN C N
I agree with @Debraj that we need to get those details. Just seeking some clarification on what we're attempting...

I've used this code before to pull the files in the directories. You could probably modify it to write a loop that references each cell in the folder path column, looks at that directory and then loops through each folder in the directory performing a Search of the folder name.

Is that what you're looking for, conceptually speaking?

Code:
Sub TestListFilesInFolder()
'With Range("A1")
' .Formula = "Folder contents:"
' .Font.Bold = True
' .Font.Size = 12
'End With
Range("A3").Formula = "File Name:"
Range("B3").Formula = "File Size:"
Range("C3").Formula = "File Type:"
Range("D3").Formula = "Date Created:"
Range("E3").Formula = "Date Last Accessed:"
Range("F3").Formula = "Date Last Modified:"
Range("G3").Formula = "Attributes:"
Range("H3").Formula = "Short File Name:"
Range("A3:H3").Font.Bold = True
'ListFilesInFolder "c:\Windows\System32", True
ListFilesInFolder Worksheets("Sheet1").Range("A1").Value2, True
' list all files included subfolders
End Sub

Code:
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True

'Note: FSO Requires Tools > References > (Microsoft Scripting Runtime) c:\windows\system32\scrrun.dll
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A" & rows.count).End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Path & FileItem.Name
Cells(r, 2).Formula = FileItem.Size
Cells(r, 3).Formula = FileItem.Type
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastAccessed
Cells(r, 6).Formula = FileItem.DateLastModified
Cells(r, 7).Formula = FileItem.Attributes
Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub
 
Hi, NAVEEN C N!

Give a look at the uploaded file. This is the code:
Code:
Option Explicit

Sub WhereDidIParkTheFerrari()
    ' constants
    Const ksWS = "Sheet1"
    Const ksRng = "DataTable"
    Const ksPass = "Pass"
    Const ksFail = "Fail"
    ' declarations
    Dim rng As Range, obj As Object
    Dim I As Long, D As Date, A As String, B As String
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    If rng Is Nothing Then Exit Sub
    With rng
        Range(.Cells(1, 3), .Cells(.Rows.Count, .Columns.Count)).ClearContents
    End With
    ' process
    With rng
        For I = 1 To .Rows.Count
            A = Dir(.Cells(I, 2).Value & Application.PathSeparator & .Cells(I, 1).Value, _
                vbDirectory)
            If A <> "" And LCase(A) Like LCase(.Cells(I, 1).Value) Then
                D = FileDateTime(.Cells(I, 2).Value & Application.PathSeparator & A)
                .Cells(I, 3).Value = ksPass
                .Cells(I, 4).Value = D
            Else
                .Cells(I, 3).Value = ksFail
                .Cells(I, 4).Value = ""
            End If
        Next I
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

It works as long as the folder keywords at column A are the whole folder names you're trying to search for; otherwise you'd have to get involved with procedures using file system objects like those pasted before, which I haven't tested. But if you have the exact subfolder name, then this is a cleaner and shorter way to do the job.

Just advise if any issue.

Regards!
 

Attachments

  • Last modified date and time of file - macro.required (for NAVEEN C N at chandoo.org).xlsm
    19 KB · Views: 1
Hi all,

Thanks a lot for your valuable response.
Let me clarify few that might helpful in building macro which i needed..

On daily basis folder will be created in validation path as specified in attached spreadsheet in my previous thread. My job is to audit, if the folder is created in specific validation path or not. Folder name will be in the format yymmdd hhmmss ( 20140423 112245) and we are not sure at what time the folder will be created..

So we are looking for macro which will check whether the folder is created in the validation path or not and we need the time at which the folder was created ( if not the full folder name).

We need a macro, which will pull the latest folder created in validation path or which will the pull the folder full name with folder keyword ( keyword will be the date 20140422).

I hope i have given you all the information.

Await your response..
 
Hi, NAVEEN C N!

Try changing this:
Code:
            A = Dir(.Cells(I, 2).Value & Application.PathSeparator & .Cells(I, 1).Value, _
                vbDirectory)
            If A <> "" And LCase(A) Like LCase(.Cells(I, 1).Value) Then
by this:
Code:
            A = Dir(.Cells(I, 2).Value & Application.PathSeparator & .Cells(I, 1).Value & "*", _
                vbDirectory)
            If A <> "" And LCase(A) Like LCase(.Cells(I, 1).Value) & "*" Then

Regards!
 
Hi, NAVEEN C N!
I tested it with local files as I don't have network access here and it worked fine. Could you test it locally too?
Regards!
 
Back
Top