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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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...
[TABLE="width: 100%"]
<tbody>[TR]
[TD]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


[/TD]
[/TR]
</tbody>[/TABLE]

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...
[TABLE="width: 100%"]
<tbody>[TR]
[TD]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

[/TD]
[/TR]
</tbody>[/TABLE]

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

willow1985
  • Solved
VBA error handling - File already exists in location
Replies
9
Views
907
NateSC
NateSC
J
  • Question Question
VBA to List all Folders, Subfolders and files in a directory
Replies
4
Views
2K
Formula11
F
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
709
Micron
M
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
S
  • Question Question
Vba code to open all excel files along with excel name and date modified and data inside the excel
Replies
0
Views
291
suyogpat
S

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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