List All Files In A Directory Which Are Modified After A Certain Date

[raghuram.star]

I wish to create a macro that looks in a specific directory "D:Records"
It finds the files which are modified after a date "January 01, 2012" in this directory (pdf files)
Then it imports these files names into a worksheet

Is this possible please???

Please Help...

 

[raghuram.star]

I tried this, and it keeps getting an error at the point below:

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject

Do you have:

Option Explicit

at the top of the code window? If so remove it and the code should work.

To solve that problem

1. Go to VBA Editor (Press Alt+F11 from Excel Window)
2. Go to "Tools" Menu
3. Select "References"
4. Scroll Down and Check/Select "Microsoft Scripting Runtime"
5. Click on "OK"
6. Save the file and execute the Macro

Hope that helps

 

[hippiehacker]

Do you have:

Option Explicit

at the top of the code window? If so remove it and the code should work.

Option Explicit statement at file level to force explicit declaration of all variables in the script:
Option Explicit</pre>

Option Explicit statement must appear in a script before any procedures.
When you use the Option Explicit statement, you must explicitly declare all variables using the Dim, Private, Public, or ReDim statements. If you attempt to use an undeclared variable name, an error occurs:

Option Explicit

Sub Test()[COLOR=#000080]
Dim[/COLOR] myVar 
myVar = 1 
myNewVar = 0	' [COLOR=#000080]ERROR[/COLOR]: Variable [COLOR=#000080]not[/COLOR] defined.
End Sub

the error message occurs as the reference to the Microsoft Scripting is not set but has been answered already

 

[jujubeans69]

To solve that problem

1. Go to VBA Editor (Press Alt+F11 from Excel Window)
2. Go to "Tools" Menu
3. Select "References"
4. Scroll Down and Check/Select "Microsoft Scripting Runtime"
5. Click on "OK"
6. Save the file and execute the Macro

Hope that helps

This helped that error. Now it stops at the next line.

 

[jujubeans69]

Option Explicit statement at file level to force explicit declaration of all variables in the script:
Option Explicit

Option Explicit statement must appear in a script before any procedures.
When you use the Option Explicit statement, you must explicitly declare all variables using the Dim, Private, Public, or ReDim statements. If you attempt to use an undeclared variable name, an error occurs:

Option Explicit

Sub Test()[COLOR=#000080]
Dim[/COLOR] myVar 
myVar = 1 
myNewVar = 0    ' [COLOR=#000080]ERROR[/COLOR]: Variable [COLOR=#000080]not[/COLOR] defined.
End Sub

the error message occurs as the reference to the Microsoft Scripting is not set but has been answered already

I tried it this way, too. My code looked like this afterwards, and stops at the red text:

Option Explicit
Sub Test()
Dim myVar
myVar = 1
myNewVar = 0    ' ERROR: Variable not defined.
End Sub

Dim iRow As Long 'start row to put output
Dim pathf As String 'pathfolder
Dim subf As Boolean 'incl subfolders true/false

Sub ListFiles()

    iRow = 2
    pathf = "\\sites\history\"
    subf = False
    Call ListMyFiles(pathf, subf)
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
     If myFile.DateLastModified > Range("A1") Then '40909 is 01-01-2012 as number
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
        End If
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

 

[hippiehacker]

try without Option Explicit

Dim iRow As Long 'start row to put output
Dim pathf As String 'pathfolder
Dim subf As Boolean 'incl subfolders true/false

Sub ListFiles()

    iRow = 2
    pathf = "\\sites\history\"
    subf = False
    Call ListMyFiles(pathf, subf)
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
     If myFile.DateLastModified > Range("A1") Then '40909 is 01-01-2012 as number
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
        End If
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

 

[jujubeans69]

Maybe I need to check some more items in References? It's stopping with highlighted line below:

Set mySource = MyObject.GetFolder(mySourcePath)

 

[hippiehacker]

and waht error message?

 

[hippiehacker]

no need for more references

 

[jujubeans69]

and waht error message?

No error message. It just highlights that line.

 

[hippiehacker]

there should be at least an error message that the path could not be found or whatever just tried to code and works fine