Hi,
I am attempting to use the following code (which I am grateful to have discovered on this website) to open the most recent file found in a particular folder. However, I am having trouble with a couple parts.
My first problem, which you may have noticed, is that I am trying to use a variable in the directory path but it is not working. Did I not code correctly or can I not use a variable here?
I then took out the variable folder name and inserted an actual folder name to see if the rest of the procedure would work. It was able to find the correct file but I received Run Time Error 1004 that said 'file name.csv' could not be found. This confused me because the file name was correct so the procedure did find it. It did however have the .csv at the end of the name which is probably what is causing the error. I am not sure what is happening here or how to move past this. Any help is appreciated.
I am attempting to use the following code (which I am grateful to have discovered on this website) to open the most recent file found in a particular folder. However, I am having trouble with a couple parts.
Code:
Sub GetFile()
Dim SystemSN As String
Dim FileSys As FileSystemObject
Dim ObjFile As File
Dim MyFolder As Folder
Dim StrFileName As String
Dim FileDate As Date
SystemSN = Range("A2").Value
Const myDir As String = "J:\Field Data\" & SystemSN & "\Downloaded\Stats"
Set FileSys = New FileSystemObject
Set MyFolder = FileSys.GetFolder(myDir)
FileDate = DateSerial(1900, 1, 1)
For Each ObjFile In MyFolder.Files
If ObjFile.DateLastModified > FileDate Then
FileDate = ObjFile.DateLastModified
StrFileName = ObjFile.Name
End If
Next ObjFile
Workbooks.Open StrFileName
Range("A1").Select
End Sub
My first problem, which you may have noticed, is that I am trying to use a variable in the directory path but it is not working. Did I not code correctly or can I not use a variable here?
I then took out the variable folder name and inserted an actual folder name to see if the rest of the procedure would work. It was able to find the correct file but I received Run Time Error 1004 that said 'file name.csv' could not be found. This confused me because the file name was correct so the procedure did find it. It did however have the .csv at the end of the name which is probably what is causing the error. I am not sure what is happening here or how to move past this. Any help is appreciated.