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

R

raghuram.star

Board Regular
Joined
Sep 5, 2012
Messages
102
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...
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
raghuram.star said:
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???

try this.

Please Help...
Click to expand...

Code: 
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 = 1
    pathf = "D:\Records"
    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 > 40909 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
 
Upvote 0
hippiehacker said:
Code: 
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 = 1
    pathf = "D:\Records"
    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 > 40909 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
Click to expand...

Wow.... !! it worked with in no time...

Thanks a Million....
 
Upvote 0
This Code is working fine on my local drive... but not on Share Drive.... I have been trying for this for long... but no luck yet.... Please some one help me in this...

Below is the code to get the list of files in a directory on local drive. Is there any way to do the same on share drive on server...
Dim iRow As Long 'start row to put output
Dim pathf As String 'pathfolder
Dim subf As Boolean 'incl subfolders true/false
Dim Fdate, Tdate As Date
Dim intC As Integer
Sub ListFiles()
Application.ScreenUpdating = False

wksList.Activate

wksList.Range("D8").FormulaR1C1 = "=COUNTA(R[-6]C[2]:R[49992]C[2])"
intC = wksList.Range("D8").Value
wksList.Range("F2:H2").ClearContents
wksList.Range("F3:H" & intC + 1).Clear
iRow = 2
pathf = wksList.Range("D2").Value
subf = True
Call ListMyFiles(pathf, subf)
intC = wksList.Range("D8").Value
wksList.Range("F2:H2").Copy
wksList.Range("F2:H" & intC + 1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
wksList.Range("D8").Value = wksList.Range("D8").Value
wksList.Range("F1").Select

ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Add Key:=Range( _
"H1:H8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("List").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub


Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
Fdate = wksList.Range("D3").Value
Tdate = wksList.Range("D4").Value + 1
For Each myFile In mySource.Files
If myFile.DateLastModified <= Tdate And myFile.DateLastModified >= Fdate Then
iCol = 6
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Path
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



<tbody>
</tbody>

Thanks a million in advance
 
Upvote 0
raghuram.star said:
This Code is working fine on my local drive... but not on Share Drive.... I have been trying for this for long... but no luck yet.... Please some one help me in this...

Below is the code to get the list of files in a directory on local drive. Is there any way to do the same on share drive on server...
Dim iRow As Long 'start row to put output
Dim pathf As String 'pathfolder
Dim subf As Boolean 'incl subfolders true/false
Dim Fdate, Tdate As Date
Dim intC As Integer
Sub ListFiles()
Application.ScreenUpdating = False

wksList.Activate

wksList.Range("D8").FormulaR1C1 = "=COUNTA(R[-6]C[2]:R[49992]C[2])"
intC = wksList.Range("D8").Value
wksList.Range("F2:H2").ClearContents
wksList.Range("F3:H" & intC + 1).Clear
iRow = 2
pathf = wksList.Range("D2").Value
subf = True
Call ListMyFiles(pathf, subf)
intC = wksList.Range("D8").Value
wksList.Range("F2:H2").Copy
wksList.Range("F2:H" & intC + 1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
wksList.Range("D8").Value = wksList.Range("D8").Value
wksList.Range("F1").Select

ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("List").AutoFilter.Sort.SortFields.Add Key:=Range( _
"H1:H8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("List").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub


Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
Fdate = wksList.Range("D3").Value
Tdate = wksList.Range("D4").Value + 1
For Each myFile In mySource.Files
If myFile.DateLastModified <= Tdate And myFile.DateLastModified >= Fdate Then
iCol = 6
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Path
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


<tbody>
</tbody>

Thanks a million in advance
Click to expand...

what network path has been defined with this line ?
Code: 
pathf = wksList.Range("D2").Value
 
Upvote 0
hippiehacker said:
what network path has been defined with this line ?
Code: 
pathf = wksList.Range("D2").Value
Click to expand...

It's " \\Server\Folder "
after giving the above path, this macro is not working, but

when I give local path as " C:\Folder "
I'm able to list all files without any hassle

Please help.... Thanks
 
Upvote 0
raghuram.star said:
It's " \\Server\Folder "
after giving the above path, this macro is not working, but

when I give local path as " C:\Folder "
I'm able to list all files without any hassle

Please help.... Thanks
Click to expand...

just tested again no issues on network folders
maybe folder security?
 
Upvote 0
hippiehacker said:
Code: 
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 = 1
    pathf = "D:\Records"
    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 > 40909 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
Click to expand...

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

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
 Set MyObject = New Scripting.FileSystemObject
 
Upvote 0
jujubeans69 said:
I tried this, and it keeps getting an error at the point below:

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

Do you have:

Option Explicit

at the top of the code window? If so remove it and the code should work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to List all Folders, Subfolders and files in a directory
Replies
4
Views
779
Formula11
F
S
  • Question
Vba code to open all excel files along with excel name and date modified and data inside the excel
Replies
0
Views
68
suyogpat
S
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
518
rlv01
R
H
  • Question
Macro to extract list of PDF Files in a date range
Replies
4
Views
661
howard
H
A
  • Question
Excel VBA - Copy and Rename File
Replies
3
Views
3K
Wookiee
Wookiee

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top