Finding the right folder with VBA

[gr1410]

Hello,

I have a spreadsheet that is updated with a macro finding the latest download in an appropriate folder. It looks as follows:

MyPath = "C:\User1\Downloads"

MyFile = Dir(MyPath & "*.xls", vbNormal)

......

Everything is working fine. Unfortunately, recently all the users have been assigned separate profiles,
so now the path to the folder depends on who is signed in and the macro works only
in one profile.

"MyPath" would have to be chosen from several different possibilities like:

"C:\User1\Downloads"
"C:\User2\Downloads"
"C:\User3\Downloads" etc.

Only one of them can be found at any one time.

Is there a way for write a code which would find which folder is available at any one time in "MyPath" variable. With an IF statement perhaps?

Thanks in advance for help with this issue.

 

[=ODIN=]

If the file is always going to be in the windows user's downloads folder, this might work for MyPath:

[COLOR=#333333]MyPath =[/COLOR]"C:\Users\" & environ("username") & "\Downloads"

this assumes that they each have a different copy of the file in their own userprofile's downloads folder. If they are all sharing the same file, you probably need to set the file up to just save to C:\ in the first place. and then reference it there.

 

[mrshl9898]

This will look for the file with the newest date and time

Sub findnewest()

MyPath1 = "C:\User1\Downloads\"
MyPath2 = "C:\User2\Downloads\"
MyPath3 = "C:\User3\Downloads\"


MyFile1 = Dir(MyPath1 & "*.xls", vbNormal)
MyFile2 = Dir(MyPath2 & "*.xls", vbNormal)
MyFile3 = Dir(MyPath3 & "*.xls", vbNormal)


Time1 = FileDateTime(MyPath1 & MyFile1)
Time2 = FileDateTime(MyPath2 & MyFile2)
Time3 = FileDateTime(MyPath3 & MyFile3)


If Time1 > Time2 And Time1 > Time3 Then
MsgBox "Time1 greatest"
ElseIf Time2 > Time1 And Time2 > Time3 Then
MsgBox "Time2 greatest"
Else
MsgBox "Time3 greatest"
End If


End Sub

 

[gr1410]

Apologies for late reply. Both of these solutions work just fine.
Thank you for your help.