I am trying to loop through a given directory to find the latest downloaded csv file. For some reason my Dir function won't find any file even if the file does exist. I am not totally familiar with VBA so i may perhaps be missing some sort of reference to perform the Dir function, but I can't find anything online that tells me I need to. All the examples and forums use Dir just like I do, but I can't get mine to work. Here is the code, please tell me if you can see what I am doing wrong:
Public Function Get_File() as string
Dim filePath As String
ChDir ("..")
filePath = CurDir
'Goes back to Documents directory to be in same directory as macro
ChDir (filePath & "\Documents")
filePath = filePath & "\Downloads\test.txt"
filePath = getLatestFile(filePath)
Get_File = filePath
End Function
Public Function getLatestFile(pathToFile As String) As String
Dim StrFile As String
Dim lastMod As Variant
Dim nextMod As Variant
Dim lastFileName As String
StrFile = Dir(pathToFile)
lastFileName = StrFile
lastMod = FileDateTime(StrFile)
While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
nextMod = FileDateTime(StrFile)
If nextMod > lastMod Then
lastFileName = StrFile
lastMod = nextMod
End If
Wend
getLatestFile = lastFileName
End Function
The test.txt file is in my Downloads file and the filePath string prints out to be the correct path, but I keep getting an error stating that it can't find the file. It fails at the first use of Dir(pathToFile). Any help would be greatly appreciated.
Dir()
only returns the filename portion of the path, i.e., it does not return the folder portion. For example,
Dir("C:\MyPath\MyFile.txt")
returns MyFile.txt
not C:\MyPath\MyFile.txt