How to find the path to the downloads folder when it is different for each user

CatMan · #1 12-07-2012, 06:39 PM

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

macropod · #2 12-07-2012, 08:41 PM

Try:
Set fls = fso.GetFolder("C:\Users\" & Environ("UserName") & "\Downloads").Files

CatMan · #3 12-07-2012, 09:59 PM

Paul, that did it! I briefly messed around with application.username but that returned my full name. I'm off to the races now, many thanks! Long live Excel Programming Forum! Oh, and thanks to whoever moved this thread to Excel\Programming.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find and Prompt the user to Replace	Lawmuse	Word VBA	9	02-27-2023 06:36 AM
How to find complete path to email from search window?	Jennifer Murphy	Outlook	1	08-12-2011 03:36 PM
User cannot move email messages within Outlook Inbox folder and sub-folders.	Darsss	Outlook	5	06-06-2011 11:37 PM
Update Outlook but user path changed	Ossie1972	Outlook	1	12-08-2010 08:19 PM
Outlook problem - Open Other User’s Folder doesn’t show Sent	imported_bankboysb	Outlook	0	12-12-2005 11:41 AM