Hello forum and thanks for checking out this thread. I think this will be interesting...
I want to open the most recent file in the downloads folder. I have no problem opening and identifying the most recent file as long as I define where the downloads folder is (see code below). The problem is, the path to the downloads folder is slightly different for each person that I deploy the VBA automation to. I need to find an automated way to set the path to each users downloads folder. Here is an example path: C:\Users\xxxxxxxx\Downloads (x's are replaced with user ID which, as mentioned, is different for each person). There will always be a 'Users' folder one level down from c:, then the user ID, then the 'Downloads' folder. This tree structure is constant. How can I set the path to the downloads folder so that I can open the most recent downloaded file? Some possible answers might be (1) to loop through all folders on c: drive until I find one called 'downloads', (2) to ask the user to navigate to it using a VBA launched browser (but some users might have trouble with this). Well that's all the methods I can think of. I wonder if there is some windows 7 API/shell command that will pass the path back to VBA? BTY, need a solution that works fo Windows 7 and Office 2007.
Code:
Dim fso As New FileSystemObject
Dim flds As Folders
Dim fls As Files
Dim strText As String
Dim i As Integer
Set fls = fso.GetFolder("C:\Users\123abc\Downloads").Files
With Worksheets("Sheet1")
For Each f In fls
If f.DateLastModified > s1 Then s1 = f.DateLastModified: fn = f.Name
Next
End With
Cells(1, 3) = fn 'filename of most recent file
Cells(1, 5) = s1 'date of most recent file