View Single Post
 
Old 12-07-2012, 06:39 PM
CatMan CatMan is offline Windows 7 32bit Office 2010 32bit
Intermediate
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default How to find the path to the downloads folder when it is different for each user

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

Last edited by CatMan; 12-07-2012 at 10:05 PM.
Reply With Quote