Dir Function in Excel 2010 VBA not working
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
derigible
Updated on June 04, 2022Comments
-
derigible almost 2 years
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.