macro to open latest file
 

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!


--
tsgol
------------------------------------------------------------------------
tsgol's Profile: http://www.excelforum.com/member.php...o&userid=26648
View this thread: http://www.excelforum.com/showthread...hreadid=399229

macro to open latest file
 

My first thought is that you need this:

checkdate = FileDateTime(mypath & checkfile)



tsgol wrote:

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!

--
tsgol
------------------------------------------------------------------------
tsgol's Profile: http://www.excelforum.com/member.php...o&userid=26648
View this thread: http://www.excelforum.com/showthread...hreadid=399229

--

Dave Peterson