I am trying to open the most recently modified .CSV file from a folder, and it's seemingly working except for the .Open part. When i manually open it, there is no prompt stopping me so i dont understand. The error message i get says, run-time 1004, "*the exact file name im trying to open*" could not be found. Check the spelling and verify file location. ... So its finding the right file because it tells me in the error msg. I can also use an input box to specifiy the file to open by name, and that works. Im hoping someone has seen the issue before, or maybe its something that stands out to the professional (not me) eye...
Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
'path for files
Const myDir As String = "*folder path*"
'filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)
'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If InStr(1, objFile.Name, "FILE01", vbTextCompare) Then
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
End If
Next objFile
Workbooks.Open strFilename
Set FileSys = Nothing
Set myFolder = Nothing
Any help would be appreciated, thank you!
Bookmarks