Open Just The Folder That Contains Most Recently Modified File

[indi visual]

I am in need of a tweak of the code below that finds the most recently modified file and simply opens the folder it resides in (a yes no pop up box is not necessary).


The file itself doesn't need to be opened (only the folder that it's in).


[pre]<br />
Sub test()<br />
Dim myDir As String, fn As String, a(), n As Long, myFile As String<br />
Dim myDate As Date, temp As Date<br />
myDir = "W:"<br />
fn = Dir(myDir & "*.xls&#34<br />
Do While fn <> ""<br />
temp = CreateObject("Scripting.FileSystemObject&#34.GetFile(myDir & "" & fn).DateLastModified<br />
If myDate = 0 Then<br />
myDate = temp : myFile = myDir & "" & fn<br />
Else<br />
If myDate < temp Then myDate = temp : myFile = myDir & "" & fn<br />
End If<br />
fn = Dir<br />
Loop<br />
If Len(myFile) Then<br />
If vbYes = MsgBox("File Name : " & myFile & vbLf & _<br />
"Last modified on : " & myDate, vbYesNo) Then<br />
Workbooks.Open(myDir & "" & myFile)<br />
End If<br />
End If<br />
End Sub<br />
[/pre]


-I will only post this question here exclusively-

 

[Hui]

Indi


I don't believe it is possible to open a File Open dialoge and leave it in an open state.


Have you thought about opening an Explorer window in the right directory?


Replace : Workbooks.Open (myDir & "" & myFile)


With : Call Shell("explorer.exe " & myDir, vbNormalFocus)

 

[indi visual]

Got the code below to work with the tweak you suggested, but it only checks the files in the directory folder only. How do I check all folders and subfolders within a specified directory.


How do I get it to search the entire C: drive for the latest modified file (and go through all subfolders to search for it if necessary as well?


[pre]<br />
Sub test()<br />
Dim myDir As String, fn As String, a(), n As Long, myFile As String<br />
Dim myDate As Date, temp As Date<br />
myDir = "C:UsersAnonymousDesktop"<br />
fn = Dir(myDir & "*.*&#34<br />
Do While fn <> ""<br />
temp = CreateObject("Scripting.FileSystemObject&#34.GetFile(myDir & "" & fn).DateLastModified<br />
If myDate = 0 Then<br />
myDate = temp: myFile = myDir & "" & fn<br />
Else<br />
If myDate < temp Then myDate = temp: myFile = myDir & "" & fn<br />
End If<br />
fn = Dir<br />
Loop<br />
If Len(myFile) Then<br />
If vbYes = MsgBox("File Name : " & myFile & vbLf & _<br />
"Last modified on : " & myDate, vbYesNo) Then<br />
Call Shell("explorer.exe " & myDir, vbNormalFocus)<br />
End If<br />
End If<br />
End Sub<br />
[/pre]

 

[Hui]

Have a read of http://www.xtremevbtalk.com/showthread.php?t=19270

 

[indi visual]

I used the call edit you suggested above and got it to work the way I wanted to (that in addition to the extra you provided in the thread).


Thanks Hui