T
tsgol
Hi all,
I need my macro to open the most recent file in a folder. There is no
standard format to the file name except that they are always '.txt'.
I'm getting an error (Run-Time error '53'; File not found) on the
second run through the loop. I stole this code from someone;s posting,
and changed the extension from .xls to txt. I also changed mypath from
"thisworkbook.path & '\'" to what is below. Here's the loop:
Dim mypath As String
Dim checkfile As String
Dim checkdate As Date
Dim lastdate As Date
Dim lastfile As String
lastdate = #1/1/2001#
mypath = "F:\Users\Allison\FtCarsonMacro\Tenant files\"
checkfile = Dir(mypath & "*.txt")
Do While checkfile <> ""
If checkfile <> "." And checkfile <> ".." Then
checkdate = FileDateTime(checkfile)
If checkdate > lastdate Then
lastdate = checkdate
lastfile = checkfile
End If
End If
checkfile = Dir
Loop
The red text is where I'm getting the error. The following is the list
of files:
8.19.05 FTCarson APT.txt 8/19/2005
APT1.txt 8/11/2005
FT Tenant 8/25/2005
It runs the 8.19.05 FTCarson APT.txt first with no problems. Then it
tries to process the APT1.txt file and when it gets to checkdate =
FileDateTime(checkfile) it says it can't find the file. When I hold my
cursor over "checkfile" is does say "APT1.txt".
If it found the file to assign it to checkfile, why can't it find it
again? I'm so confused! Any help would be GREATLY appreciated!
Thanks in advance!
I need my macro to open the most recent file in a folder. There is no
standard format to the file name except that they are always '.txt'.
I'm getting an error (Run-Time error '53'; File not found) on the
second run through the loop. I stole this code from someone;s posting,
and changed the extension from .xls to txt. I also changed mypath from
"thisworkbook.path & '\'" to what is below. Here's the loop:
Dim mypath As String
Dim checkfile As String
Dim checkdate As Date
Dim lastdate As Date
Dim lastfile As String
lastdate = #1/1/2001#
mypath = "F:\Users\Allison\FtCarsonMacro\Tenant files\"
checkfile = Dir(mypath & "*.txt")
Do While checkfile <> ""
If checkfile <> "." And checkfile <> ".." Then
checkdate = FileDateTime(checkfile)
If checkdate > lastdate Then
lastdate = checkdate
lastfile = checkfile
End If
End If
checkfile = Dir
Loop
The red text is where I'm getting the error. The following is the list
of files:
8.19.05 FTCarson APT.txt 8/19/2005
APT1.txt 8/11/2005
FT Tenant 8/25/2005
It runs the 8.19.05 FTCarson APT.txt first with no problems. Then it
tries to process the APT1.txt file and when it gets to checkdate =
FileDateTime(checkfile) it says it can't find the file. When I hold my
cursor over "checkfile" is does say "APT1.txt".
If it found the file to assign it to checkfile, why can't it find it
again? I'm so confused! Any help would be GREATLY appreciated!
Thanks in advance!