Hi Access experts,

I have this Access problem with the file to be imported having different filenames and one way to know its latest copy is by looking at the file's Modified Date from the Windows Explorer. (I am using network WindowsXP and my Explorer path has its Target at "%SystemRoot%\explorer.exe" and Start in "%HOMEDRIVE%%HOMEPATH%"


1.1) How VB code to add order to let Access using transferSpreadsheet acImport the Excel file from a specified folder with the latest "modified date", as can be seen from the Window Explorer ?
(this is because the Excel data is updated only on every Tuesday with different filenames, therefore I auto fire-up my Access database on every Tuesday 6.00pm to import the spreadsheet with (i)only the latest modified date or (ii)alternatively the excel file with the system now() date).

1.2) How to let Access open up Windows Explorer (i assume using RunApp) to default as the path D:\weekly Service Data\

Then pop up a dialog box MsgBox "the Excelfile you want to process is having
the latest Modified Date ?"
Click YES and the Windows Explorer must close.
Click NO and the Dialog box must close but the Windows Explorer remains
open.


Apprecite very much for any help offered.
Thanks in anticipation.

This is how I did it. This is in VB.NET but you can probably tweek it to work in VBA...

SKILL LEVEL: MEDIUM

In the event that you want to search...

Dim WithEvents FileLoader As FindDownloads
Dim letterNUM As Short
Dim fso2 As New Scripting.FileSystemObject
Dim fil1, fil2 As Object

Dim rs as ADODB.Recordset
Set RS = New ADODB.Recordset

With rs
.Fields.Append("fileID", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("Filename", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("Modified", ADODB.DataTypeEnum.adDate)
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockOptimistic
.Open()
End With

FileLoader = New FindDownloads
FileLoader.FindDownloadFiles("C:\FoldertoSearchin\")
'Now you can look at rs to see all the files

rs.movefirst

'Put this somewhere on your form
Private Sub FileLoader_FoundOne(ByVal Filename As String, ByVal modified As String, ByRef Cancel As Boolean) Handles FileLoader.FoundOne
i = i + 1
With rs
.AddNew()
.Fields.Item("fileID").Value = i
.Fields.Item("filename").Value = Filename
.Fields.Item("modified").Value = modified
.Update()
End With
End Sub

'Put this in a module

Public Event FoundOne(ByVal Filename As String, ByVal modified As String, ByRef Cancel As Boolean)


Public Function FindDownloadFiles(ByVal RootFolder As String) As Boolean
Dim System_Renamed As Scripting.FileSystemObject
Dim Foldr As Scripting.Folder
Dim SubFolder As Scripting.Folder
Dim Fyle As Scripting.File

' Return new FileSystemObject.
System_Renamed = New Scripting.FileSystemObject

' Get folder.
Foldr = System_Renamed.GetFolder(RootFolder)

' Loop through Files collection, firing an event if the extension matches

Dim Cancel As Boolean
Cancel = False

Dim length1 As Short
length1 = Len("Download")
For Each SubFolder In Foldr.SubFolders
Foldr = System_Renamed.GetFolder(SubFolder.Path)
For Each Fyle In Foldr.Files
System.Diagnostics.Debug.WriteLine(UCase(Left(Syst em_Renamed.GetBaseName(Fyle.Path), 8)) & UCase(Left(System_Renamed.GetExtensionName(Fyle.Pa th), 3)))
If UCase(Left(System_Renamed.GetBaseName(Fyle.Path), length1)) = "DOWNLOAD" And UCase(Left(System_Renamed.GetExtensionName(Fyle.Pa th), 2)) = "JP" Then
RaiseEvent FoundOne(Fyle.Path, CStr(Fyle.DateLastModified), Cancel)
' MsgBox "Found a file " & Fyle.path
If Cancel Then
FindDownloadFiles = False
Exit Function
End If
End If
Next Fyle
Next SubFolder
End Function

I wish I had the knowledge to help more people as I see most people on this sight do. I must say Jerry, that was very impressive answer ;)