Check for desktop folder

[rjplante]

I want to have a macro that will check for a specific folder on the desktop of the user that is using the file. Because the file is being used by several different users, I cannot use a specific file path as the users name in the file path will vary depending on who is using the file. The macro should do the following:

1) If there is NOT a folder named "Active Jobs" on the desktop
2) Ask the user if they would like to create one
3) If the user answers "YES"
4) Create new folder on the desktop named "Active Jobs"
5) Inform user a folder named "Active Jobs" now exists on the desktop
6) If "NO" - no folder is created and exit sub.
7) If a folder is found,
8) Display a message box to confirm "Active Jobs" folder already exists.

Any support with this would be much appreciated. I have been searching online and am a little confused on how to correctly get this done.

 

[Trebor76]

Hi rjplante,

Try this:

Option Explicit
Sub Macro1()

    If Dir("C:\Users\" & Environ("USERNAME") & "\Desktop\Active Jobs", vbDirectory) = "" Then
        If MsgBox("There is no folder in your desktop called ""Active Jobs""." & vbNewLine & "Would you like to create it?", vbYesNo) = vbNo Then
            Exit Sub
        Else
            'Can just create the folder as it's assumed
            'C:\Users\" & Environ("USERNAME") & "\Desktop\' resides on all machines
            MkDir "C:\Users\" & Environ("USERNAME") & "\Desktop\Active Jobs"
            MsgBox "The folder ""Active Jobs"" now exists on your desktop."
        End If
    Else
        MsgBox "The folder ""Active Jobs"" already exists."
    End If

End Sub

Regards,

Robert

 

[MARK858]

Another option....

Sub MsgboxDesktopAddress()
    MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")
End Sub

 

[Trebor76]

Nice MARK858

rjplante - I would use MARK858's solution as it's more dynamic to find the location of the desktop folder so:

Option Explicit
Sub Macro2()

    If Dir(CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Active Jobs", vbDirectory) = "" Then
        If MsgBox("There is no folder in your desktop called ""Active Jobs""." & vbNewLine & "Would you like to create it?", vbYesNo) = vbNo Then
            Exit Sub
        Else
            'Can just create the folder as it's assumed
            'C:\Users\" & Environ("USERNAME") & "\Desktop\' resides on all machines
            MkDir CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Active Jobs"
            MsgBox "The folder ""Active Jobs"" now exists on your desktop."
        End If
    Else
        MsgBox "The folder ""Active Jobs"" already exists."
    End If

End Sub

Regards,

Robert

 

[rjplante]

Thanks for the feedback gentlemen. The macro works awesome.

 

[Trebor76]

You're welcome