Unanswered: How to import from Excel spreadsheet by file date?
Every week I receive an Excel spreadsheet that I store in a directory then manually import to a table in my database. I know how to use the TransferSpreadsheet method in VBA to achieve this for a named spreadsheet, but what I would like to do is automatically import the most recent spreadsheet in a directory regardless of filename.
In other words, if my directory contained say 10 Excel files, each with a different name, my database would only import the file with the most recent date.
Yes, you can use the File object of the VBScript library to retrieve the date of creation of the files:
Sub ShowFolderList(strFolderName As String)
Dim fso, Folder, File, Files
Set fso = CreateObject("Scripting.FileSystemObject")
Set Folder = fso.GetFolder(strFolderName)
Set Files = Folder.Files
For Each File In Files
Debug.Print File.Name, File.DateCreated, File.DateLastAccessed, File.DateLastModified