Macro to open most current file in folder

[Tasha]

Is there a way for a Macro to open the most current file in a folder? I have
a file copied to a specified folder nightly (C:\DLY\PB061807.txt) for
example. The PB stays the same in every file, and then it adds the date to
the end. There may be several in the folder(for example over the weekend, on
Monday there will be three files), but I am setting up a macro to run each
night and process the text file, but I need to find out how to get it to open
the most current file. Help???

 

[Jim Cone]

Sub LatestFileIs()
'Jim Cone - San Francisco, USA - June 2005
'Displays the latest file name in the strPath folder.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim dteDate As Date

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified > dteDate Then
dteDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

' Display file name in message box.
MsgBox strName & " - is latest file - " & dteDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Tasha" <[email protected]>
wrote in message
Is there a way for a Macro to open the most current file in a folder? I have
a file copied to a specified folder nightly (C:\DLY\PB061807.txt) for
example. The PB stays the same in every file, and then it adds the date to
the end. There may be several in the folder(for example over the weekend, on
Monday there will be three files), but I am setting up a macro to run each
night and process the text file, but I need to find out how to get it to open
the most current file. Help???

 

[Harlan Grove]

Sub LatestFileIs() ....

There are some tasks better suited to console tools. This is one.


Sub test()
'change this to an open method call
MsgBox mrf("C:\DLY\PB*.txt")
End Sub


Function mrf(Optional p As String = ".") As String
Dim tfn As String
tfn = Environ("TEMP") & "\~mrf.tmp"
Shell Environ("COMSPEC") & " /c dir """ & p & _
""" /b /a-d /o-d > """ & tfn & """"
Open tfn For Input As #1
Line Input #1, mrf
Close #1
Kill tfn
End Function


And depending on how the OP is launching this macro to run at night,
it may be more efficient still to use a batch file to launch Excel
AFTER finding the latest file in the specified directory, e.g.,


@echo off
for /F %%f in ('dir "C:\DLY\PB*.txt" /b /a-d /o-d') do (
start excel "excelfilewithmacros" "%%f"
goto :EOF
)

 

[Tasha]

Compile error: Sub or function not defined. What am I doing wrong? I
changed the path to my folder????

 

[Dave Peterson]

Did you copy and paste the mrf function?

If you did, you may want to share what you tried and indicate the line that
caused the error. (Harlan's code worked fine for me in my simple test.)

 

[Tasha]

Yes, the error is highlighted on mrf and the window that popped up says
Compile error: Sub or Function not defined.

This is the code I have showing:

Sub DLYOPEN ()
'change this to an open method call
MsgBox mrf("C:\DLY\PB*.txt")
End Sub

Also, this will just show which file is the most recent right? I need it to
actually open the most recent file in the import window. I have another
macro set up to process the file from that point, but I need it to actually
get it to the import text file window because this macro is set on a timer at
night when nobody is here, so a display won't help. ??? Can this be done?

 

[Dave Peterson]

You didn't copy|paste this portion:

(copy from Harlan's original post to avoid those >'s)