5

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.

Community
  • 1
  • 1
derigible
  • 964
  • 5
  • 15
  • 32
  • The error is not with the DIR. The error is with this line `lastMod = FileDateTime(StrFile)` You have to supply the full path. Similarly for others. – Siddharth Rout Jul 26 '12 at 18:11

1 Answers1

5

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

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • Ok, that did it. Thanks for answering, I was not aware that this was the case for Dir. The help section in Excel states that but I for some reason assumed it would return the whole path. That saved me several more hours. – derigible Jul 26 '12 at 18:46