|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
Try:
Set fls = fso.GetFolder("C:\Users\" & Environ("UserName") & "\Downloads").Files
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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.
|
Tags |
downloads, loop folders |
|
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 |