Getting filenames from a folder depending on date

[addy]

Hi

I have a folder full of files on a server, each filename is an 8-digit numeric string.

I would like to have a procedure in Access which would bring all the filenames into a table depending on whether the Modified Date of the file was after a specific date.

Any ideas on where to start would be appreciated.

Thanks.

 

[snyperx3]

Loop through all the files in a folder using:

Dim strPath As String
Dim strFileName As String

strPath = "c:\myfolder\"
'you can replace the *.* with the extention you want to search for
strFileName = Dir$(strPath & "*.*")
Do Until strFileName = ""
    strFileName = Dir$
Loop

Find the Modified Date Using:

Dim myDate as Date
myDate = FileDateTime(strPath & strFileName)

Insert them into the table:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblTable ( FileName ) VALUES ('" & strFileName & strPath & "');"
DoCmd.SetWarnings True

Combine them all:

Dim strPath As String
Dim strFileName As String

strPath = "c:\myfolder\"
strFileName = Dir$(strPath & "*.*")
Do Until strFileName = ""
    If FileDateTime(strPath & strFileName) > CDate("6/08/2006") Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO tblTable ( FileName ) VALUES ('" & strFileName & strPath & "');"
        DoCmd.SetWarnings True
    End If
    strFileName = Dir$
Loop

-Pete

 

[CautionMP]

addy,
Here is a variation of the same thing, assumes that the data will be placed in [tt]tblFiles[/tt] that has the fields [tt]Path[/tt], and [tt]Name[/tt] which are both primary keys.

Option Compare Database
Option Explicit

Sub GetFilesInDirectory(Directory As String, Optional ModifiedDate As Date)
On Error GoTo GetFilesInDirectory_Error
Dim MyDatabase As DAO.Database
Dim MyRecordset As DAO.Recordset
Dim MyProperPath As String, MyFileName As String

'Open the destination recordset
Set MyDatabase = CurrentDb
Set MyRecordset = MyDatabase.OpenRecordset("tblFiles")

'Make sure the path is in the right format
If Right(Directory, 1) <> "\" Then
  MyProperPath = Directory & "\"
Else
  MyProperPath = Directory
End If

'Find the first file in the directory
MyFileName = Dir(MyProperPath)
'Add the record if found and keep calling Dir until not more files
Do While MyFileName <> ""
  'Check to see if a Modified date was passed
  If ModifiedDate = 0 Or FileDateTime(MyProperPath & MyFileName) > ModifiedDate Then
    With MyRecordset
      .AddNew
      .Fields("Path") = MyProperPath
      .Fields("Name") = MyFileName
      .Update
    End With
  End If
  MyFileName = Dir
Loop

CleanUp:
MyRecordset.Close
Set MyRecordset = Nothing
Set MyDatabase = Nothing
Exit Sub

GetFilesInDirectory_Error:
Select Case Err.Number
  Case 3022 'Duplicate key error
    MyRecordset.CancelUpdate
    Resume Next
  Case Else
    Err.Raise Err.Number
    'Because I always close the error box before I look at it
    Debug.Print Err.Number, Err.Description
    Resume CleanUp
End Select
End Sub

There's more than one way to skin a potato,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[fneily]

Here's another way. I didn't change it to go to a table, you can get that from the above post.
I created a listbox on a form, setting Row Source Type to Value List.
I then created a command button with the following code on OnClick event:

Private Sub Command2_Click()
Dim strFill As String
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\excelforcourse")
For Each strFileName In objFolder.Items
strFill = strFill & strFileName & " " & objFolder.GetDetailsOf(strFileName, 3) & ";"
Next
ListProps.RowSource = strFill
End Sub

You'll notice the 3 in the GetDetailsOF - that's the Date Modified index. There's 34 properties of a file you can retrieve this way.